| 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 |
| -- Backward scan not supported in GPDB, which makes this a lot less |
| -- interesting than in PostgreSQL. |
| 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; |
| fetch all from rf_cur; |
| i | j | o |
| ---+---+--- |
| (0 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; |
| --fetch absolute 1 from rf_cur; |
| fetch next from rf_cur; |
| i | j | o |
| ---+---+--- |
| (0 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; |
| --fetch prior from rf_cur; |
| --fetch prior from rf_cur; |
| commit; |
| -- function with implicit LATERAL |
| select * from rngfunc2, rngfunct(rngfunc2.rngfuncid) z where rngfunc2.f2 = z.f2; |
| ERROR: query plan with multiple segworker groups is not supported |
| HINT: likely caused by a function that reads or modifies data in a distributed table |
| CONTEXT: SQL function "rngfunct" statement 1 |
| -- 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; |
| ERROR: query plan with multiple segworker groups is not supported |
| HINT: likely caused by a function that reads or modifies data in a distributed table |
| CONTEXT: SQL function "rngfunct" statement 1 |
| -- 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; |
| ERROR: query plan with multiple segworker groups is not supported |
| HINT: likely caused by a function that reads or modifies data in a distributed table |
| CONTEXT: SQL function "rngfunct" statement 1 |
| -- function in subselect |
| select * from rngfunc2 where f2 in (select f2 from rngfunct(1) z where z.rngfuncid = rngfunc2.rngfuncid) ORDER BY 1,2; |
| ERROR: function cannot execute on a QE slice because it accesses relation "public.rngfunc2" |
| CONTEXT: SQL function "rngfunct" during startup |
| -- function in subselect |
| select * from rngfunc2 where f2 in (select f2 from rngfunct(rngfunc2.rngfuncid) z where z.rngfuncid = 1) ORDER BY 1,2; |
| ERROR: query plan with multiple segworker groups is not supported |
| HINT: likely caused by a function that reads or modifies data in a distributed table |
| CONTEXT: SQL function "rngfunct" statement 1 |
| -- 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ;' 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ;' 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ;' 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ;' 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ;' 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 ORDER BY rngfuncname DESC /* ORDER BY to force the Joe row to be returned */ ; 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 -- |
| -- GPDB sets the cache to 1 to ensure consistency in tests |
| CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq1 CACHE 1; |
| CREATE TEMPORARY SEQUENCE rngfunc_rescan_seq2 CACHE 1; |
| 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 |
| -- |
| -- GPDB: use a sequence column instead of serial to enforce a cache size for consistent results |
| create temporary sequence tt_seq cache 1; |
| create temp table tt(f1 int NOT NULL DEFAULT nextval('tt_seq'), data text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| alter sequence tt_seq owned by tt.f1; |
| -- GPDB: The tests below which throw NOTICEs, throw them in indeterminate |
| -- order, if the rows are hashed to different segments. Force the rows |
| -- that have problem to be hashed to the same segment, using a custom hash |
| -- function. |
| CREATE OPERATOR FAMILY dummy_int_hash_ops USING hash; |
| CREATE FUNCTION dummy_hashfunc(int) RETURNS int AS $$ |
| begin |
| return CASE WHEN $1 BETWEEN 7 AND 15 THEN 0 ELSE $1 END; |
| end; $$ LANGUAGE plpgsql STRICT IMMUTABLE; |
| CREATE OPERATOR CLASS dummy_int_hash_ops FOR TYPE int4 |
| USING hash FAMILY dummy_int_hash_ops AS |
| OPERATOR 1 =, |
| FUNCTION 1 dummy_hashfunc(int); |
| ALTER TABLE tt SET DISTRIBUTED BY (f1 dummy_int_hash_ops); |
| 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>0)::int $$ |
| language sql; |
| select insert_tt('fool'); |
| insert_tt |
| ----------- |
| 1 |
| (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. |
| -- GPDB: Only select data here. With triggers and rules, some may execute in |
| -- different orders depending on which segment triggers first--causing the |
| -- sequence number to be different. Therefore, we only select the data here to |
| -- ensure consistency in the tests |
| select data from tt_log; |
| data |
| --------- |
| barlog |
| foollog |
| (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 |
| Output: f1, f2 |
| Function Call: array_to_set('{one,two}'::text[]) |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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 startup |
| 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 |
| Output: f1, f2 |
| Function Call: array_to_set('{one,two}'::text[]) |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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 |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (4 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 |
| Settings: optimizer = 'on' |
| Optimizer: Postgres query optimizer |
| (5 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() |
| Settings: optimizer = 'on' |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| select testrngfunc(); |
| testrngfunc |
| ----------------- |
| (7.136178,7.14) |
| (1 row) |
| |
| explain (verbose, costs off) |
| select * from testrngfunc(); |
| QUERY PLAN |
| ---------------------------------------------------------------- |
| Subquery Scan on sirvf_sq |
| Output: (sirvf_sq.testrngfunc).f1, (sirvf_sq.testrngfunc).f2 |
| -> Result |
| Output: $0 |
| InitPlan 1 (returns $0) |
| -> Result |
| Output: testrngfunc() |
| Settings: optimizer = 'on' |
| Optimizer: Postgres query optimizer |
| (9 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 |
| Output: true |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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() |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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 |
| Output: true |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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() |
| Settings: optimizer = 'on' |
| Optimizer: Postgres query optimizer |
| (5 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 |
| Output: true |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select testrngfunc(); |
| testrngfunc |
| ----------------- |
| (1.000000,2.00) |
| (3.000000,4.00) |
| (2 rows) |
| |
| explain (verbose, costs off) |
| select * from testrngfunc(); |
| QUERY PLAN |
| --------------------------------------- |
| Function Scan on public.testrngfunc |
| Output: f1, f2 |
| Function Call: testrngfunc() |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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) |
| |
| 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; |
| -- 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 |
| --------------------------------------- |
| Function Scan on public.rngfuncbar |
| Output: rngfuncbar |
| Function Call: rngfuncbar() |
| Settings: optimizer = 'on' |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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 |
| ------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: f.x |
| -> 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 |
| Optimizer: Postgres query optimizer |
| (10 rows) |
| |
| select x from int8_tbl, extractq2(int8_tbl) f(x); |
| x |
| ------------------- |
| 123 |
| 4567890123456789 |
| -4567890123456789 |
| 456 |
| 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 |
| --------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: ((int8_tbl.*).q2) |
| -> Nested Loop |
| Output: ((int8_tbl.*).q2) |
| -> Seq Scan on public.int8_tbl |
| Output: int8_tbl.* |
| -> Materialize |
| Output: ((int8_tbl.*).q2) |
| -> Result |
| Output: (int8_tbl.*).q2 |
| Optimizer: Postgres query optimizer |
| Settings: optimizer=on |
| (12 rows) |
| |
| select x from int8_tbl, extractq2_2(int8_tbl) f(x); |
| x |
| ------------------- |
| 123 |
| 4567890123456789 |
| -4567890123456789 |
| 456 |
| 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 |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: int8_tbl.q2 |
| -> Seq Scan on public.int8_tbl |
| Output: int8_tbl.q2 |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); |
| x |
| ------------------- |
| 123 |
| 4567890123456789 |
| -4567890123456789 |
| 456 |
| 4567890123456789 |
| (5 rows) |
| |
| -- gpdb: test append node in subquery_motionHazard_walker(). Without that |
| -- change the select query below will panic. |
| create function extractq2_append(t int8_tbl) returns table(ret1 int8) as $$ |
| select extractq2(t) union all select extractq2(t) |
| $$ language sql immutable; |
| explain (verbose, costs off) |
| select x from (select * from int8_tbl order by 1 limit 100) as int8_tbl, extractq2_append(int8_tbl) f(x); |
| QUERY PLAN |
| ------------------------------------------------------------------ |
| Nested Loop |
| Output: (int8_tbl.q2) |
| -> Limit |
| Output: int8_tbl.q1, int8_tbl.q2 |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| Output: int8_tbl.q1, int8_tbl.q2 |
| Merge Key: int8_tbl.q1 |
| -> Limit |
| Output: int8_tbl.q1, int8_tbl.q2 |
| -> Sort |
| Output: int8_tbl.q1, int8_tbl.q2 |
| Sort Key: int8_tbl.q1 |
| -> Seq Scan on public.int8_tbl |
| Output: int8_tbl.q1, int8_tbl.q2 |
| -> Append |
| -> Result |
| Output: int8_tbl.q2 |
| -> Result |
| Output: int8_tbl.q2 |
| Optimizer: Postgres query optimizer |
| (20 rows) |
| |
| select x from (select * from int8_tbl order by 1 limit 100) as int8_tbl, extractq2_append(int8_tbl) f(x); |
| x |
| ------------------- |
| 456 |
| 456 |
| 4567890123456789 |
| 4567890123456789 |
| 123 |
| 123 |
| 4567890123456789 |
| 4567890123456789 |
| -4567890123456789 |
| -4567890123456789 |
| (10 rows) |
| |
| create function extractq2_wapper(t int8_tbl) returns table(ret1 int8) as $$ |
| select (select extractq2(t)) |
| $$ language sql immutable; |
| explain (verbose, costs off) select x from int8_tbl, extractq2_wapper(int8_tbl) f(x); |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: ((SubPlan 1)) |
| -> Seq Scan on public.int8_tbl |
| Output: (SubPlan 1) |
| SubPlan 1 |
| -> Result |
| Output: (int8_tbl.*).q2 |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| select x from int8_tbl, extractq2_wapper(int8_tbl) f(x); |
| x |
| ------------------- |
| 123 |
| 4567890123456789 |
| -4567890123456789 |
| 456 |
| 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)) |
| Optimizer: Postgres query optimizer |
| (9 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) |
| |
