CREATE TABLE rngfunc2(rngfuncid int, f2 int);
INSERT INTO rngfunc2 VALUES(1, 11);
INSERT INTO rngfunc2 VALUES(2, 22);
INSERT INTO rngfunc2 VALUES(1, 111);
CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;
-- function with ORDINALITY
select * from rngfunct(1) with ordinality as z(a,b,ord);
 a |  b  | ord 
---+-----+-----
 1 |  11 |   1
 1 | 111 |   2
(2 rows)

select * from rngfunct(1) with ordinality as z(a,b,ord) where b > 100;   -- ordinal 2, not 1
 a |  b  | ord 
---+-----+-----
 1 | 111 |   2
(1 row)

-- ordinality vs. column names and types
select a,b,ord from rngfunct(1) with ordinality as z(a,b,ord);
 a |  b  | ord 
---+-----+-----
 1 |  11 |   1
 1 | 111 |   2
(2 rows)

select a,ord from unnest(array['a','b']) with ordinality as z(a,ord);
 a | ord 
---+-----
 a |   1
 b |   2
(2 rows)

select * from unnest(array['a','b']) with ordinality as z(a,ord);
 a | ord 
---+-----
 a |   1
 b |   2
(2 rows)

select a,ord from unnest(array[1.0::float8]) with ordinality as z(a,ord);
 a | ord 
---+-----
 1 |   1
(1 row)

select * from unnest(array[1.0::float8]) with ordinality as z(a,ord);
 a | ord 
---+-----
 1 |   1
(1 row)

select row_to_json(s.*) from generate_series(11,14) with ordinality s;
       row_to_json       
-------------------------
 {"s":11,"ordinality":1}
 {"s":12,"ordinality":2}
 {"s":13,"ordinality":3}
 {"s":14,"ordinality":4}
(4 rows)

-- ordinality vs. views
create temporary view vw_ord as select * from (values (1)) v(n) join rngfunct(1) with ordinality as z(a,b,ord) on (n=ord);
select * from vw_ord;
 n | a | b  | ord 
---+---+----+-----
 1 | 1 | 11 |   1
(1 row)

select definition from pg_views where viewname='vw_ord';
                               definition                                
