1CREATE TABLE foo2(fooid int, f2 int);
2INSERT INTO foo2 VALUES(1, 11);
3INSERT INTO foo2 VALUES(2, 22);
4INSERT INTO foo2 VALUES(1, 111);
5
6CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1 ORDER BY f2;' LANGUAGE SQL;
7
8-- function with ORDINALITY
9select * from foot(1) with ordinality as z(a,b,ord);
10select * from foot(1) with ordinality as z(a,b,ord) where b > 100;   -- ordinal 2, not 1
11-- ordinality vs. column names and types
12select a,b,ord from foot(1) with ordinality as z(a,b,ord);
13select a,ord from unnest(array['a','b']) with ordinality as z(a,ord);
14select * from unnest(array['a','b']) with ordinality as z(a,ord);
15select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord);
16select * from unnest(array[1.0::float8]) with ordinality as z(a,ord);
17select row_to_json(s.*) from generate_series(11,14) with ordinality s;
18-- ordinality vs. views
19create temporary view vw_ord as select * from (values (1)) v(n) join foot(1) with ordinality as z(a,b,ord) on (n=ord);
20select * from vw_ord;
21select definition from pg_views where viewname='vw_ord';
22drop view vw_ord;
23
24-- multiple functions
25select * from rows from(foot(1),foot(2)) with ordinality as z(a,b,c,d,ord);
26create temporary view vw_ord as select * from (values (1)) v(n) join rows from(foot(1),foot(2)) with ordinality as z(a,b,c,d,ord) on (n=ord);
27select * from vw_ord;
28select definition from pg_views where viewname='vw_ord';
29drop view vw_ord;
30
31-- expansions of unnest()
32select * from unnest(array[10,20],array['foo','bar'],array[1.0]);
33select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord);
34select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord);
35select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as z(a,b,c,ord);
36create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c);
37select * from vw_ord;
38select definition from pg_views where viewname='vw_ord';
39drop view vw_ord;
40create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c);
41select * from vw_ord;
42select definition from pg_views where viewname='vw_ord';
43drop view vw_ord;
44create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(1,2)) as z(a,b,c);
45select * from vw_ord;
46select definition from pg_views where viewname='vw_ord';
47drop view vw_ord;
48
49-- ordinality and multiple functions vs. rewind and reverse scan
50begin;
51declare foo scroll cursor for select * from rows from(generate_series(1,5),generate_series(1,2)) with ordinality as g(i,j,o);
52fetch all from foo;
53fetch backward all from foo;
54fetch all from foo;
55fetch next from foo;
56fetch next from foo;
57fetch prior from foo;
58fetch absolute 1 from foo;
59fetch next from foo;
60fetch next from foo;
61fetch next from foo;
62fetch prior from foo;
63fetch prior from foo;
64fetch prior from foo;
65commit;
66
67-- function with implicit LATERAL
68select * from foo2, foot(foo2.fooid) z where foo2.f2 = z.f2;
69
70-- function with implicit LATERAL and explicit ORDINALITY
71select * from foo2, foot(foo2.fooid) with ordinality as z(fooid,f2,ord) where foo2.f2 = z.f2;
72
73-- function in subselect
74select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = foo2.fooid) ORDER BY 1,2;
75
76-- function in subselect
77select * from foo2 where f2 in (select f2 from foot(1) z where z.fooid = foo2.fooid) ORDER BY 1,2;
78
79-- function in subselect
80select * from foo2 where f2 in (select f2 from foot(foo2.fooid) z where z.fooid = 1) ORDER BY 1,2;
81
82-- nested functions
83select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
84
85CREATE TABLE foo (fooid int, foosubid int, fooname text, primary key(fooid,foosubid));
86INSERT INTO foo VALUES(1,1,'Joe');
87INSERT INTO foo VALUES(1,2,'Ed');
88INSERT INTO foo VALUES(2,1,'Mary');
89
90-- sql, proretset = f, prorettype = b
91CREATE FUNCTION getfoo1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
92SELECT * FROM getfoo1(1) AS t1;
93SELECT * FROM getfoo1(1) WITH ORDINALITY AS t1(v,o);
94CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1);
95SELECT * FROM vw_getfoo;
96DROP VIEW vw_getfoo;
97CREATE VIEW vw_getfoo AS SELECT * FROM getfoo1(1) WITH ORDINALITY as t1(v,o);
98SELECT * FROM vw_getfoo;
99DROP VIEW vw_getfoo;
100
101-- sql, proretset = t, prorettype = b
102CREATE FUNCTION getfoo2(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
103SELECT * FROM getfoo2(1) AS t1;
104SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
105CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1);
106SELECT * FROM vw_getfoo;
107DROP VIEW vw_getfoo;
108CREATE VIEW vw_getfoo AS SELECT * FROM getfoo2(1) WITH ORDINALITY AS t1(v,o);
109SELECT * FROM vw_getfoo;
110DROP VIEW vw_getfoo;
111
112-- sql, proretset = t, prorettype = b
113CREATE FUNCTION getfoo3(int) RETURNS setof text AS 'SELECT fooname FROM foo WHERE fooid = $1;' LANGUAGE SQL;
114SELECT * FROM getfoo3(1) AS t1;
115SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
116CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1);
117SELECT * FROM vw_getfoo;
118DROP VIEW vw_getfoo;
119CREATE VIEW vw_getfoo AS SELECT * FROM getfoo3(1) WITH ORDINALITY AS t1(v,o);
120SELECT * FROM vw_getfoo;
121DROP VIEW vw_getfoo;
122
123-- sql, proretset = f, prorettype = c
124CREATE FUNCTION getfoo4(int) RETURNS foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
125SELECT * FROM getfoo4(1) AS t1;
126SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
127CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1);
128SELECT * FROM vw_getfoo;
129DROP VIEW vw_getfoo;
130CREATE VIEW vw_getfoo AS SELECT * FROM getfoo4(1) WITH ORDINALITY AS t1(a,b,c,o);
131SELECT * FROM vw_getfoo;
132DROP VIEW vw_getfoo;
133
134-- sql, proretset = t, prorettype = c
135CREATE FUNCTION getfoo5(int) RETURNS setof foo AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
136SELECT * FROM getfoo5(1) AS t1;
137SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
138CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1);
139SELECT * FROM vw_getfoo;
140DROP VIEW vw_getfoo;
141CREATE VIEW vw_getfoo AS SELECT * FROM getfoo5(1) WITH ORDINALITY AS t1(a,b,c,o);
142SELECT * FROM vw_getfoo;
143DROP VIEW vw_getfoo;
144
145-- sql, proretset = f, prorettype = record
146CREATE FUNCTION getfoo6(int) RETURNS RECORD AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
147SELECT * FROM getfoo6(1) AS t1(fooid int, foosubid int, fooname text);
148SELECT * FROM ROWS FROM( getfoo6(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
149CREATE VIEW vw_getfoo AS SELECT * FROM getfoo6(1) AS
150(fooid int, foosubid int, fooname text);
151SELECT * FROM vw_getfoo;
152DROP VIEW vw_getfoo;
153CREATE VIEW vw_getfoo AS
154  SELECT * FROM ROWS FROM( getfoo6(1) AS (fooid int, foosubid int, fooname text) )
155                WITH ORDINALITY;
156SELECT * FROM vw_getfoo;
157DROP VIEW vw_getfoo;
158
159-- sql, proretset = t, prorettype = record
160CREATE FUNCTION getfoo7(int) RETURNS setof record AS 'SELECT * FROM foo WHERE fooid = $1;' LANGUAGE SQL;
161SELECT * FROM getfoo7(1) AS t1(fooid int, foosubid int, fooname text);
162SELECT * FROM ROWS FROM( getfoo7(1) AS (fooid int, foosubid int, fooname text) ) WITH ORDINALITY;
163CREATE VIEW vw_getfoo AS SELECT * FROM getfoo7(1) AS
164(fooid int, foosubid int, fooname text);
165SELECT * FROM vw_getfoo;
166DROP VIEW vw_getfoo;
167CREATE VIEW vw_getfoo AS
168  SELECT * FROM ROWS FROM( getfoo7(1) AS (fooid int, foosubid int, fooname text) )
169                WITH ORDINALITY;
170SELECT * FROM vw_getfoo;
171DROP VIEW vw_getfoo;
172
173-- plpgsql, proretset = f, prorettype = b
174CREATE FUNCTION getfoo8(int) RETURNS int AS 'DECLARE fooint int; BEGIN SELECT fooid into fooint FROM foo WHERE fooid = $1; RETURN fooint; END;' LANGUAGE plpgsql;
175SELECT * FROM getfoo8(1) AS t1;
176SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
177CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1);
178SELECT * FROM vw_getfoo;
179DROP VIEW vw_getfoo;
180CREATE VIEW vw_getfoo AS SELECT * FROM getfoo8(1) WITH ORDINALITY AS t1(v,o);
181SELECT * FROM vw_getfoo;
182DROP VIEW vw_getfoo;
183
184-- plpgsql, proretset = f, prorettype = c
185CREATE FUNCTION getfoo9(int) RETURNS foo AS 'DECLARE footup foo%ROWTYPE; BEGIN SELECT * into footup FROM foo WHERE fooid = $1; RETURN footup; END;' LANGUAGE plpgsql;
186SELECT * FROM getfoo9(1) AS t1;
187SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
188CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1);
189SELECT * FROM vw_getfoo;
190DROP VIEW vw_getfoo;
191CREATE VIEW vw_getfoo AS SELECT * FROM getfoo9(1) WITH ORDINALITY AS t1(a,b,c,o);
192SELECT * FROM vw_getfoo;
193DROP VIEW vw_getfoo;
194
195-- mix 'n match kinds, to exercise expandRTE and related logic
196
197select * from rows from(getfoo1(1),getfoo2(1),getfoo3(1),getfoo4(1),getfoo5(1),
198                    getfoo6(1) AS (fooid int, foosubid int, fooname text),
199                    getfoo7(1) AS (fooid int, foosubid int, fooname text),
200                    getfoo8(1),getfoo9(1))
201              with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
202select * from rows from(getfoo9(1),getfoo8(1),
203                    getfoo7(1) AS (fooid int, foosubid int, fooname text),
204                    getfoo6(1) AS (fooid int, foosubid int, fooname text),
205                    getfoo5(1),getfoo4(1),getfoo3(1),getfoo2(1),getfoo1(1))
206              with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
207
208create temporary view vw_foo as
209  select * from rows from(getfoo9(1),
210                      getfoo7(1) AS (fooid int, foosubid int, fooname text),
211                      getfoo1(1))
212                with ordinality as t1(a,b,c,d,e,f,g,n);
213select * from vw_foo;
214select pg_get_viewdef('vw_foo');
215drop view vw_foo;
216
217DROP FUNCTION getfoo1(int);
218DROP FUNCTION getfoo2(int);
219DROP FUNCTION getfoo3(int);
220DROP FUNCTION getfoo4(int);
221DROP FUNCTION getfoo5(int);
222DROP FUNCTION getfoo6(int);
223DROP FUNCTION getfoo7(int);
224DROP FUNCTION getfoo8(int);
225DROP FUNCTION getfoo9(int);
226DROP FUNCTION foot(int);
227DROP TABLE foo2;
228DROP TABLE foo;
229
230-- Rescan tests --
231CREATE TEMPORARY SEQUENCE foo_rescan_seq1;
232CREATE TEMPORARY SEQUENCE foo_rescan_seq2;
233CREATE TYPE foo_rescan_t AS (i integer, s bigint);
234
235CREATE FUNCTION foo_sql(int,int) RETURNS setof foo_rescan_t AS 'SELECT i, nextval(''foo_rescan_seq1'') FROM generate_series($1,$2) i;' LANGUAGE SQL;
236-- plpgsql functions use materialize mode
237CREATE FUNCTION foo_mat(int,int) RETURNS setof foo_rescan_t AS 'begin for i in $1..$2 loop return next (i, nextval(''foo_rescan_seq2'')); end loop; end;' LANGUAGE plpgsql;
238
239--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
240-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
241-- is on the inner path of a nestloop join
242
243SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
244SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) ON (r+i)<100;
245SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
246SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
247
248SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
249SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) ON (r+i)<100;
250SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
251SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN foo_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
252SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
253SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN ROWS FROM( foo_sql(11,13), foo_mat(11,13) ) WITH ORDINALITY AS f(i1,s1,i2,s2,o) ON (r+i1+i2)<100;
254
255SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
256SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
257
258SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100;
259SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
260
261--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
262
263SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
264SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13);
265SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
266SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
267SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
268SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r);
269SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
270SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
271SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
272SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2);
273SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
274SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
275
276SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
277SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13);
278SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
279SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
280SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
281SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r);
282SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
283SELECT * FROM (VALUES (1),(2),(3)) v(r), foo_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
284SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
285SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2);
286SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
287SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), foo_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
288
289-- selective rescan of multiple functions:
290
291SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
292SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( foo_sql(11,11), foo_mat(10+r,13) );
293SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
294SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( foo_sql(10+r,13), foo_mat(11,11) );
295SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
296SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( foo_sql(10+r,13), foo_mat(10+r,13) );
297
298SELECT setval('foo_rescan_seq1',1,false),setval('foo_rescan_seq2',1,false);
299SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, ROWS FROM( foo_sql(10+r1,13), foo_mat(10+r2,13) );
300
301SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
302SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o);
303
304SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i);
305SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o);
306
307-- deep nesting
308
309SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
310              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
311                                         LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
312SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
313              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
314                                         LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
315SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
316              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
317                                         LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1;
318SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
319              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
320                                         LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1;
321
322-- check handling of FULL JOIN with multiple lateral references (bug #15741)
323
324SELECT *
325FROM (VALUES (1),(2)) v1(r1)
326    LEFT JOIN LATERAL (
327        SELECT *
328        FROM generate_series(1, v1.r1) AS gs1
329        LEFT JOIN LATERAL (
330            SELECT *
331            FROM generate_series(1, gs1) AS gs2
332            LEFT JOIN generate_series(1, gs2) AS gs3 ON TRUE
333        ) AS ss1 ON TRUE
334        FULL JOIN generate_series(1, v1.r1) AS gs4 ON FALSE
335    ) AS ss0 ON TRUE;
336
337DROP FUNCTION foo_sql(int,int);
338DROP FUNCTION foo_mat(int,int);
339DROP SEQUENCE foo_rescan_seq1;
340DROP SEQUENCE foo_rescan_seq2;
341
342--
343-- Test cases involving OUT parameters
344--
345
346CREATE FUNCTION foo(in f1 int, out f2 int)
347AS 'select $1+1' LANGUAGE sql;
348SELECT foo(42);
349SELECT * FROM foo(42);
350SELECT * FROM foo(42) AS p(x);
351
352-- explicit spec of return type is OK
353CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS int
354AS 'select $1+1' LANGUAGE sql;
355-- error, wrong result type
356CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int) RETURNS float
357AS 'select $1+1' LANGUAGE sql;
358-- with multiple OUT params you must get a RECORD result
359CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text) RETURNS int
360AS 'select $1+1' LANGUAGE sql;
361CREATE OR REPLACE FUNCTION foo(in f1 int, out f2 int, out f3 text)
362RETURNS record
363AS 'select $1+1' LANGUAGE sql;
364
365CREATE OR REPLACE FUNCTION foor(in f1 int, out f2 int, out text)
366AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
367SELECT f1, foor(f1) FROM int4_tbl;
368SELECT * FROM foor(42);
369SELECT * FROM foor(42) AS p(a,b);
370
371CREATE OR REPLACE FUNCTION foob(in f1 int, inout f2 int, out text)
372AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
373SELECT f1, foob(f1, f1/2) FROM int4_tbl;
374SELECT * FROM foob(42, 99);
375SELECT * FROM foob(42, 99) AS p(a,b);
376
377-- Can reference function with or without OUT params for DROP, etc
378DROP FUNCTION foo(int);
379DROP FUNCTION foor(in f2 int, out f1 int, out text);
380DROP FUNCTION foob(in f1 int, inout f2 int);
381
382--
383-- For my next trick, polymorphic OUT parameters
384--
385
386CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
387AS 'select $1, array[$1,$1]' LANGUAGE sql;
388SELECT dup(22);
389SELECT dup('xyz');	-- fails
390SELECT dup('xyz'::text);
391SELECT * FROM dup('xyz'::text);
392
393-- fails, as we are attempting to rename first argument
394CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
395AS 'select $1, array[$1,$1]' LANGUAGE sql;
396
397DROP FUNCTION dup(anyelement);
398
399-- equivalent behavior, though different name exposed for input arg
400CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
401AS 'select $1, array[$1,$1]' LANGUAGE sql;
402SELECT dup(22);
403
404DROP FUNCTION dup(anyelement);
405
406-- fails, no way to deduce outputs
407CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
408AS 'select $1, array[$1,$1]' LANGUAGE sql;
409
410--
411-- table functions
412--
413
414CREATE OR REPLACE FUNCTION foo()
415RETURNS TABLE(a int)
416AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql;
417SELECT * FROM foo();
418DROP FUNCTION foo();
419
420CREATE OR REPLACE FUNCTION foo(int)
421RETURNS TABLE(a int, b int)
422AS $$ SELECT a, b
423         FROM generate_series(1,$1) a(a),
424              generate_series(1,$1) b(b) $$ LANGUAGE sql;
425SELECT * FROM foo(3);
426DROP FUNCTION foo(int);
427
428-- case that causes change of typmod knowledge during inlining
429CREATE OR REPLACE FUNCTION foo()
430RETURNS TABLE(a varchar(5))
431AS $$ SELECT 'hello'::varchar(5) $$ LANGUAGE sql STABLE;
432SELECT * FROM foo() GROUP BY 1;
433DROP FUNCTION foo();
434
435--
436-- some tests on SQL functions with RETURNING
437--
438
439create temp table tt(f1 serial, data text);
440
441create function insert_tt(text) returns int as
442$$ insert into tt(data) values($1) returning f1 $$
443language sql;
444
445select insert_tt('foo');
446select insert_tt('bar');
447select * from tt;
448
449-- insert will execute to completion even if function needs just 1 row
450create or replace function insert_tt(text) returns int as
451$$ insert into tt(data) values($1),($1||$1) returning f1 $$
452language sql;
453
454select insert_tt('fool');
455select * from tt;
456
457-- setof does what's expected
458create or replace function insert_tt2(text,text) returns setof int as
459$$ insert into tt(data) values($1),($2) returning f1 $$
460language sql;
461
462select insert_tt2('foolish','barrish');
463select * from insert_tt2('baz','quux');
464select * from tt;
465
466-- limit doesn't prevent execution to completion
467select insert_tt2('foolish','barrish') limit 1;
468select * from tt;
469
470-- triggers will fire, too
471create function noticetrigger() returns trigger as $$
472begin
473  raise notice 'noticetrigger % %', new.f1, new.data;
474  return null;
475end $$ language plpgsql;
476create trigger tnoticetrigger after insert on tt for each row
477execute procedure noticetrigger();
478
479select insert_tt2('foolme','barme') limit 1;
480select * from tt;
481
482-- and rules work
483create temp table tt_log(f1 int, data text);
484
485create rule insert_tt_rule as on insert to tt do also
486  insert into tt_log values(new.*);
487
488select insert_tt2('foollog','barlog') limit 1;
489select * from tt;
490-- note that nextval() gets executed a second time in the rule expansion,
491-- which is expected.
492select * from tt_log;
493
494-- test case for a whole-row-variable bug
495create function foo1(n integer, out a text, out b text)
496  returns setof record
497  language sql
498  as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$;
499
500set work_mem='64kB';
501select t.a, t, t.a from foo1(10000) t limit 1;
502reset work_mem;
503select t.a, t, t.a from foo1(10000) t limit 1;
504
505drop function foo1(n integer);
506
507-- test use of SQL functions returning record
508-- this is supported in some cases where the query doesn't specify
509-- the actual record type ...
510
511create function array_to_set(anyarray) returns setof record as $$
512  select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i
513$$ language sql strict immutable;
514
515select array_to_set(array['one', 'two']);
516select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text);
517select * from array_to_set(array['one', 'two']); -- fail
518
519create temp table foo(f1 int8, f2 int8);
520
521create function testfoo() returns record as $$
522  insert into foo values (1,2) returning *;
523$$ language sql;
524
525select testfoo();
526select * from testfoo() as t(f1 int8,f2 int8);
527select * from testfoo(); -- fail
528
529drop function testfoo();
530
531create function testfoo() returns setof record as $$
532  insert into foo values (1,2), (3,4) returning *;
533$$ language sql;
534
535select testfoo();
536select * from testfoo() as t(f1 int8,f2 int8);
537select * from testfoo(); -- fail
538
539drop function testfoo();
540
541--
542-- Check some cases involving added/dropped columns in a rowtype result
543--
544
545create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool);
546insert into users values ('id',1,'email',true,11,true);
547insert into users values ('id2',2,'email2',true,12,true);
548alter table users drop column todrop;
549
550create or replace function get_first_user() returns users as
551$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$
552language sql stable;
553
554SELECT get_first_user();
555SELECT * FROM get_first_user();
556
557create or replace function get_users() returns setof users as
558$$ SELECT * FROM users ORDER BY userid; $$
559language sql stable;
560
561SELECT get_users();
562SELECT * FROM get_users();
563SELECT * FROM get_users() WITH ORDINALITY;   -- make sure ordinality copes
564
565-- multiple functions vs. dropped columns
566SELECT * FROM ROWS FROM(generate_series(10,11), get_users()) WITH ORDINALITY;
567SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;
568
569-- check that we can cope with post-parsing changes in rowtypes
570create temp view usersview as
571SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY;
572
573select * from usersview;
574alter table users add column junk text;
575select * from usersview;
576begin;
577alter table users drop column moredrop;
578select * from usersview;  -- expect clean failure
579rollback;
580alter table users alter column seq type numeric;
581select * from usersview;  -- expect clean failure
582
583drop view usersview;
584drop function get_first_user();
585drop function get_users();
586drop table users;
587
588-- this won't get inlined because of type coercion, but it shouldn't fail
589
590create or replace function foobar() returns setof text as
591$$ select 'foo'::varchar union all select 'bar'::varchar ; $$
592language sql stable;
593
594select foobar();
595select * from foobar();
596
597drop function foobar();
598
599-- check handling of a SQL function with multiple OUT params (bug #5777)
600
601create or replace function foobar(out integer, out numeric) as
602$$ select (1, 2.1) $$ language sql;
603
604select * from foobar();
605
606create or replace function foobar(out integer, out numeric) as
607$$ select (1, 2) $$ language sql;
608
609select * from foobar();  -- fail
610
611create or replace function foobar(out integer, out numeric) as
612$$ select (1, 2.1, 3) $$ language sql;
613
614select * from foobar();  -- fail
615
616drop function foobar();
617
618-- check whole-row-Var handling in nested lateral functions (bug #11703)
619
620create function extractq2(t int8_tbl) returns int8 as $$
621  select t.q2
622$$ language sql immutable;
623
624explain (verbose, costs off)
625select x from int8_tbl, extractq2(int8_tbl) f(x);
626
627select x from int8_tbl, extractq2(int8_tbl) f(x);
628
629create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$
630  select extractq2(t) offset 0
631$$ language sql immutable;
632
633explain (verbose, costs off)
634select x from int8_tbl, extractq2_2(int8_tbl) f(x);
635
636select x from int8_tbl, extractq2_2(int8_tbl) f(x);
637
638-- without the "offset 0", this function gets optimized quite differently
639
640create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$
641  select extractq2(t)
642$$ language sql immutable;
643
644explain (verbose, costs off)
645select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
646
647select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x);
648
649-- check handling of nulls in SRF results (bug #7808)
650
651create type foo2 as (a integer, b text);
652
653select *, row_to_json(u) from unnest(array[(1,'foo')::foo2, null::foo2]) u;
654select *, row_to_json(u) from unnest(array[null::foo2, null::foo2]) u;
655select *, row_to_json(u) from unnest(array[null::foo2, (1,'foo')::foo2, null::foo2]) u;
656select *, row_to_json(u) from unnest(array[]::foo2[]) u;
657
658drop type foo2;
659