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)

-- check detection of mismatching record types with a const-folded expression
with a(b) as (values (row(1,2,3)))
select * from a, coalesce(b) as c(d int, e int);  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 3 attributes, but query expects 2.
with a(b) as (values (row(1,2,3)))
select * from a, coalesce(b) as c(d int, e int, f int, g int);  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 3 attributes, but query expects 4.
with a(b) as (values (row(1,2,3)))
select * from a, coalesce(b) as c(d int, e int, f float);  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned type integer at ordinal position 3, but query expects double precision.
select * from int8_tbl, coalesce(row(1)) as (a int, b int);  -- fail
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 1 attribute, but query expects 2.