-------------------------------------------------------------------------
  SELECT v.n,                                                           +
     z.a,                                                               +
     z.b,                                                               +
     z.ord                                                              +
    FROM (( VALUES (1)) v(n)                                            +
      JOIN rngfunct(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
(1 row)

drop view vw_ord;
-- multiple functions
select * from rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord);
 a |  b  | c | d  | ord 
---+-----+---+----+-----
 1 |  11 | 2 | 22 |   1
 1 | 111 |   |    |   2
(2 rows)

create temporary view vw_ord as select * from (values (1)) v(n) join rows from(rngfunct(1),rngfunct(2)) with ordinality as z(a,b,c,d,ord) on (n=ord);
select * from vw_ord;
 n | a | b  | c | d  | ord 
---+---+----+---+----+-----
 1 | 1 | 11 | 2 | 22 |   1
(1 row)

select definition from pg_views where viewname='vw_ord';
                                              definition                                               
-------------------------------------------------------------------------------------------------------
  SELECT v.n,                                                                                         +
     z.a,                                                                                             +
     z.b,                                                                                             +
     z.c,                                                                                             +
     z.d,                                                                                             +
     z.ord                                                                                            +
    FROM (( VALUES (1)) v(n)                                                                          +
      JOIN ROWS FROM(rngfunct(1), rngfunct(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
(1 row)

drop view vw_ord;
-- expansions of unnest()
select * from unnest(array[10,20],array['foo','bar'],array[1.0]);
 unnest | unnest | unnest 
--------+--------+--------
     10 | foo    |    1.0
     20 | bar    |       
(2 rows)

select * from unnest(array[10,20],array['foo','bar'],array[1.0]) with ordinality as z(a,b,c,ord);
 a  |  b  |  c  | ord 
----+-----+-----+-----
 10 | foo | 1.0 |   1
 20 | bar |     |   2
(2 rows)

select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) with ordinality as z(a,b,c,ord);
 a  |  b  |  c  | ord 
----+-----+-----+-----
 10 | foo | 1.0 |   1
 20 | bar |     |   2
(2 rows)

select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(101,102)) with ordinality as z(a,b,c,ord);
 a  |  b  |  c  | ord 
----+-----+-----+-----
 10 | foo | 101 |   1
 20 | bar | 102 |   2
(2 rows)

create temporary view vw_ord as select * from unnest(array[10,20],array['foo','bar'],array[1.0]) as z(a,b,c);
select * from vw_ord;
 a  |  b  |  c  
----+-----+-----
 10 | foo | 1.0
 20 | bar |    
(2 rows)

select definition from pg_views where viewname='vw_ord';
                                       definition                                       
----------------------------------------------------------------------------------------
  SELECT z.a,                                                                          +
     z.b,                                                                              +
     z.c                                                                               +
    FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
(1 row)

drop view vw_ord;
create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar'],array[1.0])) as z(a,b,c);
select * from vw_ord;
 a  |  b  |  c  
----+-----+-----
 10 | foo | 1.0
 20 | bar |    
(2 rows)

select definition from pg_views where viewname='vw_ord';
                                       definition                                       
----------------------------------------------------------------------------------------
  SELECT z.a,                                                                          +
     z.b,                                                                              +
     z.c                                                                               +
    FROM UNNEST(ARRAY[10, 20], ARRAY['foo'::text, 'bar'::text], ARRAY[1.0]) z(a, b, c);
(1 row)

drop view vw_ord;
create temporary view vw_ord as select * from rows from(unnest(array[10,20],array['foo','bar']), generate_series(1,2)) as z(a,b,c);
select * from vw_ord;
 a  |  b  | c 
----+-----+---
 10 | foo | 1
 20 | bar | 2
(2 rows)

select definition from pg_views where viewname='vw_ord';
                                                      definition                                                      
----------------------------------------------------------------------------------------------------------------------
  SELECT z.a,                                                                                                        +
     z.b,                                                                                                            +
     z.c                                                                                                             +
    FROM ROWS FROM(unnest(ARRAY[10, 20]), unnest(ARRAY['foo'::text, 'bar'::text]), generate_series(1, 2)) z(a, b, c);
(1 row)

drop view vw_ord;
-- ordinality and multiple functions vs. rewind and reverse scan
begin;
declare rf_cur scroll cursor for select * from rows from(generate_series(1,5),generate_series(1,2)) with ordinality as g(i,j,o);
fetch all from rf_cur;
 i | j | o 
---+---+---
 1 | 1 | 1
 2 | 2 | 2
 3 |   | 3
 4 |   | 4
 5 |   | 5
(5 rows)

fetch backward all from rf_cur;
 i | j | o 
---+---+---
 5 |   | 5
 4 |   | 4
 3 |   | 3
 2 | 2 | 2
 1 | 1 | 1
(5 rows)

fetch all from rf_cur;
 i | j | o 
---+---+---
 1 | 1 | 1
 2 | 2 | 2
 3 |   | 3
 4 |   | 4
 5 |   | 5
(5 rows)

fetch next from rf_cur;
 i | j | o 
---+---+---
(0 rows)

fetch next from rf_cur;
 i | j | o 
---+---+---
(0 rows)

fetch prior from rf_cur;
 i | j | o 
---+---+---
 5 |   | 5
(1 row)

fetch absolute 1 from rf_cur;
 i | j | o 
---+---+---
 1 | 1 | 1
(1 row)

fetch next from rf_cur;
 i | j | o 
---+---+---
 2 | 2 | 2
(1 row)

fetch next from rf_cur;
 i | j | o 
---+---+---
 3 |   | 3
(1 row)

fetch next from rf_cur;
 i | j | o 
---+---+---
 4 |   | 4
(1 row)

fetch prior from rf_cur;
 i | j | o 
---+---+---
 3 |   | 3
(1 row)

fetch prior from rf_cur;
 i | j | o 
---+---+---
 2 | 2 | 2
(1 row)

fetch prior from rf_cur;
 i | j | o 
---+---+---
 1 | 1 | 1
(1 row)

commit;
-- function with implicit LATERAL
select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2;
 rngfuncid | f2  | rngfuncid | f2  
-----------+-----+-----------+-----
         1 |  11 |         1 |  11
         2 |  22 |         2 |  22
         1 | 111 |         1 | 111
(3 rows)

-- function with implicit LATERAL and explicit ORDINALITY
select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) with ordinality as z(rngfuncid,f2,ord) where rngfunc2.f2 = z.f2;
 rngfuncid | f2  | rngfuncid | f2  | ord 
-----------+-----+-----------+-----+-----
         1 |  11 |         1 |  11 |   1
         2 |  22 |         2 |  22 |   1
         1 | 111 |         1 | 111 |   2
(3 rows)

-- function in subselect
select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;
 rngfuncid | f2  
-----------+-----
         1 |  11
         1 | 111
         2 |  22
(3 rows)

-- function in subselect
select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2;
 rngfuncid | f2  
-----------+-----
         1 |  11
         1 | 111
(2 rows)

-- function in subselect
select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2;
 rngfuncid | f2  
-----------+-----
         1 |  11
         1 | 111
(2 rows)

-- nested functions
select rngfunct.rngfuncid, rngfunct.f2 from rngfunct(sin(pi()/2)::int) ORDER BY 1,2;
 rngfuncid | f2  
-----------+-----
         1 |  11
         1 | 111
(2 rows)

CREATE TABLE rngfunc (rngfuncid int, rngfuncsubid int, rngfuncname text, primary key(rngfuncid,rngfuncsubid));
INSERT INTO rngfunc VALUES(1,1,'Joe');
INSERT INTO rngfunc VALUES(1,2,'Ed');
INSERT INTO rngfunc VALUES(2,1,'Mary');
-- sql, proretset = f, prorettype = b
CREATE FUNCTION getrngfunc1(int) RETURNS int AS 'SELECT $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc1(1) AS t1;
 t1 
----
  1
(1 row)

SELECT * FROM getrngfunc1(1) WITH ORDINALITY AS t1(v,o);
 v | o 
---+---
 1 | 1
(1 row)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1);
SELECT * FROM vw_getrngfunc;
 getrngfunc1 
-------------
           1
(1 row)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc1(1) WITH ORDINALITY as t1(v,o);
SELECT * FROM vw_getrngfunc;
 v | o 
---+---
 1 | 1
(1 row)

DROP VIEW vw_getrngfunc;
-- sql, proretset = t, prorettype = b
CREATE FUNCTION getrngfunc2(int) RETURNS setof int AS 'SELECT rngfuncid FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc2(1) AS t1;
 t1 
----
  1
  1
(2 rows)

SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o);
 v | o 
