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