2 -- Test returning SETOF
4 CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$
7 SELECT test_setof_error();
8 ERROR: returned object cannot be iterated
9 DETAIL: PL/Python set-returning functions must return an iterable object.
10 CONTEXT: PL/Python function "test_setof_error"
11 CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$
12 return [ content ]*count
13 $$ LANGUAGE plpythonu;
14 CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$
16 for i in range(count):
19 $$ LANGUAGE plpythonu;
20 CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$
22 def __init__ (self, icount, icontent):
23 self.icontent = icontent
32 return producer(count, content)
33 $$ LANGUAGE plpythonu;
34 CREATE FUNCTION test_setof_spi_in_iterator() RETURNS SETOF text AS
36 for s in ('Hello', 'Brave', 'New', 'World'):
37 plpy.execute('select 1')
39 plpy.execute('select 2')
42 -- Test set returning functions
43 SELECT test_setof_as_list(0, 'list');
48 SELECT test_setof_as_list(1, 'list');
54 SELECT test_setof_as_list(2, 'list');
61 SELECT test_setof_as_list(2, null);
68 SELECT test_setof_as_tuple(0, 'tuple');
73 SELECT test_setof_as_tuple(1, 'tuple');
79 SELECT test_setof_as_tuple(2, 'tuple');
86 SELECT test_setof_as_tuple(2, null);
93 SELECT test_setof_as_iterator(0, 'list');
94 test_setof_as_iterator
95 ------------------------
98 SELECT test_setof_as_iterator(1, 'list');
99 test_setof_as_iterator
100 ------------------------
104 SELECT test_setof_as_iterator(2, 'list');
105 test_setof_as_iterator
106 ------------------------
111 SELECT test_setof_as_iterator(2, null);
112 test_setof_as_iterator
113 ------------------------
118 SELECT test_setof_spi_in_iterator();
119 test_setof_spi_in_iterator
120 ----------------------------
127 -- set-returning function that modifies its parameters
128 CREATE OR REPLACE FUNCTION ugly(x int, lim int) RETURNS SETOF int AS $$
133 $$ LANGUAGE plpythonu;
144 -- interleaved execution of such a function
145 SELECT ugly(1,3), ugly(7,8);
156 -- returns set of named-composite-type tuples
157 CREATE OR REPLACE FUNCTION get_user_records()
160 return plpy.execute("SELECT * FROM users ORDER BY username")
161 $$ LANGUAGE plpythonu;
162 SELECT get_user_records();
164 ----------------------
171 SELECT * FROM get_user_records();
172 fname | lname | username | userid
173 --------+-------+----------+--------
174 jane | doe | j_doe | 1
175 john | doe | johnd | 2
176 rick | smith | slash | 4
177 willem | doe | w_doe | 3
180 -- same, but returning set of RECORD
181 CREATE OR REPLACE FUNCTION get_user_records2()
182 RETURNS TABLE(fname text, lname text, username text, userid int)
184 return plpy.execute("SELECT * FROM users ORDER BY username")
185 $$ LANGUAGE plpythonu;
186 SELECT get_user_records2();
188 ----------------------
195 SELECT * FROM get_user_records2();
196 fname | lname | username | userid
197 --------+-------+----------+--------
198 jane | doe | j_doe | 1
199 john | doe | johnd | 2
200 rick | smith | slash | 4
201 willem | doe | w_doe | 3