---+---
 1 | 1
 1 | 2
(2 rows)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1);
SELECT * FROM vw_getrngfunc;
 getrngfunc2 
-------------
           1
           1
(2 rows)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc2(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getrngfunc;
 v | o 
---+---
 1 | 1
 1 | 2
(2 rows)

DROP VIEW vw_getrngfunc;
-- sql, proretset = t, prorettype = b
CREATE FUNCTION getrngfunc3(int) RETURNS setof text AS 'SELECT rngfuncname FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc3(1) AS t1;
 t1  
-----
 Joe
 Ed
(2 rows)

SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o);
  v  | o 
-----+---
 Joe | 1
 Ed  | 2
(2 rows)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1);
SELECT * FROM vw_getrngfunc;
 getrngfunc3 
-------------
 Joe
 Ed
(2 rows)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc3(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getrngfunc;
  v  | o 
-----+---
 Joe | 1
 Ed  | 2
(2 rows)

DROP VIEW vw_getrngfunc;
-- sql, proretset = f, prorettype = c
CREATE FUNCTION getrngfunc4(int) RETURNS rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc4(1) AS t1;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o);
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
(1 row)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1);
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc4(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getrngfunc;
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
(1 row)

DROP VIEW vw_getrngfunc;
-- sql, proretset = t, prorettype = c
CREATE FUNCTION getrngfunc5(int) RETURNS setof rngfunc AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc5(1) AS t1;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
         1 |            2 | Ed
(2 rows)

SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o);
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
 1 | 2 | Ed  | 2
(2 rows)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1);
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
         1 |            2 | Ed
(2 rows)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc5(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getrngfunc;
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
 1 | 2 | Ed  | 2
(2 rows)

DROP VIEW vw_getrngfunc;
-- sql, proretset = f, prorettype = record
CREATE FUNCTION getrngfunc6(int) RETURNS RECORD AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc6(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text);
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY;
 rngfuncid | rngfuncsubid | rngfuncname | ordinality 
-----------+--------------+-------------+------------
         1 |            1 | Joe         |          1
(1 row)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc6(1) AS
(rngfuncid int, rngfuncsubid int, rngfuncname text);
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS
  SELECT * FROM ROWS FROM( getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) )
                WITH ORDINALITY;
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname | ordinality 
-----------+--------------+-------------+------------
         1 |            1 | Joe         |          1
(1 row)

DROP VIEW vw_getrngfunc;
-- sql, proretset = t, prorettype = record
CREATE FUNCTION getrngfunc7(int) RETURNS setof record AS 'SELECT * FROM rngfunc WHERE rngfuncid = $1;' LANGUAGE SQL;
SELECT * FROM getrngfunc7(1) AS t1(rngfuncid int, rngfuncsubid int, rngfuncname text);
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
         1 |            2 | Ed
(2 rows)

SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) ) WITH ORDINALITY;
 rngfuncid | rngfuncsubid | rngfuncname | ordinality 
-----------+--------------+-------------+------------
         1 |            1 | Joe         |          1
         1 |            2 | Ed          |          2
(2 rows)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc7(1) AS
(rngfuncid int, rngfuncsubid int, rngfuncname text);
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
         1 |            2 | Ed
(2 rows)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS
  SELECT * FROM ROWS FROM( getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text) )
                WITH ORDINALITY;
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname | ordinality 
-----------+--------------+-------------+------------
         1 |            1 | Joe         |          1
         1 |            2 | Ed          |          2
(2 rows)

DROP VIEW vw_getrngfunc;
-- plpgsql, proretset = f, prorettype = b
CREATE FUNCTION getrngfunc8(int) RETURNS int AS 'DECLARE rngfuncint int; BEGIN SELECT rngfuncid into rngfuncint FROM rngfunc WHERE rngfuncid = $1; RETURN rngfuncint; END;' LANGUAGE plpgsql;
SELECT * FROM getrngfunc8(1) AS t1;
 t1 
----
  1
(1 row)

SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o);
 v | o 
---+---
 1 | 1
(1 row)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1);
SELECT * FROM vw_getrngfunc;
 getrngfunc8 
-------------
           1
(1 row)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc8(1) WITH ORDINALITY AS t1(v,o);
SELECT * FROM vw_getrngfunc;
 v | o 
---+---
 1 | 1
(1 row)

DROP VIEW vw_getrngfunc;
-- plpgsql, proretset = f, prorettype = c
CREATE FUNCTION getrngfunc9(int) RETURNS rngfunc AS 'DECLARE rngfunctup rngfunc%ROWTYPE; BEGIN SELECT * into rngfunctup FROM rngfunc WHERE rngfuncid = $1; RETURN rngfunctup; END;' LANGUAGE plpgsql;
SELECT * FROM getrngfunc9(1) AS t1;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o);
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
(1 row)

CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1);
SELECT * FROM vw_getrngfunc;
 rngfuncid | rngfuncsubid | rngfuncname 
-----------+--------------+-------------
         1 |            1 | Joe
(1 row)

DROP VIEW vw_getrngfunc;
CREATE VIEW vw_getrngfunc AS SELECT * FROM getrngfunc9(1) WITH ORDINALITY AS t1(a,b,c,o);
SELECT * FROM vw_getrngfunc;
 a | b |  c  | o 
---+---+-----+---
 1 | 1 | Joe | 1
(1 row)

