1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
CREATE FUNCTION multiout_simple(OUT i integer, OUT j integer) AS $$
return (1, 2)
$$ LANGUAGE plpython3u;
SELECT multiout_simple();
SELECT * FROM multiout_simple();
SELECT i, j + 2 FROM multiout_simple();
SELECT (multiout_simple()).j + 3;
CREATE FUNCTION multiout_simple_setof(n integer = 1, OUT integer, OUT integer) RETURNS SETOF record AS $$
return [(1, 2)] * n
$$ LANGUAGE plpython3u;
SELECT multiout_simple_setof();
SELECT * FROM multiout_simple_setof();
SELECT * FROM multiout_simple_setof(3);
CREATE FUNCTION multiout_record_as(typ text,
first text, OUT first text,
second integer, OUT second integer,
retnull boolean) RETURNS record AS $$
if retnull:
return None
if typ == 'dict':
return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
elif typ == 'tuple':
return ( first, second )
elif typ == 'list':
return [ first, second ]
elif typ == 'obj':
class type_record: pass
type_record.first = first
type_record.second = second
return type_record
elif typ == 'str':
return "('%s',%r)" % (first, second)
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_record_as('dict', 'foo', 1, 'f');
SELECT multiout_record_as('dict', 'foo', 1, 'f');
SELECT * FROM multiout_record_as('dict', null, null, false);
SELECT * FROM multiout_record_as('dict', 'one', null, false);
SELECT * FROM multiout_record_as('dict', null, 2, false);
SELECT * FROM multiout_record_as('dict', 'three', 3, false);
SELECT * FROM multiout_record_as('dict', null, null, true);
SELECT * FROM multiout_record_as('tuple', null, null, false);
SELECT * FROM multiout_record_as('tuple', 'one', null, false);
SELECT * FROM multiout_record_as('tuple', null, 2, false);
SELECT * FROM multiout_record_as('tuple', 'three', 3, false);
SELECT * FROM multiout_record_as('tuple', null, null, true);
SELECT * FROM multiout_record_as('list', null, null, false);
SELECT * FROM multiout_record_as('list', 'one', null, false);
SELECT * FROM multiout_record_as('list', null, 2, false);
SELECT * FROM multiout_record_as('list', 'three', 3, false);
SELECT * FROM multiout_record_as('list', null, null, true);
SELECT * FROM multiout_record_as('obj', null, null, false);
SELECT * FROM multiout_record_as('obj', 'one', null, false);
SELECT * FROM multiout_record_as('obj', null, 2, false);
SELECT * FROM multiout_record_as('obj', 'three', 3, false);
SELECT * FROM multiout_record_as('obj', null, null, true);
SELECT * FROM multiout_record_as('str', 'one', 1, false);
SELECT * FROM multiout_record_as('str', 'one', 2, false);
SELECT *, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', NULL, 'f') AS f(f, s);
SELECT *, f IS NULL AS fnull, s IS NULL AS snull FROM multiout_record_as('tuple', 'xxx', 1, 't') AS f(f, s);
SELECT * FROM multiout_record_as('obj', NULL, 10, 'f');
CREATE FUNCTION multiout_setof(n integer,
OUT power_of_2 integer,
OUT length integer) RETURNS SETOF record AS $$
for i in range(n):
power = 2 ** i
length = plpy.execute("select length('%d')" % power)[0]['length']
yield power, length
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_setof(3);
SELECT multiout_setof(5);
CREATE FUNCTION multiout_return_table() RETURNS TABLE (x integer, y text) AS $$
return [{'x': 4, 'y' :'four'},
{'x': 7, 'y' :'seven'},
{'x': 0, 'y' :'zero'}]
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_return_table();
CREATE FUNCTION multiout_array(OUT integer[], OUT text) RETURNS SETOF record AS $$
yield [[1], 'a']
yield [[1,2], 'b']
yield [[1,2,3], None]
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_array();
CREATE FUNCTION singleout_composite(OUT type_record) AS $$
return {'first': 1, 'second': 2}
$$ LANGUAGE plpython3u;
CREATE FUNCTION multiout_composite(OUT type_record) RETURNS SETOF type_record AS $$
return [{'first': 1, 'second': 2},
{'first': 3, 'second': 4 }]
$$ LANGUAGE plpython3u;
SELECT * FROM singleout_composite();
SELECT * FROM multiout_composite();
-- composite OUT parameters in functions returning RECORD not supported yet
CREATE FUNCTION multiout_composite(INOUT n integer, OUT type_record) AS $$
return (n, (n * 2, n * 3))
$$ LANGUAGE plpython3u;
CREATE FUNCTION multiout_table_type_setof(typ text, returnnull boolean, INOUT n integer, OUT table_record) RETURNS SETOF record AS $$
if returnnull:
d = None
elif typ == 'dict':
d = {'first': n * 2, 'second': n * 3, 'extra': 'not important'}
elif typ == 'tuple':
d = (n * 2, n * 3)
elif typ == 'list':
d = [ n * 2, n * 3 ]
elif typ == 'obj':
class d: pass
d.first = n * 2
d.second = n * 3
elif typ == 'str':
d = "(%r,%r)" % (n * 2, n * 3)
for i in range(n):
yield (i, d)
$$ LANGUAGE plpython3u;
SELECT * FROM multiout_composite(2);
SELECT * FROM multiout_table_type_setof('dict', 'f', 3);
SELECT * FROM multiout_table_type_setof('dict', 'f', 7);
SELECT * FROM multiout_table_type_setof('tuple', 'f', 2);
SELECT * FROM multiout_table_type_setof('tuple', 'f', 3);
SELECT * FROM multiout_table_type_setof('list', 'f', 2);
SELECT * FROM multiout_table_type_setof('list', 'f', 3);
SELECT * FROM multiout_table_type_setof('obj', 'f', 4);
SELECT * FROM multiout_table_type_setof('obj', 'f', 5);
SELECT * FROM multiout_table_type_setof('str', 'f', 6);
SELECT * FROM multiout_table_type_setof('str', 'f', 7);
SELECT * FROM multiout_table_type_setof('dict', 't', 3);
-- check what happens if a type changes under us
CREATE TABLE changing (
i integer,
j integer
);
CREATE FUNCTION changing_test(OUT n integer, OUT changing) RETURNS SETOF record AS $$
return [(1, {'i': 1, 'j': 2}),
(1, (3, 4))]
$$ LANGUAGE plpython3u;
SELECT * FROM changing_test();
ALTER TABLE changing DROP COLUMN j;
SELECT * FROM changing_test();
SELECT * FROM changing_test();
ALTER TABLE changing ADD COLUMN j integer;
SELECT * FROM changing_test();
-- tables of composite types
CREATE FUNCTION composite_types_table(OUT tab table_record[], OUT typ type_record[] ) RETURNS SETOF record AS $$
yield {'tab': [('first', 1), ('second', 2)],
'typ': [{'first': 'third', 'second': 3},
{'first': 'fourth', 'second': 4}]}
yield {'tab': [('first', 1), ('second', 2)],
'typ': [{'first': 'third', 'second': 3},
{'first': 'fourth', 'second': 4}]}
yield {'tab': [('first', 1), ('second', 2)],
'typ': [{'first': 'third', 'second': 3},
{'first': 'fourth', 'second': 4}]}
$$ LANGUAGE plpython3u;
SELECT * FROM composite_types_table();
-- check what happens if the output record descriptor changes
CREATE FUNCTION return_record(t text) RETURNS record AS $$
return {'t': t, 'val': 10}
$$ LANGUAGE plpython3u;
SELECT * FROM return_record('abc') AS r(t text, val integer);
SELECT * FROM return_record('abc') AS r(t text, val bigint);
SELECT * FROM return_record('abc') AS r(t text, val integer);
SELECT * FROM return_record('abc') AS r(t varchar(30), val integer);
SELECT * FROM return_record('abc') AS r(t varchar(100), val integer);
SELECT * FROM return_record('999') AS r(val text, t integer);
CREATE FUNCTION return_record_2(t text) RETURNS record AS $$
return {'v1':1,'v2':2,t:3}
$$ LANGUAGE plpython3u;
SELECT * FROM return_record_2('v3') AS (v3 int, v2 int, v1 int);
SELECT * FROM return_record_2('v3') AS (v2 int, v3 int, v1 int);
SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int);
SELECT * FROM return_record_2('v4') AS (v1 int, v4 int, v2 int);
-- error
SELECT * FROM return_record_2('v4') AS (v1 int, v3 int, v2 int);
-- works
SELECT * FROM return_record_2('v3') AS (v1 int, v3 int, v2 int);
SELECT * FROM return_record_2('v3') AS (v1 int, v2 int, v3 int);
-- multi-dimensional array of composite types.
CREATE FUNCTION composite_type_as_list() RETURNS type_record[] AS $$
return [[('first', 1), ('second', 1)], [('first', 2), ('second', 2)], [('first', 3), ('second', 3)]];
$$ LANGUAGE plpython3u;
SELECT * FROM composite_type_as_list();
-- Starting with PostgreSQL 10, a composite type in an array cannot be
-- represented as a Python list, because it's ambiguous with multi-dimensional
-- arrays. So this throws an error now. The error should contain a useful hint
-- on the issue.
CREATE FUNCTION composite_type_as_list_broken() RETURNS type_record[] AS $$
return [['first', 1]];
$$ LANGUAGE plpython3u;
SELECT * FROM composite_type_as_list_broken();
|
