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
|
--
-- Test returning SETOF
--
CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$
return 37
$$ LANGUAGE plpython3u;
SELECT test_setof_error();
ERROR: returned object cannot be iterated
DETAIL: PL/Python set-returning functions must return an iterable object.
CONTEXT: PL/Python function "test_setof_error"
CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$
return [ content ]*count
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$
t = ()
for i in range(count):
t += ( content, )
return t
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$
class producer:
def __init__ (self, icount, icontent):
self.icontent = icontent
self.icount = icount
def __iter__ (self):
return self
def __next__ (self):
if self.icount == 0:
raise StopIteration
self.icount -= 1
return self.icontent
return producer(count, content)
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_setof_spi_in_iterator() RETURNS SETOF text AS
$$
for s in ('Hello', 'Brave', 'New', 'World'):
plpy.execute('select 1')
yield s
plpy.execute('select 2')
$$
LANGUAGE plpython3u;
-- Test set returning functions
SELECT test_setof_as_list(0, 'list');
test_setof_as_list
--------------------
(0 rows)
SELECT test_setof_as_list(1, 'list');
test_setof_as_list
--------------------
list
(1 row)
SELECT test_setof_as_list(2, 'list');
test_setof_as_list
--------------------
list
list
(2 rows)
SELECT test_setof_as_list(2, null);
test_setof_as_list
--------------------
(2 rows)
SELECT test_setof_as_tuple(0, 'tuple');
test_setof_as_tuple
---------------------
(0 rows)
SELECT test_setof_as_tuple(1, 'tuple');
test_setof_as_tuple
---------------------
tuple
(1 row)
SELECT test_setof_as_tuple(2, 'tuple');
test_setof_as_tuple
---------------------
tuple
tuple
(2 rows)
SELECT test_setof_as_tuple(2, null);
test_setof_as_tuple
---------------------
(2 rows)
SELECT test_setof_as_iterator(0, 'list');
test_setof_as_iterator
------------------------
(0 rows)
SELECT test_setof_as_iterator(1, 'list');
test_setof_as_iterator
------------------------
list
(1 row)
SELECT test_setof_as_iterator(2, 'list');
test_setof_as_iterator
------------------------
list
list
(2 rows)
SELECT test_setof_as_iterator(2, null);
test_setof_as_iterator
------------------------
(2 rows)
SELECT test_setof_spi_in_iterator();
test_setof_spi_in_iterator
----------------------------
Hello
Brave
New
World
(4 rows)
-- set-returning function that modifies its parameters
CREATE OR REPLACE FUNCTION ugly(x int, lim int) RETURNS SETOF int AS $$
global x
while x <= lim:
yield x
x = x + 1
$$ LANGUAGE plpython3u;
SELECT ugly(1, 5);
ugly
------
1
2
3
4
5
(5 rows)
-- interleaved execution of such a function
SELECT ugly(1,3), ugly(7,8);
ugly | ugly
------+------
1 | 7
2 | 8
3 |
(3 rows)
-- returns set of named-composite-type tuples
CREATE OR REPLACE FUNCTION get_user_records()
RETURNS SETOF users
AS $$
return plpy.execute("SELECT * FROM users ORDER BY username")
$$ LANGUAGE plpython3u;
SELECT get_user_records();
get_user_records
----------------------
(jane,doe,j_doe,1)
(john,doe,johnd,2)
(rick,smith,slash,4)
(willem,doe,w_doe,3)
(4 rows)
SELECT * FROM get_user_records();
fname | lname | username | userid
--------+-------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
rick | smith | slash | 4
willem | doe | w_doe | 3
(4 rows)
-- same, but returning set of RECORD
CREATE OR REPLACE FUNCTION get_user_records2()
RETURNS TABLE(fname text, lname text, username text, userid int)
AS $$
return plpy.execute("SELECT * FROM users ORDER BY username")
$$ LANGUAGE plpython3u;
SELECT get_user_records2();
get_user_records2
----------------------
(jane,doe,j_doe,1)
(john,doe,johnd,2)
(rick,smith,slash,4)
(willem,doe,w_doe,3)
(4 rows)
SELECT * FROM get_user_records2();
fname | lname | username | userid
--------+-------+----------+--------
jane | doe | j_doe | 1
john | doe | johnd | 2
rick | smith | slash | 4
willem | doe | w_doe | 3
(4 rows)
|