DROP VIEW vw_getrngfunc;
-- mix 'n match kinds, to exercise expandRTE and related logic
select * from rows from(getrngfunc1(1),getrngfunc2(1),getrngfunc3(1),getrngfunc4(1),getrngfunc5(1),
                    getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
                    getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
                    getrngfunc8(1),getrngfunc9(1))
              with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
 a | b |  c  | d | e |  f  | g | h |  i  | j | k |  l  | m | o |  p  | q | r | s |  t  | u 
---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+-----+---+---+---+-----+---
 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1
   | 1 | Ed  |   |   |     | 1 | 2 | Ed  |   |   |     | 1 | 2 | Ed  |   |   |   |     | 2
(2 rows)

select * from rows from(getrngfunc9(1),getrngfunc8(1),
                    getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
                    getrngfunc6(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
                    getrngfunc5(1),getrngfunc4(1),getrngfunc3(1),getrngfunc2(1),getrngfunc1(1))
              with ordinality as t1(a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u);
 a | b |  c  | d | e | f |  g  | h | i |  j  | k | l |  m  | o | p |  q  |  r  | s | t | u 
---+---+-----+---+---+---+-----+---+---+-----+---+---+-----+---+---+-----+-----+---+---+---
 1 | 1 | Joe | 1 | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1 | Joe | Joe | 1 | 1 | 1
   |   |     |   | 1 | 2 | Ed  |   |   |     | 1 | 2 | Ed  |   |   |     | Ed  | 1 |   | 2
(2 rows)

create temporary view vw_rngfunc as
  select * from rows from(getrngfunc9(1),
                      getrngfunc7(1) AS (rngfuncid int, rngfuncsubid int, rngfuncname text),
                      getrngfunc1(1))
                with ordinality as t1(a,b,c,d,e,f,g,n);
select * from vw_rngfunc;
 a | b |  c  | d | e |  f  | g | n 
---+---+-----+---+---+-----+---+---
 1 | 1 | Joe | 1 | 1 | Joe | 1 | 1
   |   |     | 1 | 2 | Ed  |   | 2
(2 rows)

select pg_get_viewdef('vw_rngfunc');
                                                                                pg_get_viewdef                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT t1.a,                                                                                                                                                               +
     t1.b,                                                                                                                                                                   +
     t1.c,                                                                                                                                                                   +
     t1.d,                                                                                                                                                                   +
     t1.e,                                                                                                                                                                   +
     t1.f,                                                                                                                                                                   +
     t1.g,                                                                                                                                                                   +
     t1.n                                                                                                                                                                    +
    FROM ROWS FROM(getrngfunc9(1), getrngfunc7(1) AS (rngfuncid integer, rngfuncsubid integer, rngfuncname text), getrngfunc1(1)) WITH ORDINALITY t1(a, b, c, d, e, f, g, n);
(1 row)

drop view vw_rngfunc;
DROP FUNCTION getrngfunc1(int);
DROP FUNCTION getrngfunc2(int);
DROP FUNCTION getrngfunc3(int);
DROP FUNCTION getrngfunc4(int);
DROP FUNCTION getrngfunc5(int);
DROP FUNCTION getrngfunc6(int);
DROP FUNCTION getrngfunc7(int);
DROP FUNCTION getrngfunc8(int);
DROP FUNCTION getrngfunc9(int);
DROP FUNCTION rngfunct(int);
DROP TABLE rngfunc2;
DROP TABLE rngfunc;
-- Rescan tests --
CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq1;
CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq2;
CREATE TYPE rngfunc_rescan_t AS (i integer, s bigint);
CREATE FUNCTION rngfunc_sql(int,int) RETURNS setof rngfunc_rescan_t AS 'SELECT i, nextval(''rngfunc_rescan_seq1'') FROM generate_series($1,$2) i;' LANGUAGE SQL;
-- plpgsql functions use materialize mode
CREATE FUNCTION rngfunc_mat(int,int) RETURNS setof rngfunc_rescan_t AS 'begin for i in $1..$2 loop return next (i, nextval(''rngfunc_rescan_seq2'')); end loop; end;' LANGUAGE plpgsql;
--invokes ExecReScanFunctionScan - all these cases should materialize the function only once
-- LEFT JOIN on a condition that the planner can't prove to be true is used to ensure the function
-- is on the inner path of a nestloop join
SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) ON (r+i)<100;
 r | i  | s 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 2 | 11 | 1
 2 | 12 | 2
 2 | 13 | 3
 3 | 11 | 1
 3 | 12 | 2
 3 | 13 | 3
