1CREATE TABLE rngfunc2(rngfuncid int, f2 int); 2INSERT INTO rngfunc2 VALUES(1, 11); 3INSERT INTO rngfunc2 VALUES(2, 22); 4INSERT INTO rngfunc2 VALUES(1, 111); 5 6CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL; 7 8-- function with ORDINALITY 9select * from rngfunct(1) with ordinality as z(a,b,ord); 10select * from rngfunct(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 rngfunct(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 rngfunct(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(rngfunct(1),rngfunct(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(rngfunct(1),rngfunct(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 rf_cur 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 rf_cur; 53fetch backward all from rf_cur; 54fetch all from rf_cur; 55fetch next from rf_cur; 56fetch next from rf_cur; 57fetch prior from rf_cur; 58fetch absolute 1 from rf_cur; 59fetch next from rf_cur; 60fetch next from rf_cur; 61fetch next from rf_cur; 62fetch prior from rf_cur; 63fetch prior from rf_cur; 64fetch prior from rf_cur; 65commit; 66 67-- function with implicit LATERAL 68select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2; 69 70-- function with implicit LATERAL and explicit ORDINALITY 71select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) with ordinality as z(rngfuncid,f2,ord) where rngfunc2.f2 = z.f2; 72 73-- function in subselect 74select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2; 75 76-- function in subselect 77select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2; 78 79-- function in subselect 80select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2; 81 82-- nested functions 83select rngfunct.rngfuncid, rngfunct.f2 from rngfunct(sin(pi()/2)::int) ORDER BY 1,2; 84 85CREATE TABLE rngfunc (rngfuncid int, rngfuncsubid int, rngfuncname text, primary key(rngfuncid,rngfuncsubid)); 86INSERT INTO rngfunc VALUES(1,1,'Joe'); 87INSERT INTO rngfunc VALUES(1,2,'Ed'); 88INSERT INTO rngfunc VALUES(2,1,'Mary'); 89 90-- sql, proretset = f, prorettype = b 91CREATE FUNCTION getrngfunc1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL; 92SELECT * FROM getrngfunc1(1) AS t1; 93SELECT * FROM getrngfunc1(1) WITH ORDINALITY AS t1(v,o); 94CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1); 95SELECT * FROM vw_getrngfunc; 96DROP VIEW vw_getrngfunc; 97CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1) WITH ORDINALITY as t1(v,o); 98SELECT * FROM vw_getrngfunc; 99DROP VIEW vw_getrngfunc; 100 101-- sql, proretset = t, prorettype = b 102CREATE FUNCTION getrngfunc2(int) RETURNS setof int AS 'SELECT rngfuncid FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 103SELECT * FROM getrngfunc2(1) AS t1; 104SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o); 105CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1); 106SELECT * FROM vw_getrngfunc; 107DROP VIEW vw_getrngfunc; 108CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o); 109SELECT * FROM vw_getrngfunc; 110DROP VIEW vw_getrngfunc; 111 112-- sql, proretset = t, prorettype = b 113CREATE FUNCTION getrngfunc3(int) RETURNS setof text AS 'SELECT rngfuncname FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 114SELECT * FROM getrngfunc3(1) AS t1; 115SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o); 116CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1); 117SELECT * FROM vw_getrngfunc; 118DROP VIEW vw_getrngfunc; 119CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o); 120SELECT * FROM vw_getrngfunc; 121DROP VIEW vw_getrngfunc; 122 123-- sql, proretset = f, prorettype = c 124CREATE FUNCTION getrngfunc4(int) RETURNS rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 125SELECT * FROM getrngfunc4(1) AS t1; 126SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o); 127CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1); 128SELECT * FROM vw_getrngfunc; 129DROP VIEW vw_getrngfunc; 130CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o); 131SELECT * FROM vw_getrngfunc; 132DROP VIEW vw_getrngfunc; 133 134-- sql, proretset = t, prorettype = c 135CREATE FUNCTION getrngfunc5(int) RETURNS setof rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 136SELECT * FROM getrngfunc5(1) AS t1; 137SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o); 138CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1); 139SELECT * FROM vw_getrngfunc; 140DROP VIEW vw_getrngfunc; 141CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o); 142SELECT * FROM vw_getrngfunc; 143DROP VIEW vw_getrngfunc; 144 145-- sql, proretset = f, prorettype = record 146CREATE FUNCTION getrngfunc6(int) RETURNS RECORD AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 147SELECT * FROM getrngfunc6(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text); 148SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; 149CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc6(1) AS 150(rngfuncid int, rngfuncsubid int, rngfuncname text); 151SELECT * FROM vw_getrngfunc; 152DROP VIEW vw_getrngfunc; 153CREATE VIEW vw_getrngfunc AS 154 SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) 155 WITH ORDINALITY; 156SELECT * FROM vw_getrngfunc; 157DROP VIEW vw_getrngfunc; 158 159-- sql, proretset = t, prorettype = record 160CREATE FUNCTION getrngfunc7(int) RETURNS setof record AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL; 161SELECT * FROM getrngfunc7(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text); 162SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY; 163CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc7(1) AS 164(rngfuncid int, rngfuncsubid int, rngfuncname text); 165SELECT * FROM vw_getrngfunc; 166DROP VIEW vw_getrngfunc; 167CREATE VIEW vw_getrngfunc AS 168 SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) 169 WITH ORDINALITY; 170SELECT * FROM vw_getrngfunc; 171DROP VIEW vw_getrngfunc; 172 173-- plpgsql, proretset = f, prorettype = b 174CREATE FUNCTION getrngfunc8(int) RETURNS int AS 'DECLARE rngfuncint int; BEGIN SELECT rngfuncid into rngfuncint FROM rngfunc WHERE rngfuncid = $1; RETURN rngfuncint; END;' LANGUAGE plpgsql; 175SELECT * FROM getrngfunc8(1) AS t1; 176SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o); 177CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1); 178SELECT * FROM vw_getrngfunc; 179DROP VIEW vw_getrngfunc; 180CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o); 181SELECT * FROM vw_getrngfunc; 182DROP VIEW vw_getrngfunc; 183 184-- plpgsql, proretset = f, prorettype = c 185CREATE FUNCTION getrngfunc9(int) RETURNS rngfunc AS 'DECLARE rngfunctup rngfunc%ROWTYPE; BEGIN SELECT * into rngfunctup FROM rngfunc WHERE rngfuncid = $1; RETURN rngfunctup; END;' LANGUAGE plpgsql; 186SELECT * FROM getrngfunc9(1) AS t1; 187SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o); 188CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1); 189SELECT * FROM vw_getrngfunc; 190DROP VIEW vw_getrngfunc; 191CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o); 192SELECT * FROM vw_getrngfunc; 193DROP VIEW vw_getrngfunc; 194 195-- mix 'n match kinds, to exercise expandRTE and related logic 196 197select * from rows from(getrngfunc1(1),getrngfunc2(1),getrngfunc3(1),getrngfunc4(1),getrngfunc5(1), 198 getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), 199 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), 200 getrngfunc8(1),getrngfunc9(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(getrngfunc9(1),getrngfunc8(1), 203 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), 204 getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), 205 getrngfunc5(1),getrngfunc4(1),getrngfunc3(1),getrngfunc2(1),getrngfunc1(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_rngfunc as 209 select * from rows from(getrngfunc9(1), 210 getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text), 211 getrngfunc1(1)) 212 with ordinality as t1(a,b,c,d,e,f,g,n); 213select * from vw_rngfunc; 214select pg_get_viewdef('vw_rngfunc'); 215drop view vw_rngfunc; 216 217DROP FUNCTION getrngfunc1(int); 218DROP FUNCTION getrngfunc2(int); 219DROP FUNCTION getrngfunc3(int); 220DROP FUNCTION getrngfunc4(int); 221DROP FUNCTION getrngfunc5(int); 222DROP FUNCTION getrngfunc6(int); 223DROP FUNCTION getrngfunc7(int); 224DROP FUNCTION getrngfunc8(int); 225DROP FUNCTION getrngfunc9(int); 226DROP FUNCTION rngfunct(int); 227DROP TABLE rngfunc2; 228DROP TABLE rngfunc; 229 230-- Rescan tests -- 231CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq1; 232CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq2; 233CREATE TYPE rngfunc_rescan_t AS (i integer, s bigint); 234 235CREATE 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; 236-- plpgsql functions use materialize mode 237CREATE 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; 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('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 244SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) ON (r+i)<100; 245SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 246SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100; 247 248SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 249SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) ON (r+i)<100; 250SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 251SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100; 252SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 253SELECT * 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; 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('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 264SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13); 265SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 266SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13) WITH ORDINALITY AS f(i,s,o); 267SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 268SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r); 269SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 270SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r) WITH ORDINALITY AS f(i,s,o); 271SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 272SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2); 273SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 274SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2) WITH ORDINALITY AS f(i,s,o); 275 276SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 277SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13); 278SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 279SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13) WITH ORDINALITY AS f(i,s,o); 280SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 281SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r); 282SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 283SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r) WITH ORDINALITY AS f(i,s,o); 284SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 285SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2); 286SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 287SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2) WITH ORDINALITY AS f(i,s,o); 288 289-- selective rescan of multiple functions: 290 291SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 292SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(11,11), rngfunc_mat(10+r,13) ); 293SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 294SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(11,11) ); 295SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 296SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(10+r,13) ); 297 298SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false); 299SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, ROWS FROM( rngfunc_sql(10+r1,13), rngfunc_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 rngfunc_sql(int,int); 338DROP FUNCTION rngfunc_mat(int,int); 339DROP SEQUENCE rngfunc_rescan_seq1; 340DROP SEQUENCE rngfunc_rescan_seq2; 341 342-- 343-- Test cases involving OUT parameters 344-- 345 346CREATE FUNCTION rngfunc(in f1 int, out f2 int) 347AS 'select $1+1' LANGUAGE sql; 348SELECT rngfunc(42); 349SELECT * FROM rngfunc(42); 350SELECT * FROM rngfunc(42) AS p(x); 351 352-- explicit spec of return type is OK 353CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS int 354AS 'select $1+1' LANGUAGE sql; 355-- error, wrong result type 356CREATE OR REPLACE FUNCTION rngfunc(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 rngfunc(in f1 int, out f2 int, out f3 text) RETURNS int 360AS 'select $1+1' LANGUAGE sql; 361CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text) 362RETURNS record 363AS 'select $1+1' LANGUAGE sql; 364 365CREATE OR REPLACE FUNCTION rngfuncr(in f1 int, out f2 int, out text) 366AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql; 367SELECT f1, rngfuncr(f1) FROM int4_tbl; 368SELECT * FROM rngfuncr(42); 369SELECT * FROM rngfuncr(42) AS p(a,b); 370 371CREATE OR REPLACE FUNCTION rngfuncb(in f1 int, inout f2 int, out text) 372AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql; 373SELECT f1, rngfuncb(f1, f1/2) FROM int4_tbl; 374SELECT * FROM rngfuncb(42, 99); 375SELECT * FROM rngfuncb(42, 99) AS p(a,b); 376 377-- Can reference function with or without OUT params for DROP, etc 378DROP FUNCTION rngfunc(int); 379DROP FUNCTION rngfuncr(in f2 int, out f1 int, out text); 380DROP FUNCTION rngfuncb(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 410CREATE FUNCTION dup (f1 anycompatible, f2 anycompatiblearray, f3 out anycompatible, f4 out anycompatiblearray) 411AS 'select $1, $2' LANGUAGE sql; 412SELECT dup(22, array[44]); 413SELECT dup(4.5, array[44]); 414SELECT dup(22, array[44::bigint]); 415SELECT *, pg_typeof(f3), pg_typeof(f4) FROM dup(22, array[44::bigint]); 416 417DROP FUNCTION dup(f1 anycompatible, f2 anycompatiblearray); 418 419CREATE FUNCTION dup (f1 anycompatiblerange, f2 out anycompatible, f3 out anycompatiblearray, f4 out anycompatiblerange) 420AS 'select lower($1), array[lower($1), upper($1)], $1' LANGUAGE sql; 421SELECT dup(int4range(4,7)); 422SELECT dup(numrange(4,7)); 423SELECT dup(textrange('aaa', 'bbb')); 424 425DROP FUNCTION dup(f1 anycompatiblerange); 426 427-- fails, no way to deduce outputs 428CREATE FUNCTION bad (f1 anyarray, out f2 anycompatible, out f3 anycompatiblearray) 429AS 'select $1, array[$1,$1]' LANGUAGE sql; 430 431-- 432-- table functions 433-- 434 435CREATE OR REPLACE FUNCTION rngfunc() 436RETURNS TABLE(a int) 437AS $$ SELECT a FROM generate_series(1,5) a(a) $$ LANGUAGE sql; 438SELECT * FROM rngfunc(); 439DROP FUNCTION rngfunc(); 440 441CREATE OR REPLACE FUNCTION rngfunc(int) 442RETURNS TABLE(a int, b int) 443AS $$ SELECT a, b 444 FROM generate_series(1,$1) a(a), 445 generate_series(1,$1) b(b) $$ LANGUAGE sql; 446SELECT * FROM rngfunc(3); 447DROP FUNCTION rngfunc(int); 448 449-- case that causes change of typmod knowledge during inlining 450CREATE OR REPLACE FUNCTION rngfunc() 451RETURNS TABLE(a varchar(5)) 452AS $$ SELECT 'hello'::varchar(5) $$ LANGUAGE sql STABLE; 453SELECT * FROM rngfunc() GROUP BY 1; 454DROP FUNCTION rngfunc(); 455 456-- 457-- some tests on SQL functions with RETURNING 458-- 459 460create temp table tt(f1 serial, data text); 461 462create function insert_tt(text) returns int as 463$$ insert into tt(data) values($1) returning f1 $$ 464language sql; 465 466select insert_tt('foo'); 467select insert_tt('bar'); 468select * from tt; 469 470-- insert will execute to completion even if function needs just 1 row 471create or replace function insert_tt(text) returns int as 472$$ insert into tt(data) values($1),($1||$1) returning f1 $$ 473language sql; 474 475select insert_tt('fool'); 476select * from tt; 477 478-- setof does what's expected 479create or replace function insert_tt2(text,text) returns setof int as 480$$ insert into tt(data) values($1),($2) returning f1 $$ 481language sql; 482 483select insert_tt2('foolish','barrish'); 484select * from insert_tt2('baz','quux'); 485select * from tt; 486 487-- limit doesn't prevent execution to completion 488select insert_tt2('foolish','barrish') limit 1; 489select * from tt; 490 491-- triggers will fire, too 492create function noticetrigger() returns trigger as $$ 493begin 494 raise notice 'noticetrigger % %', new.f1, new.data; 495 return null; 496end $$ language plpgsql; 497create trigger tnoticetrigger after insert on tt for each row 498execute procedure noticetrigger(); 499 500select insert_tt2('foolme','barme') limit 1; 501select * from tt; 502 503-- and rules work 504create temp table tt_log(f1 int, data text); 505 506create rule insert_tt_rule as on insert to tt do also 507 insert into tt_log values(new.*); 508 509select insert_tt2('foollog','barlog') limit 1; 510select * from tt; 511-- note that nextval() gets executed a second time in the rule expansion, 512-- which is expected. 513select * from tt_log; 514 515-- test case for a whole-row-variable bug 516create function rngfunc1(n integer, out a text, out b text) 517 returns setof record 518 language sql 519 as $$ select 'foo ' || i, 'bar ' || i from generate_series(1,$1) i $$; 520 521set work_mem='64kB'; 522select t.a, t, t.a from rngfunc1(10000) t limit 1; 523reset work_mem; 524select t.a, t, t.a from rngfunc1(10000) t limit 1; 525 526drop function rngfunc1(n integer); 527 528-- test use of SQL functions returning record 529-- this is supported in some cases where the query doesn't specify 530-- the actual record type ... 531 532create function array_to_set(anyarray) returns setof record as $$ 533 select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i 534$$ language sql strict immutable; 535 536select array_to_set(array['one', 'two']); 537select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text); 538select * from array_to_set(array['one', 'two']); -- fail 539-- after-the-fact coercion of the columns is now possible, too 540select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); 541-- and if it doesn't work, you get a compile-time not run-time error 542select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text); 543 544-- with "strict", this function can't be inlined in FROM 545explain (verbose, costs off) 546 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); 547 548-- but without, it can be: 549 550create or replace function array_to_set(anyarray) returns setof record as $$ 551 select i AS "index", $1[i] AS "value" from generate_subscripts($1, 1) i 552$$ language sql immutable; 553 554select array_to_set(array['one', 'two']); 555select * from array_to_set(array['one', 'two']) as t(f1 int,f2 text); 556select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); 557select * from array_to_set(array['one', 'two']) as t(f1 point,f2 text); 558explain (verbose, costs off) 559 select * from array_to_set(array['one', 'two']) as t(f1 numeric(4,2),f2 text); 560 561create temp table rngfunc(f1 int8, f2 int8); 562 563create function testrngfunc() returns record as $$ 564 insert into rngfunc values (1,2) returning *; 565$$ language sql; 566 567select testrngfunc(); 568select * from testrngfunc() as t(f1 int8,f2 int8); 569select * from testrngfunc(); -- fail 570 571drop function testrngfunc(); 572 573create function testrngfunc() returns setof record as $$ 574 insert into rngfunc values (1,2), (3,4) returning *; 575$$ language sql; 576 577select testrngfunc(); 578select * from testrngfunc() as t(f1 int8,f2 int8); 579select * from testrngfunc(); -- fail 580 581drop function testrngfunc(); 582 583-- Check that typmod imposed by a composite type is honored 584create type rngfunc_type as (f1 numeric(35,6), f2 numeric(35,2)); 585 586create function testrngfunc() returns rngfunc_type as $$ 587 select 7.136178319899999964, 7.136178319899999964; 588$$ language sql immutable; 589 590explain (verbose, costs off) 591select testrngfunc(); 592select testrngfunc(); 593explain (verbose, costs off) 594select * from testrngfunc(); 595select * from testrngfunc(); 596 597create or replace function testrngfunc() returns rngfunc_type as $$ 598 select 7.136178319899999964, 7.136178319899999964; 599$$ language sql volatile; 600 601explain (verbose, costs off) 602select testrngfunc(); 603select testrngfunc(); 604explain (verbose, costs off) 605select * from testrngfunc(); 606select * from testrngfunc(); 607 608drop function testrngfunc(); 609 610create function testrngfunc() returns setof rngfunc_type as $$ 611 select 7.136178319899999964, 7.136178319899999964; 612$$ language sql immutable; 613 614explain (verbose, costs off) 615select testrngfunc(); 616select testrngfunc(); 617explain (verbose, costs off) 618select * from testrngfunc(); 619select * from testrngfunc(); 620 621create or replace function testrngfunc() returns setof rngfunc_type as $$ 622 select 7.136178319899999964, 7.136178319899999964; 623$$ language sql volatile; 624 625explain (verbose, costs off) 626select testrngfunc(); 627select testrngfunc(); 628explain (verbose, costs off) 629select * from testrngfunc(); 630select * from testrngfunc(); 631 632create or replace function testrngfunc() returns setof rngfunc_type as $$ 633 select 1, 2 union select 3, 4 order by 1; 634$$ language sql immutable; 635 636explain (verbose, costs off) 637select testrngfunc(); 638select testrngfunc(); 639explain (verbose, costs off) 640select * from testrngfunc(); 641select * from testrngfunc(); 642 643drop type rngfunc_type cascade; 644 645-- 646-- Check some cases involving added/dropped columns in a rowtype result 647-- 648 649create temp table users (userid text, seq int, email text, todrop bool, moredrop int, enabled bool); 650insert into users values ('id',1,'email',true,11,true); 651insert into users values ('id2',2,'email2',true,12,true); 652alter table users drop column todrop; 653 654create or replace function get_first_user() returns users as 655$$ SELECT * FROM users ORDER BY userid LIMIT 1; $$ 656language sql stable; 657 658SELECT get_first_user(); 659SELECT * FROM get_first_user(); 660 661create or replace function get_users() returns setof users as 662$$ SELECT * FROM users ORDER BY userid; $$ 663language sql stable; 664 665SELECT get_users(); 666SELECT * FROM get_users(); 667SELECT * FROM get_users() WITH ORDINALITY; -- make sure ordinality copes 668 669-- multiple functions vs. dropped columns 670SELECT * FROM ROWS FROM(generate_series(10,11), get_users()) WITH ORDINALITY; 671SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY; 672 673-- check that we can cope with post-parsing changes in rowtypes 674create temp view usersview as 675SELECT * FROM ROWS FROM(get_users(), generate_series(10,11)) WITH ORDINALITY; 676 677select * from usersview; 678alter table users add column junk text; 679select * from usersview; 680begin; 681alter table users drop column moredrop; 682select * from usersview; -- expect clean failure 683rollback; 684alter table users alter column seq type numeric; 685select * from usersview; -- expect clean failure 686 687drop view usersview; 688drop function get_first_user(); 689drop function get_users(); 690drop table users; 691 692-- check behavior with type coercion required for a set-op 693 694create or replace function rngfuncbar() returns setof text as 695$$ select 'foo'::varchar union all select 'bar'::varchar ; $$ 696language sql stable; 697 698select rngfuncbar(); 699select * from rngfuncbar(); 700-- this function is now inlinable, too: 701explain (verbose, costs off) select * from rngfuncbar(); 702 703drop function rngfuncbar(); 704 705-- check handling of a SQL function with multiple OUT params (bug #5777) 706 707create or replace function rngfuncbar(out integer, out numeric) as 708$$ select (1, 2.1) $$ language sql; 709 710select * from rngfuncbar(); 711 712create or replace function rngfuncbar(out integer, out numeric) as 713$$ select (1, 2) $$ language sql; 714 715select * from rngfuncbar(); -- fail 716 717create or replace function rngfuncbar(out integer, out numeric) as 718$$ select (1, 2.1, 3) $$ language sql; 719 720select * from rngfuncbar(); -- fail 721 722drop function rngfuncbar(); 723 724-- check whole-row-Var handling in nested lateral functions (bug #11703) 725 726create function extractq2(t int8_tbl) returns int8 as $$ 727 select t.q2 728$$ language sql immutable; 729 730explain (verbose, costs off) 731select x from int8_tbl, extractq2(int8_tbl) f(x); 732 733select x from int8_tbl, extractq2(int8_tbl) f(x); 734 735create function extractq2_2(t int8_tbl) returns table(ret1 int8) as $$ 736 select extractq2(t) offset 0 737$$ language sql immutable; 738 739explain (verbose, costs off) 740select x from int8_tbl, extractq2_2(int8_tbl) f(x); 741 742select x from int8_tbl, extractq2_2(int8_tbl) f(x); 743 744-- without the "offset 0", this function gets optimized quite differently 745 746create function extractq2_2_opt(t int8_tbl) returns table(ret1 int8) as $$ 747 select extractq2(t) 748$$ language sql immutable; 749 750explain (verbose, costs off) 751select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); 752 753select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); 754 755-- check handling of nulls in SRF results (bug #7808) 756 757create type rngfunc2 as (a integer, b text); 758 759select *, row_to_json(u) from unnest(array[(1,'foo')::rngfunc2, null::rngfunc2]) u; 760select *, row_to_json(u) from unnest(array[null::rngfunc2, null::rngfunc2]) u; 761select *, row_to_json(u) from unnest(array[null::rngfunc2, (1,'foo')::rngfunc2, null::rngfunc2]) u; 762select *, row_to_json(u) from unnest(array[]::rngfunc2[]) u; 763 764drop type rngfunc2; 765 766-- check handling of functions pulled up into function RTEs (bug #17227) 767 768explain (verbose, costs off) 769select * from 770 (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture 771 from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) 772 as unnested_modules(module)) as ss, 773 jsonb_to_recordset(ss.lecture) as j (id text); 774 775select * from 776 (select jsonb_path_query_array(module->'lectures', '$[*]') as lecture 777 from unnest(array['{"lectures": [{"id": "1"}]}'::jsonb]) 778 as unnested_modules(module)) as ss, 779 jsonb_to_recordset(ss.lecture) as j (id text); 780