1-- 2-- tsrf - targetlist set returning function tests 3-- 4 5-- simple srf 6SELECT generate_series(1, 3); 7 8-- parallel iteration 9SELECT generate_series(1, 3), generate_series(3,5); 10 11-- parallel iteration, different number of rows 12SELECT generate_series(1, 2), generate_series(1,4); 13 14-- srf, with SRF argument 15SELECT generate_series(1, generate_series(1, 3)); 16 17-- but we've traditionally rejected the same in FROM 18SELECT * FROM generate_series(1, generate_series(1, 3)); 19 20-- srf, with two SRF arguments 21SELECT generate_series(generate_series(1,3), generate_series(2, 4)); 22 23-- check proper nesting of SRFs in different expressions 24explain (verbose, costs off) 25SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4); 26SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4); 27 28CREATE TABLE few(id int, dataa text, datab text); 29INSERT INTO few VALUES(1, 'a', 'foo'),(2, 'a', 'bar'),(3, 'b', 'bar'); 30 31-- SRF with a provably-dummy relation 32explain (verbose, costs off) 33SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; 34SELECT unnest(ARRAY[1, 2]) FROM few WHERE false; 35 36-- SRF shouldn't prevent upper query from recognizing lower as dummy 37explain (verbose, costs off) 38SELECT * FROM few f1, 39 (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss; 40SELECT * FROM few f1, 41 (SELECT unnest(ARRAY[1,2]) FROM few f2 WHERE false OFFSET 0) ss; 42 43-- SRF output order of sorting is maintained, if SRF is not referenced 44SELECT few.id, generate_series(1,3) g FROM few ORDER BY id DESC; 45 46-- but SRFs can be referenced in sort 47SELECT few.id, generate_series(1,3) g FROM few ORDER BY id, g DESC; 48SELECT few.id, generate_series(1,3) g FROM few ORDER BY id, generate_series(1,3) DESC; 49 50-- it's weird to have ORDER BYs that increase the number of results 51SELECT few.id FROM few ORDER BY id, generate_series(1,3) DESC; 52 53-- SRFs are computed after aggregation 54SET enable_hashagg TO 0; -- stable output order 55SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa; 56-- unless referenced in GROUP BY clause 57SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]); 58SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, 5; 59RESET enable_hashagg; 60 61-- check HAVING works when GROUP BY does [not] reference SRF output 62SELECT dataa, generate_series(1,1), count(*) FROM few GROUP BY 1 HAVING count(*) > 1; 63SELECT dataa, generate_series(1,1), count(*) FROM few GROUP BY 1, 2 HAVING count(*) > 1; 64 65-- it's weird to have GROUP BYs that increase the number of results 66SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa ORDER BY 2; 67SELECT few.dataa, count(*) FROM few WHERE dataa = 'a' GROUP BY few.dataa, unnest('{1,1,3}'::int[]) ORDER BY 2; 68 69-- SRFs are not allowed if they'd need to be conditionally executed 70SELECT q1, case when q1 > 0 then generate_series(1,3) else 0 end FROM int8_tbl; 71SELECT q1, coalesce(generate_series(1,3), 0) FROM int8_tbl; 72 73-- SRFs are not allowed in aggregate arguments 74SELECT min(generate_series(1, 3)) FROM few; 75 76-- ... unless they're within a sub-select 77SELECT sum((3 = ANY(SELECT generate_series(1,4)))::int); 78 79SELECT sum((3 = ANY(SELECT lag(x) over(order by x) 80 FROM generate_series(1,4) x))::int); 81 82-- SRFs are not allowed in window function arguments, either 83SELECT min(generate_series(1, 3)) OVER() FROM few; 84 85-- SRFs are normally computed after window functions 86SELECT id,lag(id) OVER(), count(*) OVER(), generate_series(1,3) FROM few; 87-- unless referencing SRFs 88SELECT SUM(count(*)) OVER(PARTITION BY generate_series(1,3) ORDER BY generate_series(1,3)), generate_series(1,3) g FROM few GROUP BY g; 89 90-- sorting + grouping 91SELECT few.dataa, count(*), min(id), max(id), generate_series(1,3) FROM few GROUP BY few.dataa ORDER BY 5, 1; 92 93-- grouping sets are a bit special, they produce NULLs in columns not actually NULL 94set enable_hashagg = false; 95SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab); 96SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY dataa; 97SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab) ORDER BY g; 98SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g); 99SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY dataa; 100SELECT dataa, datab b, generate_series(1,2) g, count(*) FROM few GROUP BY CUBE(dataa, datab, g) ORDER BY g; 101reset enable_hashagg; 102 103-- case with degenerate ORDER BY 104explain (verbose, costs off) 105select 'foo' as f, generate_series(1,2) as g from few order by 1; 106select 'foo' as f, generate_series(1,2) as g from few order by 1; 107 108-- data modification 109CREATE TABLE fewmore AS SELECT generate_series(1,3) AS data; 110INSERT INTO fewmore VALUES(generate_series(4,5)); 111SELECT * FROM fewmore; 112 113-- SRFs are not allowed in UPDATE (they once were, but it was nonsense) 114UPDATE fewmore SET data = generate_series(4,9); 115 116-- SRFs are not allowed in RETURNING 117INSERT INTO fewmore VALUES(1) RETURNING generate_series(1,3); 118 119-- nor standalone VALUES (but surely this is a bug?) 120VALUES(1, generate_series(1,2)); 121 122-- We allow tSRFs that are not at top level 123SELECT int4mul(generate_series(1,2), 10); 124SELECT generate_series(1,3) IS DISTINCT FROM 2; 125 126-- but SRFs in function RTEs must be at top level (annoying restriction) 127SELECT * FROM int4mul(generate_series(1,2), 10); 128 129-- DISTINCT ON is evaluated before tSRF evaluation if SRF is not 130-- referenced either in ORDER BY or in the DISTINCT ON list. The ORDER 131-- BY reference can be implicitly generated, if there's no other ORDER BY. 132 133-- implicit reference (via implicit ORDER) to all columns 134SELECT DISTINCT ON (a) a, b, generate_series(1,3) g 135FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b); 136 137-- unreferenced in DISTINCT ON or ORDER BY 138SELECT DISTINCT ON (a) a, b, generate_series(1,3) g 139FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) 140ORDER BY a, b DESC; 141 142-- referenced in ORDER BY 143SELECT DISTINCT ON (a) a, b, generate_series(1,3) g 144FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) 145ORDER BY a, b DESC, g DESC; 146 147-- referenced in ORDER BY and DISTINCT ON 148SELECT DISTINCT ON (a, b, g) a, b, generate_series(1,3) g 149FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b) 150ORDER BY a, b DESC, g DESC; 151 152-- only SRF mentioned in DISTINCT ON 153SELECT DISTINCT ON (g) a, b, generate_series(1,3) g 154FROM (VALUES (3, 2), (3,1), (1,1), (1,4), (5,3), (5,1)) AS t(a, b); 155 156-- LIMIT / OFFSET is evaluated after SRF evaluation 157SELECT a, generate_series(1,2) FROM (VALUES(1),(2),(3)) r(a) LIMIT 2 OFFSET 2; 158-- SRFs are not allowed in LIMIT. 159SELECT 1 LIMIT generate_series(1,3); 160 161-- tSRF in correlated subquery, referencing table outside 162SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET few.id) FROM few; 163-- tSRF in correlated subquery, referencing SRF outside 164SELECT (SELECT generate_series(1,3) LIMIT 1 OFFSET g.i) FROM generate_series(0,3) g(i); 165 166-- Operators can return sets too 167CREATE OPERATOR |@| (PROCEDURE = unnest, RIGHTARG = ANYARRAY); 168SELECT |@|ARRAY[1,2,3]; 169 170-- Some fun cases involving duplicate SRF calls 171explain (verbose, costs off) 172select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; 173select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1; 174explain (verbose, costs off) 175select generate_series(1,3)+1 order by generate_series(1,3); 176select generate_series(1,3)+1 order by generate_series(1,3); 177 178-- Check that SRFs of same nesting level run in lockstep 179explain (verbose, costs off) 180select generate_series(1,3) as x, generate_series(3,6) + 1 as y; 181select generate_series(1,3) as x, generate_series(3,6) + 1 as y; 182 183-- Clean up 184DROP TABLE few; 185DROP TABLE fewmore; 186