(9 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_sql(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 1 | 12 | 2 | 2
 1 | 13 | 3 | 3
 2 | 11 | 1 | 1
 2 | 12 | 2 | 2
 2 | 13 | 3 | 3
 3 | 11 | 1 | 1
 3 | 12 | 2 | 2
 3 | 13 | 3 | 3
(9 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) ON (r+i)<100;
 r | i  | s 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 2 | 11 | 1
 2 | 12 | 2
 2 | 13 | 3
 3 | 11 | 1
 3 | 12 | 2
 3 | 13 | 3
(9 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN rngfunc_mat(11,13) WITH ORDINALITY AS f(i,s,o) ON (r+i)<100;
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 1 | 12 | 2 | 2
 1 | 13 | 3 | 3
 2 | 11 | 1 | 1
 2 | 12 | 2 | 2
 2 | 13 | 3 | 3
 3 | 11 | 1 | 1
 3 | 12 | 2 | 2
 3 | 13 | 3 | 3
(9 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN ROWS FROM( rngfunc_sql(11,13), rngfunc_mat(11,13) ) WITH ORDINALITY AS f(i1,s1,i2,s2,o) ON (r+i1+i2)<100;
 r | i1 | s1 | i2 | s2 | o 
---+----+----+----+----+---
 1 | 11 |  1 | 11 |  1 | 1
 1 | 12 |  2 | 12 |  2 | 2
 1 | 13 |  3 | 13 |  3 | 3
 2 | 11 |  1 | 11 |  1 | 1
 2 | 12 |  2 | 12 |  2 | 2
 2 | 13 |  3 | 13 |  3 | 3
 3 | 11 |  1 | 11 |  1 | 1
 3 | 12 |  2 | 12 |  2 | 2
 3 | 13 |  3 | 13 |  3 | 3
(9 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) f(i) ON (r+i)<100;
 r | i  
---+----
 1 | 11
 1 | 12
 1 | 13
 2 | 11
 2 | 12
 2 | 13
 3 | 11
 3 | 12
 3 | 13
(9 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN generate_series(11,13) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
 r | i  | o 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 2 | 11 | 1
 2 | 12 | 2
 2 | 13 | 3
 3 | 11 | 1
 3 | 12 | 2
 3 | 13 | 3
(9 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) f(i) ON (r+i)<100;
 r | i  
---+----
 1 | 10
 1 | 20
 1 | 30
 2 | 10
 2 | 20
 2 | 30
 3 | 10
 3 | 20
 3 | 30
(9 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r) LEFT JOIN unnest(array[10,20,30]) WITH ORDINALITY AS f(i,o) ON (r+i)<100;
 r | i  | o 
---+----+---
 1 | 10 | 1
 1 | 20 | 2
 1 | 30 | 3
 2 | 10 | 1
 2 | 20 | 2
 2 | 30 | 3
 3 | 10 | 1
 3 | 20 | 2
 3 | 30 | 3
(9 rows)

--invokes ExecReScanFunctionScan with chgParam != NULL (using implied LATERAL)
SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13);
 r | i  | s 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 2 | 12 | 4
 2 | 13 | 5
 3 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(10+r,13) WITH ORDINALITY AS f(i,s,o);
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 1 | 12 | 2 | 2
 1 | 13 | 3 | 3
 2 | 12 | 4 | 1
 2 | 13 | 5 | 2
 3 | 13 | 6 | 1
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r);
 r | i  | s 
---+----+---
 1 | 11 | 1
 2 | 11 | 2
 2 | 12 | 3
 3 | 11 | 4
 3 | 12 | 5
 3 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_sql(11,10+r) WITH ORDINALITY AS f(i,s,o);
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 2 | 11 | 2 | 1
 2 | 12 | 3 | 2
 3 | 11 | 4 | 1
 3 | 12 | 5 | 2
 3 | 13 | 6 | 3
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2);
 r1 | r2 | i  | s  
----+----+----+----
 11 | 12 | 11 |  1
 11 | 12 | 12 |  2
 13 | 15 | 13 |  3
 13 | 15 | 14 |  4
 13 | 15 | 15 |  5
 16 | 20 | 16 |  6
 16 | 20 | 17 |  7
 16 | 20 | 18 |  8
 16 | 20 | 19 |  9
 16 | 20 | 20 | 10
(10 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_sql(r1,r2) WITH ORDINALITY AS f(i,s,o);
 r1 | r2 | i  | s  | o 
----+----+----+----+---
 11 | 12 | 11 |  1 | 1
 11 | 12 | 12 |  2 | 2
 13 | 15 | 13 |  3 | 1
 13 | 15 | 14 |  4 | 2
 13 | 15 | 15 |  5 | 3
 16 | 20 | 16 |  6 | 1
 16 | 20 | 17 |  7 | 2
 16 | 20 | 18 |  8 | 3
 16 | 20 | 19 |  9 | 4
 16 | 20 | 20 | 10 | 5
(10 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13);
 r | i  | s 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 2 | 12 | 4
 2 | 13 | 5
 3 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(10+r,13) WITH ORDINALITY AS f(i,s,o);
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 1 | 12 | 2 | 2
 1 | 13 | 3 | 3
 2 | 12 | 4 | 1
 2 | 13 | 5 | 2
 3 | 13 | 6 | 1
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r);
 r | i  | s 
---+----+---
 1 | 11 | 1
 2 | 11 | 2
 2 | 12 | 3
 3 | 11 | 4
 3 | 12 | 5
 3 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), rngfunc_mat(11,10+r) WITH ORDINALITY AS f(i,s,o);
 r | i  | s | o 
---+----+---+---
 1 | 11 | 1 | 1
 2 | 11 | 2 | 1
 2 | 12 | 3 | 2
 3 | 11 | 4 | 1
 3 | 12 | 5 | 2
 3 | 13 | 6 | 3
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2);
 r1 | r2 | i  | s  
----+----+----+----
 11 | 12 | 11 |  1
 11 | 12 | 12 |  2
 13 | 15 | 13 |  3
 13 | 15 | 14 |  4
 13 | 15 | 15 |  5
 16 | 20 | 16 |  6
 16 | 20 | 17 |  7
 16 | 20 | 18 |  8
 16 | 20 | 19 |  9
 16 | 20 | 20 | 10
(10 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (11,12),(13,15),(16,20)) v(r1,r2), rngfunc_mat(r1,r2) WITH ORDINALITY AS f(i,s,o);
 r1 | r2 | i  | s  | o 
