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