Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 2487 insertions, 0 deletions

diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
new file mode 100644
index 0000000..fbb840e
--- /dev/null
+++ b/src/test/regress/expected/rangefuncs.out
@@ -0,0 +1,2487 @@
+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 a,                                                                            +
+     b,                                                                                +
+     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 a,                                                                            +
+     b,                                                                                +
+     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 a,                                                                                                          +
+     b,                                                                                                              +
+     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 a,                                                                                                                                                                  +
+     b,                                                                                                                                                                      +
+     c,                                                                                                                                                                      +
+     d,                                                                                                                                                                      +
+     e,                                                                                                                                                                      +
+     f,                                                                                                                                                                      +
+     g,                                                                                                                                                                      +
+     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)
+