----+----+----+----+---
 11 | 12 | 11 |  1 | 1
 11 | 12 | 12 |  2 | 2
 13 | 15 | 13 |  3 | 1
 13 | 15 | 14 |  4 | 2
 13 | 15 | 15 |  5 | 3
 16 | 20 | 16 |  6 | 1
 16 | 20 | 17 |  7 | 2
 16 | 20 | 18 |  8 | 3
 16 | 20 | 19 |  9 | 4
 16 | 20 | 20 | 10 | 5
(10 rows)

-- selective rescan of multiple functions:
SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(11,11), rngfunc_mat(10+r,13) );
 r | i  | s | i  | s 
---+----+---+----+---
 1 | 11 | 1 | 11 | 1
 1 |    |   | 12 | 2
 1 |    |   | 13 | 3
 2 | 11 | 1 | 12 | 4
 2 |    |   | 13 | 5
 3 | 11 | 1 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(11,11) );
 r | i  | s | i  | s 
---+----+---+----+---
 1 | 11 | 1 | 11 | 1
 1 | 12 | 2 |    |  
 1 | 13 | 3 |    |  
 2 | 12 | 4 | 11 | 1
 2 | 13 | 5 |    |  
 3 | 13 | 6 | 11 | 1
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM (VALUES (1),(2),(3)) v(r), ROWS FROM( rngfunc_sql(10+r,13), rngfunc_mat(10+r,13) );
 r | i  | s | i  | s 
---+----+---+----+---
 1 | 11 | 1 | 11 | 1
 1 | 12 | 2 | 12 | 2
 1 | 13 | 3 | 13 | 3
 2 | 12 | 4 | 12 | 4
 2 | 13 | 5 | 13 | 5
 3 | 13 | 6 | 13 | 6
(6 rows)

SELECT setval('rngfunc_rescan_seq1',1,false),setval('rngfunc_rescan_seq2',1,false);
 setval | setval 
--------+--------
      1 |      1
(1 row)

SELECT * FROM generate_series(1,2) r1, generate_series(r1,3) r2, ROWS FROM( rngfunc_sql(10+r1,13), rngfunc_mat(10+r2,13) );
 r1 | r2 | i  | s  | i  | s 
----+----+----+----+----+---
  1 |  1 | 11 |  1 | 11 | 1
  1 |  1 | 12 |  2 | 12 | 2
  1 |  1 | 13 |  3 | 13 | 3
  1 |  2 | 11 |  4 | 12 | 4
  1 |  2 | 12 |  5 | 13 | 5
  1 |  2 | 13 |  6 |    |  
  1 |  3 | 11 |  7 | 13 | 6
  1 |  3 | 12 |  8 |    |  
  1 |  3 | 13 |  9 |    |  
  2 |  2 | 12 | 10 | 12 | 7
  2 |  2 | 13 | 11 | 13 | 8
  2 |  3 | 12 | 12 | 13 | 9
  2 |  3 | 13 | 13 |    |  
(13 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) f(i);
 r | i  
---+----
 1 | 11
 1 | 12
 1 | 13
 1 | 14
 1 | 15
 1 | 16
 1 | 17
 1 | 18
 1 | 19
 2 | 12
 2 | 13
 2 | 14
 2 | 15
 2 | 16
 2 | 17
 2 | 18
 3 | 13
 3 | 14
 3 | 15
 3 | 16
 3 | 17
(21 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r), generate_series(10+r,20-r) WITH ORDINALITY AS f(i,o);
 r | i  | o 
---+----+---
 1 | 11 | 1
 1 | 12 | 2
 1 | 13 | 3
 1 | 14 | 4
 1 | 15 | 5
 1 | 16 | 6
 1 | 17 | 7
 1 | 18 | 8
 1 | 19 | 9
 2 | 12 | 1
 2 | 13 | 2
 2 | 14 | 3
 2 | 15 | 4
 2 | 16 | 5
 2 | 17 | 6
 2 | 18 | 7
 3 | 13 | 1
 3 | 14 | 2
 3 | 15 | 3
 3 | 16 | 4
 3 | 17 | 5
(21 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) f(i);
 r | i  
---+----
 1 | 10
 1 | 20
 1 | 30
 2 | 20
 2 | 40
 2 | 60
 3 | 30
 3 | 60
 3 | 90
(9 rows)

SELECT * FROM (VALUES (1),(2),(3)) v(r), unnest(array[r*10,r*20,r*30]) WITH ORDINALITY AS f(i,o);
 r | i  | o 
---+----+---
 1 | 10 | 1
 1 | 20 | 2
 1 | 30 | 3
 2 | 20 | 1
 2 | 40 | 2
 2 | 60 | 3
 3 | 30 | 1
 3 | 60 | 2
 3 | 90 | 3
(9 rows)

