2 -- Test returning SETOF
5 CREATE FUNCTION test_setof_error() RETURNS SETOF text AS $$
9 SELECT test_setof_error();
12 CREATE FUNCTION test_setof_as_list(count integer, content text) RETURNS SETOF text AS $$
13 return [ content ]*count
14 $$ LANGUAGE plpythonu;
16 CREATE FUNCTION test_setof_as_tuple(count integer, content text) RETURNS SETOF text AS $$
18 for i in range(count):
21 $$ LANGUAGE plpythonu;
23 CREATE FUNCTION test_setof_as_iterator(count integer, content text) RETURNS SETOF text AS $$
25 def __init__ (self, icount, icontent):
26 self.icontent = icontent
35 return producer(count, content)
36 $$ LANGUAGE plpythonu;
38 CREATE FUNCTION test_setof_spi_in_iterator() RETURNS SETOF text AS
40 for s in ('Hello', 'Brave', 'New', 'World'):
41 plpy.execute('select 1')
43 plpy.execute('select 2')
48 -- Test set returning functions
49 SELECT test_setof_as_list(0, 'list');
50 SELECT test_setof_as_list(1, 'list');
51 SELECT test_setof_as_list(2, 'list');
52 SELECT test_setof_as_list(2, null);
54 SELECT test_setof_as_tuple(0, 'tuple');
55 SELECT test_setof_as_tuple(1, 'tuple');
56 SELECT test_setof_as_tuple(2, 'tuple');
57 SELECT test_setof_as_tuple(2, null);
59 SELECT test_setof_as_iterator(0, 'list');
60 SELECT test_setof_as_iterator(1, 'list');
61 SELECT test_setof_as_iterator(2, 'list');
62 SELECT test_setof_as_iterator(2, null);
64 SELECT test_setof_spi_in_iterator();
66 -- set-returning function that modifies its parameters
67 CREATE OR REPLACE FUNCTION ugly(x int, lim int) RETURNS SETOF int AS $$
72 $$ LANGUAGE plpythonu;
76 -- interleaved execution of such a function
77 SELECT ugly(1,3), ugly(7,8);
79 -- returns set of named-composite-type tuples
80 CREATE OR REPLACE FUNCTION get_user_records()
83 return plpy.execute("SELECT * FROM users ORDER BY username")
84 $$ LANGUAGE plpythonu;
86 SELECT get_user_records();
87 SELECT * FROM get_user_records();
89 -- same, but returning set of RECORD
90 CREATE OR REPLACE FUNCTION get_user_records2()
91 RETURNS TABLE(fname text, lname text, username text, userid int)
93 return plpy.execute("SELECT * FROM users ORDER BY username")
94 $$ LANGUAGE plpythonu;
96 SELECT get_user_records2();
97 SELECT * FROM get_user_records2();