CREATE TABLE rngfunc2(rngfuncid int, f2 int); INSERT INTO rngfunc2 VALUES(1, 11); INSERT INTO rngfunc2 VALUES(2, 22); INSERT INTO rngfunc2 VALUES(1, 111); CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL; -- function with ORDINALITY select * from rngfunct(1) with ordinality as z(a,b,ord); a | b | ord ---+-----+----- 1 | 11 | 1 1 | 111 | 2 (2 rows) select * from rngfunct(1) with ordinality as z(a,b,ord) where b > 100; -- ordinal 2, not 1 a | b | ord ---+-----+----- 1 | 111 | 2 (1 row) -- ordinality vs. column names and types select a,b,ord from rngfunct(1) with ordinality as z(a,b,ord); a | b | ord ---+-----+----- 1 | 11 | 1 1 | 111 | 2 (2 rows) select a,ord from unnest(array['a','b']) with ordinality as z(a,ord); a | ord ---+----- a | 1 b | 2 (2 rows) select * from unnest(array['a','b']) with ordinality as z(a,ord); a | ord ---+----- a | 1 b | 2 (2 rows) select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord); a | ord ---+----- 1 | 1 (1 row) select * from unnest(array[1.0::float8]) with ordinality as z(a,ord); a | ord ---+----- 1 | 1 (1 row) select row_to_json(s.*) from generate_series(11,14) with ordinality s; row_to_json ------------------------- {"s":11,"ordinality":1} {"s":12,"ordinality":2} {"s":13,"ordinality":3} {"s":14,"ordinality":4} (4 rows) -- ordinality vs. views create temporary view vw_ord as select * from (values (1)) v(n) join rngfunct(1) with ordinality as z(a,b,ord) on (n=ord); select * from vw_ord; n | a | b | ord ---+---+----+----- 1 | 1 | 11 | 1 (1 row) select definition from pg_views where viewname='vw_ord'; definition ------------------------------------------------------------------------- SELECT v.n, + z.a, + z.b, + z.ord + FROM (( VALUES (1)) v(n) + JOIN rngfunct(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord))); (1 row) drop view vw_ord; -- multiple functions select * from rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord); a | b | c | d | ord ---+-----+---+----+----- 1 | 11 | 2 | 22 | 1 1 | 111 | | | 2 (2 rows) create temporary view vw_ord as select * from (values (1)) v(n) join rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord) on (n=ord); select * from vw_ord; n | a | b | c | d | ord ---+---+----+---+----+----- 1 | 1 | 11 | 2 | 22 | 1 (1 row) select definition from pg_views where viewname='vw_ord'; definition ------------------------------------------------------------------------------------------------------- SELECT v.n, + z.a, + z.b, + z.c, + z.d, + z.ord + FROM (( VALUES (1)) v(n) + JOIN ROWS FROM(rngfunct(1), rngfunct(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord))); (1 row) drop view vw_ord; -- expansions of unnest() select * from unnest(array[10,20],array['foo','bar'],array[1.0]); unnest | unnest | unnest --------+--------+-------- 10 | foo | 1.0 20 | bar | (2 rows) select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord); a | b | c | ord ----+-----+-----+----- 10 | foo | 1.0 | 1 20 | bar | | 2 (2 rows) select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord); a | b | c | ord ----+-----+-----+----- 10 | foo | 1.0 | 1 20 | bar | | 2 (2 rows) select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as z(a,b,c,ord); a | b | c | ord ----+-----+-----+----- 10 | foo | 101 | 1 20 | bar | 102 | 2 (2 rows) create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c); select * from vw_ord; a | b | c ----+-----+----- 10 | foo | 1.0 20 | bar | (2 rows) select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------- SELECT z.a, + z.b, + z.c + FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c); (1 row) drop view vw_ord; create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c); select * from vw_ord; a | b | c ----+-----+----- 10 | foo | 1.0 20 | bar | (2 rows) select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------- SELECT z.a, + z.b, + z.c + FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c); (1 row) drop view vw_ord; create 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); select * from vw_ord; a | b | c ----+-----+--- 10 | foo | 1 20 | bar | 2 (2 rows) select definition from pg_views where viewname='vw_ord'; definition ---------------------------------------------------------------------------------------------------------------------- SELECT z.a, + z.b, + z.c + FROM ROWS FROM(unnest(ARRAY[10, 20]), unnest(ARRAY['foo'::text, 'bar'::text]), generate_series(1, 2)) z(a, b, c); (1 row) drop view vw_ord; -- ordinality and multiple functions vs. rewind and reverse scan begin; declare rf_cur scroll cursor for select * from rows from(generate_series(1,5),generate_series(1,2)) with ordinality as g(i,j,o); fetch all from rf_cur; i | j | o ---+---+--- 1 | 1 | 1 2 | 2 | 2 3 | | 3 4 | | 4 5 | | 5 (5 rows) fetch backward all from rf_cur; i | j | o ---+---+--- 5 | | 5 4 | | 4 3 | | 3 2 | 2 | 2 1 | 1 | 1 (5 rows) fetch all from rf_cur; i | j | o ---+---+--- 1 | 1 | 1 2 | 2 | 2 3 | | 3 4 | | 4 5 | | 5 (5 rows) fetch next from rf_cur; i | j | o ---+---+--- (0 rows) fetch next from rf_cur; i | j | o ---+---+--- (0 rows) fetch prior from rf_cur; i | j | o ---+---+--- 5 | | 5 (1 row) fetch absolute 1 from rf_cur; i | j | o ---+---+--- 1 | 1 | 1 (1 row) fetch next from rf_cur; i | j | o ---+---+--- 2 | 2 | 2 (1 row) fetch next from rf_cur; i | j | o ---+---+--- 3 | | 3 (1 row) fetch next from rf_cur; i | j | o ---+---+--- 4 | | 4 (1 row) fetch prior from rf_cur; i | j | o ---+---+--- 3 | | 3 (1 row) fetch prior from rf_cur; i | j | o ---+---+--- 2 | 2 | 2 (1 row) fetch prior from rf_cur; i | j | o ---+---+--- 1 | 1 | 1 (1 row) commit; -- function with implicit LATERAL select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2; rngfuncid | f2 | rngfuncid | f2 -----------+-----+-----------+----- 1 | 11 | 1 | 11 2 | 22 | 2 | 22 1 | 111 | 1 | 111 (3 rows) -- function with implicit LATERAL and explicit ORDINALITY select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) with ordinality as z(rngfuncid,f2,ord) where rngfunc2.f2 = z.f2; rngfuncid | f2 | rngfuncid | f2 | ord -----------+-----+-----------+-----+----- 1 | 11 | 1 | 11 | 1 2 | 22 | 2 | 22 | 1 1 | 111 | 1 | 111 | 2 (3 rows) -- function in subselect select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2; rngfuncid | f2 -----------+----- 1 | 11 1 | 111 2 | 22 (3 rows) -- function in subselect select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2; rngfuncid | f2 -----------+----- 1 | 11 1 | 111 (2 rows) -- function in subselect select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2; rngfuncid | f2 -----------+----- 1 | 11 1 | 111 (2 rows) -- nested functions select rngfunct.rngfuncid, rngfunct.f2 from rngfunct(sin(pi()/2)::int) ORDER BY 1,2; rngfuncid | f2 -----------+----- 1 | 11 1 | 111 (2 rows) CREATE TABLE rngfunc (rngfuncid int, rngfuncsubid int, rngfuncname text, primary key(rngfuncid,rngfuncsubid)); INSERT INTO rngfunc VALUES(1,1,'Joe'); INSERT INTO rngfunc VALUES(1,2,'Ed'); INSERT INTO rngfunc VALUES(2,1,'Mary'); -- sql, proretset = f, prorettype = b CREATE FUNCTION getrngfunc1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL; SELECT * FROM getrngfunc1(1) AS t1; t1 ---- 1 (1 row) SELECT * FROM getrngfunc1(1) WITH ORDINALITY AS t1(v,o); v | o ---+--- 1 | 1 (1 row) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1); SELECT * FROM vw_getrngfunc; getrngfunc1 ------------- 1 (1 row) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1) WITH ORDINALITY as t1(v,o); SELECT * FROM vw_getrngfunc; v | o ---+--- 1 | 1 (1 row) DROP VIEW vw_getrngfunc; -- sql, proretset = t, prorettype = b CREATE FUNCTION getrngfunc2(int) RETURNS setof int AS 'SELECT rngfuncid FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc2(1) AS t1; t1 ---- 1 1 (2 rows) SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o); v | o ---+--- 1 | 1 1 | 2 (2 rows) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1); SELECT * FROM vw_getrngfunc; getrngfunc2 ------------- 1 1 (2 rows) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o); SELECT * FROM vw_getrngfunc; v | o ---+--- 1 | 1 1 | 2 (2 rows) DROP VIEW vw_getrngfunc; -- sql, proretset = t, prorettype = b CREATE FUNCTION getrngfunc3(int) RETURNS setof text AS 'SELECT rngfuncname FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc3(1) AS t1; t1 ----- Joe Ed (2 rows) SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o); v | o -----+--- Joe | 1 Ed | 2 (2 rows) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1); SELECT * FROM vw_getrngfunc; getrngfunc3 ------------- Joe Ed (2 rows) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o); SELECT * FROM vw_getrngfunc; v | o -----+--- Joe | 1 Ed | 2 (2 rows) DROP VIEW vw_getrngfunc; -- sql, proretset = f, prorettype = c CREATE FUNCTION getrngfunc4(int) RETURNS rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc4(1) AS t1; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o); a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 (1 row) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1); SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o); SELECT * FROM vw_getrngfunc; a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 (1 row) DROP VIEW vw_getrngfunc; -- sql, proretset = t, prorettype = c CREATE FUNCTION getrngfunc5(int) RETURNS setof rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc5(1) AS t1; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o); a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 1 | 2 | Ed | 2 (2 rows) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1); SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o); SELECT * FROM vw_getrngfunc; a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 1 | 2 | Ed | 2 (2 rows) DROP VIEW vw_getrngfunc; -- sql, proretset = f, prorettype = record CREATE FUNCTION getrngfunc6(int) RETURNS RECORD AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc6(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text); rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; rngfuncid | rngfuncsubid | rngfuncname | ordinality -----------+--------------+-------------+------------ 1 | 1 | Joe | 1 (1 row) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text); SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname | ordinality -----------+--------------+-------------+------------ 1 | 1 | Joe | 1 (1 row) DROP VIEW vw_getrngfunc; -- sql, proretset = t, prorettype = record CREATE FUNCTION getrngfunc7(int) RETURNS setof record AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; SELECT * FROM getrngfunc7(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text); rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; rngfuncid | rngfuncsubid | rngfuncname | ordinality -----------+--------------+-------------+------------ 1 | 1 | Joe | 1 1 | 2 | Ed | 2 (2 rows) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text); SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe 1 | 2 | Ed (2 rows) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname | ordinality -----------+--------------+-------------+------------ 1 | 1 | Joe | 1 1 | 2 | Ed | 2 (2 rows) DROP VIEW vw_getrngfunc; -- plpgsql, proretset = f, prorettype = b CREATE FUNCTION getrngfunc8(int) RETURNS int AS 'DECLARE rngfuncint int; BEGIN SELECT rngfuncid into rngfuncint FROM rngfunc WHERE rngfuncid = $1; RETURN rngfuncint; END;' LANGUAGE plpgsql; SELECT * FROM getrngfunc8(1) AS t1; t1 ---- 1 (1 row) SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o); v | o ---+--- 1 | 1 (1 row) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1); SELECT * FROM vw_getrngfunc; getrngfunc8 ------------- 1 (1 row) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o); SELECT * FROM vw_getrngfunc; v | o ---+--- 1 | 1 (1 row) DROP VIEW vw_getrngfunc; -- plpgsql, proretset = f, prorettype = c CREATE FUNCTION getrngfunc9(int) RETURNS rngfunc AS 'DECLARE rngfunctup rngfunc%ROWTYPE; BEGIN SELECT * into rngfunctup FROM rngfunc WHERE rngfuncid = $1; RETURN rngfunctup; END;' LANGUAGE plpgsql; SELECT * FROM getrngfunc9(1) AS t1; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o); a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 (1 row) CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1); SELECT * FROM vw_getrngfunc; rngfuncid | rngfuncsubid | rngfuncname -----------+--------------+------------- 1 | 1 | Joe (1 row) DROP VIEW vw_getrngfunc; CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o); SELECT * FROM vw_getrngfunc; a | b | c | o ---+---+-----+--- 1 | 1 | Joe | 1 (1 row) DROP VIEW vw_getrngfunc; -- mix 'n match kinds, to exercise expandRTE and related logic select * from rows from(getrngfunc1(1),getrngfunc2(1),getrngfunc3(1),getrngfunc4(1),getrngfunc5(1), getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), getrngfunc8(1),getrngfunc9(1)) with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u); a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u ---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+---+-----+--- 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1 | 1 | Ed | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | | 2 (2 rows) select * from rows from(getrngfunc9(1),getrngfunc8(1), getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), getrngfunc5(1),getrngfunc4(1),getrngfunc3(1),getrngfunc2(1),getrngfunc1(1)) with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u); a | b | c | d | e | f | g | h | i | j | k | l | m | o | p | q | r | s | t | u ---+---+-----+---+---+---+-----+---+---+-----+---+---+-----+---+---+-----+-----+---+---+--- 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | Joe | 1 | 1 | 1 | | | | 1 | 2 | Ed | | | | 1 | 2 | Ed | | | | Ed | 1 | | 2 (2 rows) create temporary view vw_rngfunc as select * from rows from(getrngfunc9(1), getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), getrngfunc1(1)) with ordinality as t1(a,b,c,d,e,f,g,n); select * from vw_rngfunc; a | b | c | d | e | f | g | n ---+---+-----+---+---+-----+---+--- 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | | | 1 | 2 | Ed | | 2 (2 rows) select pg_get_viewdef('vw_rngfunc'); pg_get_viewdef ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SELECT t1.a, + t1.b, + t1.c, + t1.d, + t1.e, + t1.f, + t1.g, + t1.n + FROM ROWS FROM(getrngfunc9(1), getrngfunc7(1) AS (rngfuncid integer, rngfuncsubid integer, rngfuncname text), getrngfunc1(1)) WITH ORDINALITY t1(a, b, c, d, e, f, g, n); (1 row) drop view vw_rngfunc; DROP FUNCTION getrngfunc1(int); DROP FUNCTION getrngfunc2(int); DROP FUNCTION getrngfunc3(int); DROP FUNCTION getrngfunc4(int); DROP FUNCTION getrngfunc5(int); DROP FUNCTION getrngfunc6(int); DROP FUNCTION getrngfunc7(int); DROP FUNCTION getrngfunc8(int); DROP FUNCTION getrngfunc9(int); DROP FUNCTION rngfunct(int); DROP TABLE rngfunc2; DROP TABLE rngfunc; -- Rescan tests -- CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq1; CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq2; CREATE TYPE rngfunc_rescan_t AS (i integer, s bigint); CREATE FUNCTION rngfunc_sql(int,int) RETURNS setof rngfunc_rescan_t AS 'SELECT i, nextval(''rngfunc_rescan_seq1'') FROM generate_series($1,$2) i;' LANGUAGE SQL; -- plpgsql functions use materialize mode CREATE FUNCTION rngfunc_mat(int,int) RETURNS setof rngfunc_rescan_t AS 'begin for i in $1..$2 loop return next (i, nextval(''rngfunc_rescan_seq2'')); end loop; end;' LANGUAGE plpgsql; --invokes ExecReScanFunctionScan - all these cases should materialize the function only once -- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function -- is on the inner path of a nestloop join SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) ON (r+i)<100; r | i | s ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 2 | 11 | 1 2 | 12 | 2 2 | 13 | 3 3 | 11 | 1 3 | 12 | 2 3 | 13 | 3 (9 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100; r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 1 | 12 | 2 | 2 1 | 13 | 3 | 3 2 | 11 | 1 | 1 2 | 12 | 2 | 2 2 | 13 | 3 | 3 3 | 11 | 1 | 1 3 | 12 | 2 | 2 3 | 13 | 3 | 3 (9 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) ON (r+i)<100; r | i | s ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 2 | 11 | 1 2 | 12 | 2 2 | 13 | 3 3 | 11 | 1 3 | 12 | 2 3 | 13 | 3 (9 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100; r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 1 | 12 | 2 | 2 1 | 13 | 3 | 3 2 | 11 | 1 | 1 2 | 12 | 2 | 2 2 | 13 | 3 | 3 3 | 11 | 1 | 1 3 | 12 | 2 | 2 3 | 13 | 3 | 3 (9 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN ROWS FROM( rngfunc_sql(11,13), rngfunc_mat(11,13) ) WITH ORDINALITY AS f(i1,s1,i2,s2,o) ON (r+i1+i2)<100; r | i1 | s1 | i2 | s2 | o ---+----+----+----+----+--- 1 | 11 | 1 | 11 | 1 | 1 1 | 12 | 2 | 12 | 2 | 2 1 | 13 | 3 | 13 | 3 | 3 2 | 11 | 1 | 11 | 1 | 1 2 | 12 | 2 | 12 | 2 | 2 2 | 13 | 3 | 13 | 3 | 3 3 | 11 | 1 | 11 | 1 | 1 3 | 12 | 2 | 12 | 2 | 2 3 | 13 | 3 | 13 | 3 | 3 (9 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100; r | i ---+---- 1 | 11 1 | 12 1 | 13 2 | 11 2 | 12 2 | 13 3 | 11 3 | 12 3 | 13 (9 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100; r | i | o ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 2 | 11 | 1 2 | 12 | 2 2 | 13 | 3 3 | 11 | 1 3 | 12 | 2 3 | 13 | 3 (9 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100; r | i ---+---- 1 | 10 1 | 20 1 | 30 2 | 10 2 | 20 2 | 30 3 | 10 3 | 20 3 | 30 (9 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100; r | i | o ---+----+--- 1 | 10 | 1 1 | 20 | 2 1 | 30 | 3 2 | 10 | 1 2 | 20 | 2 2 | 30 | 3 3 | 10 | 1 3 | 20 | 2 3 | 30 | 3 (9 rows) --invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13); r | i | s ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 2 | 12 | 4 2 | 13 | 5 3 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13) WITH ORDINALITY AS f(i,s,o); r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 1 | 12 | 2 | 2 1 | 13 | 3 | 3 2 | 12 | 4 | 1 2 | 13 | 5 | 2 3 | 13 | 6 | 1 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r); r | i | s ---+----+--- 1 | 11 | 1 2 | 11 | 2 2 | 12 | 3 3 | 11 | 4 3 | 12 | 5 3 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r) WITH ORDINALITY AS f(i,s,o); r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 2 | 11 | 2 | 1 2 | 12 | 3 | 2 3 | 11 | 4 | 1 3 | 12 | 5 | 2 3 | 13 | 6 | 3 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2); r1 | r2 | i | s ----+----+----+---- 11 | 12 | 11 | 1 11 | 12 | 12 | 2 13 | 15 | 13 | 3 13 | 15 | 14 | 4 13 | 15 | 15 | 5 16 | 20 | 16 | 6 16 | 20 | 17 | 7 16 | 20 | 18 | 8 16 | 20 | 19 | 9 16 | 20 | 20 | 10 (10 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2) WITH ORDINALITY AS f(i,s,o); r1 | r2 | i | s | o ----+----+----+----+--- 11 | 12 | 11 | 1 | 1 11 | 12 | 12 | 2 | 2 13 | 15 | 13 | 3 | 1 13 | 15 | 14 | 4 | 2 13 | 15 | 15 | 5 | 3 16 | 20 | 16 | 6 | 1 16 | 20 | 17 | 7 | 2 16 | 20 | 18 | 8 | 3 16 | 20 | 19 | 9 | 4 16 | 20 | 20 | 10 | 5 (10 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13); r | i | s ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 2 | 12 | 4 2 | 13 | 5 3 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13) WITH ORDINALITY AS f(i,s,o); r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 1 | 12 | 2 | 2 1 | 13 | 3 | 3 2 | 12 | 4 | 1 2 | 13 | 5 | 2 3 | 13 | 6 | 1 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r); r | i | s ---+----+--- 1 | 11 | 1 2 | 11 | 2 2 | 12 | 3 3 | 11 | 4 3 | 12 | 5 3 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r) WITH ORDINALITY AS f(i,s,o); r | i | s | o ---+----+---+--- 1 | 11 | 1 | 1 2 | 11 | 2 | 1 2 | 12 | 3 | 2 3 | 11 | 4 | 1 3 | 12 | 5 | 2 3 | 13 | 6 | 3 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2); r1 | r2 | i | s ----+----+----+---- 11 | 12 | 11 | 1 11 | 12 | 12 | 2 13 | 15 | 13 | 3 13 | 15 | 14 | 4 13 | 15 | 15 | 5 16 | 20 | 16 | 6 16 | 20 | 17 | 7 16 | 20 | 18 | 8 16 | 20 | 19 | 9 16 | 20 | 20 | 10 (10 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2) WITH ORDINALITY AS f(i,s,o); r1 | r2 | i | s | o ----+----+----+----+--- 11 | 12 | 11 | 1 | 1 11 | 12 | 12 | 2 | 2 13 | 15 | 13 | 3 | 1 13 | 15 | 14 | 4 | 2 13 | 15 | 15 | 5 | 3 16 | 20 | 16 | 6 | 1 16 | 20 | 17 | 7 | 2 16 | 20 | 18 | 8 | 3 16 | 20 | 19 | 9 | 4 16 | 20 | 20 | 10 | 5 (10 rows) -- selective rescan of multiple functions: SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(11,11), rngfunc_mat(10+r,13) ); r | i | s | i | s ---+----+---+----+--- 1 | 11 | 1 | 11 | 1 1 | | | 12 | 2 1 | | | 13 | 3 2 | 11 | 1 | 12 | 4 2 | | | 13 | 5 3 | 11 | 1 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(11,11) ); r | i | s | i | s ---+----+---+----+--- 1 | 11 | 1 | 11 | 1 1 | 12 | 2 | | 1 | 13 | 3 | | 2 | 12 | 4 | 11 | 1 2 | 13 | 5 | | 3 | 13 | 6 | 11 | 1 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(10+r,13) ); r | i | s | i | s ---+----+---+----+--- 1 | 11 | 1 | 11 | 1 1 | 12 | 2 | 12 | 2 1 | 13 | 3 | 13 | 3 2 | 12 | 4 | 12 | 4 2 | 13 | 5 | 13 | 5 3 | 13 | 6 | 13 | 6 (6 rows) SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); setval | setval --------+-------- 1 | 1 (1 row) SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, ROWS FROM( rngfunc_sql(10+r1,13), rngfunc_mat(10+r2,13) ); r1 | r2 | i | s | i | s ----+----+----+----+----+--- 1 | 1 | 11 | 1 | 11 | 1 1 | 1 | 12 | 2 | 12 | 2 1 | 1 | 13 | 3 | 13 | 3 1 | 2 | 11 | 4 | 12 | 4 1 | 2 | 12 | 5 | 13 | 5 1 | 2 | 13 | 6 | | 1 | 3 | 11 | 7 | 13 | 6 1 | 3 | 12 | 8 | | 1 | 3 | 13 | 9 | | 2 | 2 | 12 | 10 | 12 | 7 2 | 2 | 13 | 11 | 13 | 8 2 | 3 | 12 | 12 | 13 | 9 2 | 3 | 13 | 13 | | (13 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i); r | i ---+---- 1 | 11 1 | 12 1 | 13 1 | 14 1 | 15 1 | 16 1 | 17 1 | 18 1 | 19 2 | 12 2 | 13 2 | 14 2 | 15 2 | 16 2 | 17 2 | 18 3 | 13 3 | 14 3 | 15 3 | 16 3 | 17 (21 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o); r | i | o ---+----+--- 1 | 11 | 1 1 | 12 | 2 1 | 13 | 3 1 | 14 | 4 1 | 15 | 5 1 | 16 | 6 1 | 17 | 7 1 | 18 | 8 1 | 19 | 9 2 | 12 | 1 2 | 13 | 2 2 | 14 | 3 2 | 15 | 4 2 | 16 | 5 2 | 17 | 6 2 | 18 | 7 3 | 13 | 1 3 | 14 | 2 3 | 15 | 3 3 | 16 | 4 3 | 17 | 5 (21 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i); r | i ---+---- 1 | 10 1 | 20 1 | 30 2 | 20 2 | 40 2 | 60 3 | 30 3 | 60 3 | 90 (9 rows) SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o); r | i | o ---+----+--- 1 | 10 | 1 1 | 20 | 2 1 | 30 | 3 2 | 20 | 1 2 | 40 | 2 2 | 60 | 3 3 | 30 | 1 3 | 60 | 2 3 | 90 | 3 (9 rows) -- deep nesting SELECT * FROM (VALUES (1),(2),(3)) v1(r1), LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; r1 | r1 | r2 | i ----+----+----+---- 1 | 1 | 10 | 21 1 | 1 | 10 | 22 1 | 1 | 10 | 23 1 | 1 | 20 | 21 1 | 1 | 20 | 22 1 | 1 | 20 | 23 1 | 1 | 30 | 21 1 | 1 | 30 | 22 1 | 1 | 30 | 23 2 | 2 | 10 | 21 2 | 2 | 10 | 22 2 | 2 | 10 | 23 2 | 2 | 20 | 21 2 | 2 | 20 | 22 2 | 2 | 20 | 23 2 | 2 | 30 | 21 2 | 2 | 30 | 22 2 | 2 | 30 | 23 3 | 3 | 10 | 21 3 | 3 | 10 | 22 3 | 3 | 10 | 23 3 | 3 | 20 | 21 3 | 3 | 20 | 22 3 | 3 | 20 | 23 3 | 3 | 30 | 21 3 | 3 | 30 | 22 3 | 3 | 30 | 23 (27 rows) SELECT * FROM (VALUES (1),(2),(3)) v1(r1), LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1; r1 | r1 | r2 | i ----+----+----+---- 1 | 1 | 10 | 21 1 | 1 | 10 | 22 1 | 1 | 10 | 23 1 | 1 | 20 | 21 1 | 1 | 20 | 22 1 | 1 | 20 | 23 1 | 1 | 30 | 21 1 | 1 | 30 | 22 1 | 1 | 30 | 23 2 | 2 | 10 | 22 2 | 2 | 10 | 23 2 | 2 | 20 | 22 2 | 2 | 20 | 23 2 | 2 | 30 | 22 2 | 2 | 30 | 23 3 | 3 | 10 | 23 3 | 3 | 20 | 23 3 | 3 | 30 | 23 (18 rows) SELECT * FROM (VALUES (1),(2),(3)) v1(r1), LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1; r1 | r1 | r2 | i ----+----+----+---- 1 | 1 | 10 | 10 1 | 1 | 10 | 11 1 | 1 | 10 | 12 1 | 1 | 10 | 13 1 | 1 | 20 | 20 1 | 1 | 20 | 21 1 | 1 | 20 | 22 1 | 1 | 20 | 23 1 | 1 | 30 | 30 1 | 1 | 30 | 31 1 | 1 | 30 | 32 1 | 1 | 30 | 33 2 | 2 | 10 | 10 2 | 2 | 10 | 11 2 | 2 | 10 | 12 2 | 2 | 10 | 13 2 | 2 | 20 | 20 2 | 2 | 20 | 21 2 | 2 | 20 | 22 2 | 2 | 20 | 23 2 | 2 | 30 | 30 2 | 2 | 30 | 31 2 | 2 | 30 | 32 2 | 2 | 30 | 33 3 | 3 | 10 | 10 3 | 3 | 10 | 11 3 | 3 | 10 | 12 3 | 3 | 10 | 13 3 | 3 | 20 | 20 3 | 3 | 20 | 21 3 | 3 | 20 | 22 3 | 3 | 20 | 23 3 | 3 | 30 | 30 3 | 3 | 30 | 31 3 | 3 | 30 | 32 3 | 3 | 30 | 33 (36 rows) SELECT * FROM (VALUES (1),(2),(3)) v1(r1), LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2) LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1; r1 | r1 | r2 | i ----+----+----+--- 1 | 1 | 10 | 1 1 | 1 | 10 | 2 1 | 1 | 10 | 3 1 | 1 | 10 | 4 1 | 1 | 20 | 1 1 | 1 | 20 | 2 1 | 1 | 20 | 3 1 | 1 | 20 | 4 1 | 1 | 20 | 5 1 | 1 | 20 | 6 1 | 1 | 30 | 1 1 | 1 | 30 | 2 1 | 1 | 30 | 3 1 | 1 | 30 | 4 1 | 1 | 30 | 5 1 | 1 | 30 | 6 1 | 1 | 30 | 7 1 | 1 | 30 | 8 2 | 2 | 10 | 2 2 | 2 | 10 | 3 2 | 2 | 10 | 4 2 | 2 | 20 | 2 2 | 2 | 20 | 3 2 | 2 | 20 | 4 2 | 2 | 20 | 5 2 | 2 | 20 | 6 2 | 2 | 30 | 2 2 | 2 | 30 | 3 2 | 2 | 30 | 4 2 | 2 | 30 | 5 2 | 2 | 30 | 6 2 | 2 | 30 | 7 2 | 2 | 30 | 8 3 | 3 | 10 | 3 3 | 3 | 10 | 4 3 | 3 | 20 | 3 3 | 3 | 20 | 4 3 | 3 | 20 | 5 3 | 3 | 20 | 6 3 | 3 | 30 | 3 3 | 3 | 30 | 4 3 | 3 | 30 | 5 3 | 3 | 30 | 6 3 | 3 | 30 | 7 3 | 3 | 30 | 8 (45 rows) -- check handling of FULL JOIN with multiple lateral references (bug #15741) SELECT * FROM (VALUES (1),(2)) v1(r1) LEFT JOIN LATERAL ( SELECT * FROM generate_series(1, v1.r1) AS gs1 LEFT JOIN LATERAL ( SELECT * FROM generate_series(1, gs1) AS gs2 LEFT JOIN generate_series(1, gs2) AS gs3 ON TRUE ) AS ss1 ON TRUE FULL JOIN generate_series(1, v1.r1) AS gs4 ON FALSE ) AS ss0 ON TRUE; r1 | gs1 | gs2 | gs3 | gs4 ----+-----+-----+-----+----- 1 | | | | 1 1 | 1 | 1 | 1 | 2 | | | | 1 2 | | | | 2 2 | 1 | 1 | 1 | 2 | 2 | 1 | 1 | 2 | 2 | 2 | 1 | 2 | 2 | 2 | 2 | (8 rows) DROP FUNCTION rngfunc_sql(int,int); DROP FUNCTION rngfunc_mat(int,int); DROP SEQUENCE rngfunc_rescan_seq1; DROP SEQUENCE rngfunc_rescan_seq2; -- -- Test cases involving OUT parameters -- CREATE FUNCTION rngfunc(in f1 int, out f2 int) AS 'select $1+1' LANGUAGE sql; SELECT rngfunc(42); rngfunc --------- 43 (1 row) SELECT * FROM rngfunc(42); f2 ---- 43 (1 row) SELECT * FROM rngfunc(42) AS p(x); x ---- 43 (1 row) -- explicit spec of return type is OK CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS int AS 'select $1+1' LANGUAGE sql; -- error, wrong result type CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS float AS 'select $1+1' LANGUAGE sql; ERROR: function result type must be integer because of OUT parameters -- with multiple OUT params you must get a RECORD result CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text) RETURNS int AS 'select $1+1' LANGUAGE sql; ERROR: function result type must be record because of OUT parameters CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text) RETURNS record AS 'select $1+1' LANGUAGE sql; ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION rngfunc(integer) first. CREATE OR REPLACE FUNCTION rngfuncr(in f1 int, out f2 int, out text) AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql; SELECT f1, rngfuncr(f1) FROM int4_tbl; f1 | rngfuncr -------------+---------------------------- 0 | (-1,0z) 123456 | (123455,123456z) -123456 | (-123457,-123456z) 2147483647 | (2147483646,2147483647z) -2147483647 | (-2147483648,-2147483647z) (5 rows) SELECT * FROM rngfuncr(42); f2 | column2 ----+--------- 41 | 42z (1 row) SELECT * FROM rngfuncr(42) AS p(a,b); a | b ----+----- 41 | 42z (1 row) CREATE OR REPLACE FUNCTION rngfuncb(in f1 int, inout f2 int, out text) AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql; SELECT f1, rngfuncb(f1, f1/2) FROM int4_tbl; f1 | rngfuncb -------------+---------------------------- 0 | (-1,0z) 123456 | (61727,123456z) -123456 | (-61729,-123456z) 2147483647 | (1073741822,2147483647z) -2147483647 | (-1073741824,-2147483647z) (5 rows) SELECT * FROM rngfuncb(42, 99); f2 | column2 ----+--------- 98 | 42z (1 row) SELECT * FROM rngfuncb(42, 99) AS p(a,b); a | b ----+----- 98 | 42z (1 row) -- Can reference function with or without OUT params for DROP, etc DROP FUNCTION rngfunc(int); DROP FUNCTION rngfuncr(in f2 int, out f1 int, out text); DROP FUNCTION rngfuncb(in f1 int, inout f2 int); -- -- For my next trick, polymorphic OUT parameters -- CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray) AS 'select $1, array[$1,$1]' LANGUAGE sql; SELECT dup(22); dup ---------------- (22,"{22,22}") (1 row) SELECT dup('xyz'); -- fails ERROR: could not determine polymorphic type because input has type unknown SELECT dup('xyz'::text); dup ------------------- (xyz,"{xyz,xyz}") (1 row) SELECT * FROM dup('xyz'::text); f2 | f3 -----+----------- xyz | {xyz,xyz} (1 row) -- fails, as we are attempting to rename first argument CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray) AS 'select $1, array[$1,$1]' LANGUAGE sql; ERROR: cannot change name of input parameter "f1" HINT: Use DROP FUNCTION dup(anyelement) first. DROP FUNCTION dup(anyelement); -- equivalent behavior, though different name exposed for input arg CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray) AS 'select $1, array[$1,$1]' LANGUAGE sql; SELECT dup(22); dup ---------------- (22,"{22,22}") (1 row) DROP FUNCTION dup(anyelement); -- fails, no way to deduce outputs CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray) AS 'select $1, array[$1,$1]' LANGUAGE sql; ERROR: cannot determine result data type DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, anyrange, or anymultirange. CREATE FUNCTION dup (f1 anycompatible, f2 anycompatiblearray, f3 out anycompatible, f4 out anycompatiblearray) AS 'select $1, $2' LANGUAGE sql; SELECT dup(22, array[44]); dup ----------- (22,{44}) (1 row) SELECT dup(4.5, array[44]); dup ------------ (4.5,{44}) (1 row) SELECT dup(22, array[44::bigint]); dup ----------- (22,{44}) (1 row) SELECT *, pg_typeof(f3), pg_typeof(f4) FROM dup(22, array[44::bigint]); f3 | f4 | pg_typeof | pg_typeof ----+------+-----------+----------- 22 | {44} | bigint | bigint[] (1 row) DROP FUNCTION dup(f1 anycompatible, f2 anycompatiblearray); CREATE FUNCTION dup (f1 anycompatiblerange, f2 out anycompatible, f3 out anycompatiblearray, f4 out anycompatiblerange) AS 'select lower($1), array[lower($1), upper($1)], $1' LANGUAGE sql; SELECT dup(int4range(4,7)); dup --------------------- (4,"{4,7}","[4,7)") (1 row) SELECT dup(numrange(4,7)); dup --------------------- (4,"{4,7}","[4,7)") (1 row) SELECT dup(textrange('aaa', 'bbb')); dup ------------------------------- (aaa,"{aaa,bbb}","[aaa,bbb)") (1 row) DROP FUNCTION dup(f1 anycompatiblerange); -- fails, no way to deduce outputs CREATE FUNCTION bad (f1 anyarray, out f2 anycompatible, out f3 anycompatiblearray) AS 'select $1, array[$1,$1]' LANGUAGE sql; ERROR: cannot determine result data type DETAIL: A result of type anycompatible requires at least one input of type anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange, or anycompatiblemultirange. -- -- table functions -- CREATE OR REPLACE FUNCTION rngfunc() RETURNS TABLE(a int) AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql; SELECT * FROM rngfunc(); a --- 1 2 3 4 5 (5 rows) DROP FUNCTION rngfunc(); CREATE OR REPLACE FUNCTION rngfunc(int) RETURNS TABLE(a int, b int) AS $$ SELECT a, b FROM generate_series(1,$1) a(a), generate_series(1,$1) b(b) $$ LANGUAGE sql; SELECT * FROM rngfunc(3); a | b ---+--- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 3 | 1 3 | 2 3 | 3 (9 rows) DROP FUNCTION rngfunc(int); -- case that causes change of typmod knowledge during inlining CREATE OR REPLACE FUNCTION rngfunc() RETURNS TABLE(a varchar(5)) AS $$ SELECT 'hello'::varchar(5) $$ LANGUAGE sql STABLE; SELECT * FROM rngfunc() GROUP BY 1; a ------- hello (1 row) DROP FUNCTION rngfunc(); -- -- some tests on SQL functions with RETURNING -- create temp table tt(f1 serial, data text); create function insert_tt(text) returns int as $$ insert into tt(data) values($1) returning f1 $$ language sql; select insert_tt('foo'); insert_tt ----------- 1 (1 row) select insert_tt('bar'); insert_tt ----------- 2 (1 row) select * from tt; f1 | data ----+------ 1 | foo 2 | bar (2 rows) -- insert will execute to completion even if function needs just 1 row create or replace function insert_tt(text) returns int as $$ insert into tt(data) values($1),($1||$1) returning f1 $$ language sql; select insert_tt('fool'); insert_tt ----------- 3 (1 row) select * from tt; f1 | data ----+---------- 1 | foo 2 | bar 3 | fool 4 | foolfool (4 rows) -- setof does what's expected create or replace function insert_tt2(text,text) returns setof int as $$ insert into tt(data) values($1),($2) returning f1 $$ language sql; select insert_tt2('foolish','barrish'); insert_tt2 ------------ 5 6 (2 rows) select * from insert_tt2('baz','quux'); insert_tt2 ------------ 7 8 (2 rows) select * from tt; f1 | data ----+---------- 1 | foo 2 | bar 3 | fool 4 | foolfool 5 | foolish 6 | barrish 7 | baz 8 | quux (8 rows) -- limit doesn't prevent execution to completion select insert_tt2('foolish','barrish') limit 1; insert_tt2 ------------ 9 (1 row) select * from tt; f1 | data ----+---------- 1 | foo 2 | bar 3 | fool 4 | foolfool 5 | foolish 6 | barrish 7 | baz 8 | quux 9 | foolish 10 | barrish (10 rows) -- triggers will fire, too create function noticetrigger() returns trigger as $$ begin raise notice 'noticetrigger % %', new.f1, new.data; return null; end $$ language plpgsql; create trigger tnoticetrigger after insert on tt for each row execute procedure noticetrigger(); select insert_tt2('foolme','barme') limit 1; NOTICE: noticetrigger 11 foolme NOTICE: noticetrigger 12 barme insert_tt2 ------------ 11 (1 row) select * from tt; f1 | data ----+---------- 1 | foo 2 | bar 3 | fool 4 | foolfool 5 | foolish 6 | barrish 7 | baz 8 | quux 9 | foolish 10 | barrish 11 | foolme 12 | barme (12 rows) -- and rules work create temp table tt_log(f1 int, data text); create rule insert_tt_rule as on insert to tt do also insert into tt_log values(new.*); select insert_tt2('foollog','barlog') limit 1; NOTICE: noticetrigger 13 foollog NOTICE: noticetrigger 14 barlog insert_tt2 ------------ 13 (1 row) select * from tt; f1 | data ----+---------- 1 | foo 2 | bar 3 | fool 4 | foolfool 5 | foolish 6 | barrish 7 | baz 8 | quux 9 | foolish 10 | barrish 11 | foolme 12 | barme 13 | foollog 14 | barlog (14 rows) -- note that nextval() gets executed a second time in the rule expansion, -- which is expected. select * from tt_log; f1 | data ----+--------- 15 | foollog 16 | barlog (2 rows) -- test case for a whole-row-variable bug create function rngfunc1(n integer, out a text, out b text) returns setof record language sql as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$; set work_mem='64kB'; select t.a, t, t.a from rngfunc1(10000) t limit 1; a | t | a -------+-------------------+------- foo 1 | ("foo 1","bar 1") | foo 1 (1 row) reset work_mem; select t.a, t, t.a from rngfunc1(10000) t limit 1; a | t | a -------+-------------------+------- foo 1 | ("foo 1","bar 1") | foo 1 (1 row) drop function rngfunc1(n integer); -- test use of SQL functions returning record -- this is supported in some cases where the query doesn't specify -- the actual record type ... create function array_to_set(anyarray) returns setof record as $$ select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i $$ language sql strict immutable; select array_to_set(array['one', 'two']); array_to_set -------------- (1,one) (2,two) (2 rows) select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text); f1 | f2 ----+----- 1 | one 2 | two (2 rows) select * from array_to_set(array['one', 'two']); -- fail ERROR: a column definition list is required for functions returning "record" LINE 1: select * from array_to_set(array['one', 'two']); ^ -- after-the-fact coercion of the columns is now possible, too select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); f1 | f2 ------+----- 1.00 | one 2.00 | two (2 rows) -- and if it doesn't work, you get a compile-time not run-time error select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text); ERROR: return type mismatch in function declared to return record DETAIL: Final statement returns integer instead of point at column 1. CONTEXT: SQL function "array_to_set" during startup -- with "strict", this function can't be inlined in FROM explain (verbose, costs off) select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); QUERY PLAN ---------------------------------------------------- Function Scan on public.array_to_set t Output: f1, f2 Function Call: array_to_set('{one,two}'::text[]) (3 rows) -- but without, it can be: create or replace function array_to_set(anyarray) returns setof record as $$ select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i $$ language sql immutable; select array_to_set(array['one', 'two']); array_to_set -------------- (1,one) (2,two) (2 rows) select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text); f1 | f2 ----+----- 1 | one 2 | two (2 rows) select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); f1 | f2 ------+----- 1.00 | one 2.00 | two (2 rows) select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text); ERROR: return type mismatch in function declared to return record DETAIL: Final statement returns integer instead of point at column 1. CONTEXT: SQL function "array_to_set" during inlining explain (verbose, costs off) select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); QUERY PLAN -------------------------------------------------------------- Function Scan on pg_catalog.generate_subscripts i Output: i.i, ('{one,two}'::text[])[i.i] Function Call: generate_subscripts('{one,two}'::text[], 1) (3 rows) create temp table rngfunc(f1 int8, f2 int8); create function testrngfunc() returns record as $$ insert into rngfunc values (1,2) returning *; $$ language sql; select testrngfunc(); testrngfunc ------------- (1,2) (1 row) select * from testrngfunc() as t(f1 int8,f2 int8); f1 | f2 ----+---- 1 | 2 (1 row) select * from testrngfunc(); -- fail ERROR: a column definition list is required for functions returning "record" LINE 1: select * from testrngfunc(); ^ drop function testrngfunc(); create function testrngfunc() returns setof record as $$ insert into rngfunc values (1,2), (3,4) returning *; $$ language sql; select testrngfunc(); testrngfunc ------------- (1,2) (3,4) (2 rows) select * from testrngfunc() as t(f1 int8,f2 int8); f1 | f2 ----+---- 1 | 2 3 | 4 (2 rows) select * from testrngfunc(); -- fail ERROR: a column definition list is required for functions returning "record" LINE 1: select * from testrngfunc(); ^ drop function testrngfunc(); -- Check that typmod imposed by a composite type is honored create type rngfunc_type as (f1 numeric(35,6), f2 numeric(35,2)); create function testrngfunc() returns rngfunc_type as $$ select 7.136178319899999964, 7.136178319899999964; $$ language sql immutable; explain (verbose, costs off) select testrngfunc(); QUERY PLAN ------------------------------------------- Result Output: '(7.136178,7.14)'::rngfunc_type (2 rows) select testrngfunc(); testrngfunc ----------------- (7.136178,7.14) (1 row) explain (verbose, costs off) select * from testrngfunc(); QUERY PLAN -------------------------------------------------- Function Scan on testrngfunc Output: f1, f2 Function Call: '(7.136178,7.14)'::rngfunc_type (3 rows) select * from testrngfunc(); f1 | f2 ----------+------ 7.136178 | 7.14 (1 row) create or replace function testrngfunc() returns rngfunc_type as $$ select 7.136178319899999964, 7.136178319899999964; $$ language sql volatile; explain (verbose, costs off) select testrngfunc(); QUERY PLAN ------------------------- Result Output: testrngfunc() (2 rows) select testrngfunc(); testrngfunc ----------------- (7.136178,7.14) (1 row) explain (verbose, costs off) select * from testrngfunc(); QUERY PLAN ------------------------------------- Function Scan on public.testrngfunc Output: f1, f2 Function Call: testrngfunc() (3 rows) select * from testrngfunc(); f1 | f2 ----------+------ 7.136178 | 7.14 (1 row) drop function testrngfunc(); create function testrngfunc() returns setof rngfunc_type as $$ select 7.136178319899999964, 7.136178319899999964; $$ language sql immutable; explain (verbose, costs off) select testrngfunc(); QUERY PLAN ------------------------- ProjectSet Output: testrngfunc() -> Result (3 rows) select testrngfunc(); testrngfunc ----------------- (7.136178,7.14) (1 row) explain (verbose, costs off) select * from testrngfunc(); QUERY PLAN -------------------------------------------------------- Result Output: 7.136178::numeric(35,6), 7.14::numeric(35,2) (2 rows) select * from testrngfunc(); f1 | f2 ----------+------ 7.136178 | 7.14 (1 row) create or replace function testrngfunc() returns setof rngfunc_type as $$ select 7.136178319899999964, 7.136178319899999964; $$ language sql volatile; explain (verbose, costs off) select testrngfunc(); QUERY PLAN ------------------------- ProjectSet Output: testrngfunc() -> Result (3 rows) select testrngfunc(); testrngfunc ----------------- (7.136178,7.14) (1 row) explain (verbose, costs off) select * from testrngfunc(); QUERY PLAN ------------------------------------- Function Scan on public.testrngfunc Output: f1, f2 Function Call: testrngfunc() (3 rows) select * from testrngfunc(); f1 | f2 ----------+------ 7.136178 | 7.14 (1 row) create or replace function testrngfunc() returns setof rngfunc_type as $$ select 1, 2 union select 3, 4 order by 1; $$ language sql immutable; explain (verbose, costs off) select testrngfunc(); QUERY PLAN ------------------------- ProjectSet Output: testrngfunc() -> Result (3 rows) select testrngfunc(); testrngfunc ----------------- (1.000000,2.00) (3.000000,4.00) (2 rows) explain (verbose, costs off) select * from testrngfunc(); QUERY PLAN ---------------------------------------------------------- Subquery Scan on "*SELECT*" Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1" -> Unique Output: (1), (2) -> Sort Output: (1), (2) Sort Key: (1), (2) -> Append -> Result Output: 1, 2 -> Result Output: 3, 4 (12 rows) select * from testrngfunc(); f1 | f2 ----------+------ 1.000000 | 2.00 3.000000 | 4.00 (2 rows) -- Check a couple of error cases while we're here select * from testrngfunc() as t(f1 int8,f2 int8); -- fail, composite result ERROR: a column definition list is redundant for a function returning a named composite type LINE 1: select * from testrngfunc() as t(f1 int8,f2 int8); ^ select * from pg_get_keywords() as t(f1 int8,f2 int8); -- fail, OUT params ERROR: a column definition list is redundant for a function with OUT parameters LINE 1: select * from pg_get_keywords() as t(f1 int8,f2 int8); ^ select * from sin(3) as t(f1 int8,f2 int8); -- fail, scalar result type ERROR: a column definition list is only allowed for functions returning "record" LINE 1: select * from sin(3) as t(f1 int8,f2 int8); ^ drop type rngfunc_type cascade; NOTICE: drop cascades to function testrngfunc() -- -- Check some cases involving added/dropped columns in a rowtype result -- create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool); insert into users values ('id',1,'email',true,11,true); insert into users values ('id2',2,'email2',true,12,true); alter table users drop column todrop; create or replace function get_first_user() returns users as $$ SELECT * FROM users ORDER BY userid LIMIT 1; $$ language sql stable; SELECT get_first_user(); get_first_user ------------------- (id,1,email,11,t) (1 row) SELECT * FROM get_first_user(); userid | seq | email | moredrop | enabled --------+-----+-------+----------+--------- id | 1 | email | 11 | t (1 row) create or replace function get_users() returns setof users as $$ SELECT * FROM users ORDER BY userid; $$ language sql stable; SELECT get_users(); get_users --------------------- (id,1,email,11,t) (id2,2,email2,12,t) (2 rows) SELECT * FROM get_users(); userid | seq | email | moredrop | enabled --------+-----+--------+----------+--------- id | 1 | email | 11 | t id2 | 2 | email2 | 12 | t (2 rows) SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes userid | seq | email | moredrop | enabled | ordinality --------+-----+--------+----------+---------+------------ id | 1 | email | 11 | t | 1 id2 | 2 | email2 | 12 | t | 2 (2 rows) -- multiple functions vs. dropped columns SELECT * FROM ROWS FROM(generate_series(10,11), get_users()) WITH ORDINALITY; generate_series | userid | seq | email | moredrop | enabled | ordinality -----------------+--------+-----+--------+----------+---------+------------ 10 | id | 1 | email | 11 | t | 1 11 | id2 | 2 | email2 | 12 | t | 2 (2 rows) SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY; userid | seq | email | moredrop | enabled | generate_series | ordinality --------+-----+--------+----------+---------+-----------------+------------ id | 1 | email | 11 | t | 10 | 1 id2 | 2 | email2 | 12 | t | 11 | 2 (2 rows) -- check that we can cope with post-parsing changes in rowtypes create temp view usersview as SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY; select * from usersview; userid | seq | email | moredrop | enabled | generate_series | ordinality --------+-----+--------+----------+---------+-----------------+------------ id | 1 | email | 11 | t | 10 | 1 id2 | 2 | email2 | 12 | t | 11 | 2 (2 rows) alter table users add column junk text; select * from usersview; userid | seq | email | moredrop | enabled | generate_series | ordinality --------+-----+--------+----------+---------+-----------------+------------ id | 1 | email | 11 | t | 10 | 1 id2 | 2 | email2 | 12 | t | 11 | 2 (2 rows) alter table users drop column moredrop; -- fail, view has reference ERROR: cannot drop column moredrop of table users because other objects depend on it DETAIL: view usersview depends on column moredrop of table users HINT: Use DROP ... CASCADE to drop the dependent objects too. -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. begin; -- destroy the dependency entry that prevents the DROP: delete from pg_depend where objid = (select oid from pg_rewrite where ev_class = 'usersview'::regclass and rulename = '_RETURN') and refobjsubid = 5 returning pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype; obj | ref | deptype --------------------------------+--------------------------------+--------- rule _RETURN on view usersview | column moredrop of table users | n (1 row) alter table users drop column moredrop; select * from usersview; -- expect clean failure ERROR: attribute 5 of type record has been dropped rollback; alter table users alter column seq type numeric; -- fail, view has reference ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view usersview depends on column "seq" -- likewise, check we don't crash if the dependency goes wrong begin; -- destroy the dependency entry that prevents the ALTER: delete from pg_depend where objid = (select oid from pg_rewrite where ev_class = 'usersview'::regclass and rulename = '_RETURN') and refobjsubid = 2 returning pg_describe_object(classid, objid, objsubid) as obj, pg_describe_object(refclassid, refobjid, refobjsubid) as ref, deptype; obj | ref | deptype --------------------------------+---------------------------+--------- rule _RETURN on view usersview | column seq of table users | n (1 row) alter table users alter column seq type numeric; select * from usersview; -- expect clean failure ERROR: attribute 2 of type record has wrong type DETAIL: Table has type numeric, but query expects integer. rollback; drop view usersview; drop function get_first_user(); drop function get_users(); drop table users; -- check behavior with type coercion required for a set-op create or replace function rngfuncbar() returns setof text as $$ select 'foo'::varchar union all select 'bar'::varchar ; $$ language sql stable; select rngfuncbar(); rngfuncbar ------------ foo bar (2 rows) select * from rngfuncbar(); rngfuncbar ------------ foo bar (2 rows) -- this function is now inlinable, too: explain (verbose, costs off) select * from rngfuncbar(); QUERY PLAN ------------------------------------------------ Result Output: ('foo'::character varying) -> Append -> Result Output: 'foo'::character varying -> Result Output: 'bar'::character varying (7 rows) drop function rngfuncbar(); -- check handling of a SQL function with multiple OUT params (bug #5777) create or replace function rngfuncbar(out integer, out numeric) as $$ select (1, 2.1) $$ language sql; select * from rngfuncbar(); column1 | column2 ---------+--------- 1 | 2.1 (1 row) create or replace function rngfuncbar(out integer, out numeric) as $$ select (1, 2) $$ language sql; select * from rngfuncbar(); -- fail ERROR: function return row and query-specified return row do not match DETAIL: Returned type integer at ordinal position 2, but query expects numeric. create or replace function rngfuncbar(out integer, out numeric) as $$ select (1, 2.1, 3) $$ language sql; select * from rngfuncbar(); -- fail ERROR: function return row and query-specified return row do not match DETAIL: Returned row contains 3 attributes, but query expects 2. drop function rngfuncbar(); -- check whole-row-Var handling in nested lateral functions (bug #11703) create function extractq2(t int8_tbl) returns int8 as $$ select t.q2 $$ language sql immutable; explain (verbose, costs off) select x from int8_tbl, extractq2(int8_tbl) f(x); QUERY PLAN ------------------------------------------ Nested Loop Output: f.x -> Seq Scan on public.int8_tbl Output: int8_tbl.q1, int8_tbl.q2 -> Function Scan on f Output: f.x Function Call: int8_tbl.q2 (7 rows) select x from int8_tbl, extractq2(int8_tbl) f(x); x ------------------- 456 4567890123456789 123 4567890123456789 -4567890123456789 (5 rows) create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$ select extractq2(t) offset 0 $$ language sql immutable; explain (verbose, costs off) select x from int8_tbl, extractq2_2(int8_tbl) f(x); QUERY PLAN ----------------------------------- Nested Loop Output: ((int8_tbl.*).q2) -> Seq Scan on public.int8_tbl Output: int8_tbl.* -> Result Output: (int8_tbl.*).q2 (6 rows) select x from int8_tbl, extractq2_2(int8_tbl) f(x); x ------------------- 456 4567890123456789 123 4567890123456789 -4567890123456789 (5 rows) -- without the "offset 0", this function gets optimized quite differently create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$ select extractq2(t) $$ language sql immutable; explain (verbose, costs off) select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); QUERY PLAN ----------------------------- Seq Scan on public.int8_tbl Output: int8_tbl.q2 (2 rows) select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); x ------------------- 456 4567890123456789 123 4567890123456789 -4567890123456789 (5 rows) -- check handling of nulls in SRF results (bug #7808) create type rngfunc2 as (a integer, b text); select *, row_to_json(u) from unnest(array[(1,'foo')::rngfunc2, null::rngfunc2]) u; a | b | row_to_json ---+-----+--------------------- 1 | foo | {"a":1,"b":"foo"} | | {"a":null,"b":null} (2 rows) select *, row_to_json(u) from unnest(array[null::rngfunc2, null::rngfunc2]) u; a | b | row_to_json ---+---+--------------------- | | {"a":null,"b":null} | | {"a":null,"b":null} (2 rows) select *, row_to_json(u) from unnest(array[null::rngfunc2, (1,'foo')::rngfunc2, null::rngfunc2]) u; a | b | row_to_json ---+-----+--------------------- | | {"a":null,"b":null} 1 | foo | {"a":1,"b":"foo"} | | {"a":null,"b":null} (3 rows) select *, row_to_json(u) from unnest(array[]::rngfunc2[]) u; a | b | row_to_json ---+---+------------- (0 rows) drop type rngfunc2; -- check handling of functions pulled up into function RTEs (bug #17227) explain (verbose, costs off) select * from (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) as unnested_modules(module)) as ss, jsonb_to_recordset(ss.lecture) as j (id text); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop Output: jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false), j.id -> Function Scan on pg_catalog.unnest unnested_modules Output: unnested_modules.module Function Call: unnest('{"{\"lectures\": [{\"id\": \"1\"}]}"}'::jsonb[]) -> Function Scan on pg_catalog.jsonb_to_recordset j Output: j.id Function Call: jsonb_to_recordset(jsonb_path_query_array((unnested_modules.module -> 'lectures'::text), '$[*]'::jsonpath, '{}'::jsonb, false)) (8 rows) select * from (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) as unnested_modules(module)) as ss, jsonb_to_recordset(ss.lecture) as j (id text); lecture | id ---------------+---- [{"id": "1"}] | 1 (1 row)