-- deep nesting
SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
                                         LEFT JOIN generate_series(21,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
 r1 | r1 | r2 | i  
----+----+----+----
  1 |  1 | 10 | 21
  1 |  1 | 10 | 22
  1 |  1 | 10 | 23
  1 |  1 | 20 | 21
  1 |  1 | 20 | 22
  1 |  1 | 20 | 23
  1 |  1 | 30 | 21
  1 |  1 | 30 | 22
  1 |  1 | 30 | 23
  2 |  2 | 10 | 21
  2 |  2 | 10 | 22
  2 |  2 | 10 | 23
  2 |  2 | 20 | 21
  2 |  2 | 20 | 22
  2 |  2 | 20 | 23
  2 |  2 | 30 | 21
  2 |  2 | 30 | 22
  2 |  2 | 30 | 23
  3 |  3 | 10 | 21
  3 |  3 | 10 | 22
  3 |  3 | 10 | 23
  3 |  3 | 20 | 21
  3 |  3 | 20 | 22
  3 |  3 | 20 | 23
  3 |  3 | 30 | 21
  3 |  3 | 30 | 22
  3 |  3 | 30 | 23
(27 rows)

SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
                                         LEFT JOIN generate_series(20+r1,23) f(i) ON ((r2+i)<100) OFFSET 0) s1;
 r1 | r1 | r2 | i  
----+----+----+----
  1 |  1 | 10 | 21
  1 |  1 | 10 | 22
  1 |  1 | 10 | 23
  1 |  1 | 20 | 21
  1 |  1 | 20 | 22
  1 |  1 | 20 | 23
  1 |  1 | 30 | 21
  1 |  1 | 30 | 22
  1 |  1 | 30 | 23
  2 |  2 | 10 | 22
  2 |  2 | 10 | 23
  2 |  2 | 20 | 22
  2 |  2 | 20 | 23
  2 |  2 | 30 | 22
  2 |  2 | 30 | 23
  3 |  3 | 10 | 23
  3 |  3 | 20 | 23
  3 |  3 | 30 | 23
(18 rows)

SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
                                         LEFT JOIN generate_series(r2,r2+3) f(i) ON ((r2+i)<100) OFFSET 0) s1;
 r1 | r1 | r2 | i  
----+----+----+----
  1 |  1 | 10 | 10
  1 |  1 | 10 | 11
  1 |  1 | 10 | 12
  1 |  1 | 10 | 13
  1 |  1 | 20 | 20
  1 |  1 | 20 | 21
  1 |  1 | 20 | 22
  1 |  1 | 20 | 23
  1 |  1 | 30 | 30
  1 |  1 | 30 | 31
  1 |  1 | 30 | 32
  1 |  1 | 30 | 33
  2 |  2 | 10 | 10
  2 |  2 | 10 | 11
  2 |  2 | 10 | 12
  2 |  2 | 10 | 13
  2 |  2 | 20 | 20
  2 |  2 | 20 | 21
  2 |  2 | 20 | 22
  2 |  2 | 20 | 23
  2 |  2 | 30 | 30
  2 |  2 | 30 | 31
  2 |  2 | 30 | 32
  2 |  2 | 30 | 33
  3 |  3 | 10 | 10
  3 |  3 | 10 | 11
  3 |  3 | 10 | 12
  3 |  3 | 10 | 13
  3 |  3 | 20 | 20
  3 |  3 | 20 | 21
  3 |  3 | 20 | 22
  3 |  3 | 20 | 23
  3 |  3 | 30 | 30
  3 |  3 | 30 | 31
  3 |  3 | 30 | 32
  3 |  3 | 30 | 33
(36 rows)

SELECT * FROM (VALUES (1),(2),(3)) v1(r1),
              LATERAL (SELECT r1, * FROM (VALUES (10),(20),(30)) v2(r2)
                                         LEFT JOIN generate_series(r1,2+r2/5) f(i) ON ((r2+i)<100) OFFSET 0) s1;
 r1 | r1 | r2 | i 
----+----+----+---
  1 |  1 | 10 | 1
  1 |  1 | 10 | 2
  1 |  1 | 10 | 3
  1 |  1 | 10 | 4
  1 |  1 | 20 | 1
  1 |  1 | 20 | 2
  1 |  1 | 20 | 3
  1 |  1 | 20 | 4
  1 |  1 | 20 | 5
  1 |  1 | 20 | 6
  1 |  1 | 30 | 1
  1 |  1 | 30 | 2
  1 |  1 | 30 | 3
  1 |  1 | 30 | 4
  1 |  1 | 30 | 5
  1 |  1 | 30 | 6
  1 |  1 | 30 | 7
  1 |  1 | 30 | 8
  2 |  2 | 10 | 2
  2 |  2 | 10 | 3
  2 |  2 | 10 | 4
  2 |  2 | 20 | 2
  2 |  2 | 20 | 3
  2 |  2 | 20 | 4
  2 |  2 | 20 | 5
  2 |  2 | 20 | 6
  2 |  2 | 30 | 2
  2 |  2 | 30 | 3
  2 |  2 | 30 | 4
  2 |  2 | 30 | 5
  2 |  2 | 30 | 6
  2 |  2 | 30 | 7
  2 |  2 | 30 | 8
  3 |  3 | 10 | 3
  3 |  3 | 10 | 4
  3 |  3 | 20 | 3
  3 |  3 | 20 | 4
  3 |  3 | 20 | 5
  3 |  3 | 20 | 6
  3 |  3 | 30 | 3
  3 |  3 | 30 | 4
  3 |  3 | 30 | 5
  3 |  3 | 30 | 6
  3 |  3 | 30 | 7
  3 |  3 | 30 | 8
(45 rows)

-- check handling of FULL JOIN with multiple lateral references (bug #15741)
SELECT *
FROM (VALUES (1),(2)) v1(r1)
    LEFT JOIN LATERAL (
        SELECT *
        FROM generate_series(1, v1.r1) AS gs1
        LEFT JOIN LATERAL (
            SELECT *
            FROM generate_series(1, gs1) AS gs2
            LEFT JOIN generate_series(1, gs2) AS gs3 ON TRUE
        ) AS ss1 ON TRUE
        FULL JOIN generate_series(1, v1.r1) AS gs4 ON FALSE
    ) AS ss0 ON TRUE;
 r1 | gs1 | gs2 | gs3 | gs4 
----+-----+-----+-----+-----
  1 |     |     |     |   1
  1 |   1 |   1 |   1 |    
  2 |     |     |     |   1
  2 |     |     |     |   2
  2 |   1 |   1 |   1 |    
  2 |   2 |   1 |   1 |    
  2 |   2 |   2 |   1 |    
  2 |   2 |   2 |   2 |    
(8 rows)

DROP FUNCTION rngfunc_sql(int,int);
DROP FUNCTION rngfunc_mat(int,int);
DROP SEQUENCE rngfunc_rescan_seq1;
DROP SEQUENCE rngfunc_rescan_seq2;
--
-- Test cases involving OUT parameters
--
CREATE FUNCTION rngfunc(in f1 int, out f2 int)
AS 'select $1+1' LANGUAGE sql;
SELECT rngfunc(42);
 rngfunc 
---------
      43
(1 row)

SELECT * FROM rngfunc(42);
 f2 
----
 43
(1 row)

SELECT * FROM rngfunc(42) AS p(x);
 x  
----
 43
(1 row)

-- explicit spec of return type is OK
CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS int
AS 'select $1+1' LANGUAGE sql;
-- error, wrong result type
CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int) RETURNS float
AS 'select $1+1' LANGUAGE sql;
ERROR:  function result type must be integer because of OUT parameters
-- with multiple OUT params you must get a RECORD result
CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text) RETURNS int
AS 'select $1+1' LANGUAGE sql;
ERROR:  function result type must be record because of OUT parameters
CREATE OR REPLACE FUNCTION rngfunc(in f1 int, out f2 int, out f3 text)
RETURNS record
AS 'select $1+1' LANGUAGE sql;
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION rngfunc(integer) first.
CREATE OR REPLACE FUNCTION rngfuncr(in f1 int, out f2 int, out text)
AS $$select $1-1, $1::text || 'z'$$ LANGUAGE sql;
SELECT f1, rngfuncr(f1) FROM int4_tbl;
     f1      |          rngfuncr          
-------------+----------------------------
           0 | (-1,0z)
      123456 | (123455,123456z)
     -123456 | (-123457,-123456z)
  2147483647 | (2147483646,2147483647z)
 -2147483647 | (-2147483648,-2147483647z)
(5 rows)

SELECT * FROM rngfuncr(42);
 f2 | column2 
----+---------
 41 | 42z
(1 row)

SELECT * FROM rngfuncr(42) AS p(a,b);
 a  |  b  
----+-----
 41 | 42z
(1 row)

CREATE OR REPLACE FUNCTION rngfuncb(in f1 int, inout f2 int, out text)
AS $$select $2-1, $1::text || 'z'$$ LANGUAGE sql;
SELECT f1, rngfuncb(f1, f1/2) FROM int4_tbl;
     f1      |          rngfuncb          
-------------+----------------------------
           0 | (-1,0z)
      123456 | (61727,123456z)
     -123456 | (-61729,-123456z)
  2147483647 | (1073741822,2147483647z)
 -2147483647 | (-1073741824,-2147483647z)
(5 rows)

SELECT * FROM rngfuncb(42, 99);
 f2 | column2 
----+---------
 98 | 42z
(1 row)

SELECT * FROM rngfuncb(42, 99) AS p(a,b);
 a  |  b  
----+-----
 98 | 42z
(1 row)

-- Can reference function with or without OUT params for DROP, etc
DROP FUNCTION rngfunc(int);
DROP FUNCTION rngfuncr(in f2 int, out f1 int, out text);
DROP FUNCTION rngfuncb(in f1 int, inout f2 int);
--
-- For my next trick, polymorphic OUT parameters
--
CREATE FUNCTION dup (f1 anyelement, f2 out anyelement, f3 out anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT dup(22);
      dup       
----------------
 (22,"{22,22}")
(1 row)

SELECT dup('xyz');	-- fails
ERROR:  could not determine polymorphic type because input has type unknown
SELECT dup('xyz'::text);
        dup        
-------------------
 (xyz,"{xyz,xyz}")
(1 row)

SELECT * FROM dup('xyz'::text);
 f2  |    f3     
-----+-----------
 xyz | {xyz,xyz}
(1 row)

-- fails, as we are attempting to rename first argument
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
ERROR:  cannot change name of input parameter "f1"
HINT:  Use DROP FUNCTION dup(anyelement) first.
DROP FUNCTION dup(anyelement);
-- equivalent behavior, though different name exposed for input arg
CREATE OR REPLACE FUNCTION dup (inout f2 anyelement, out f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
SELECT dup(22);
      dup       
----------------
 (22,"{22,22}")
(1 row)

DROP FUNCTION dup(anyelement);
-- fails, no way to deduce outputs
CREATE FUNCTION bad (f1 int, out f2 anyelement, out f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE sql;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.
--
-- 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']);
                      ^
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 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)

begin;
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;
select * from usersview;  -- expect clean failure
ERROR:  attribute 2 of type record has wrong type
DETAIL:  Table has type numeric, but query expects integer.
drop view usersview;
drop function get_first_user();
drop function get_users();
drop table users;
-- this won't get inlined because of type coercion, but it shouldn't fail
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)

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;