Hi,
I have a set returning function returning variable number of rows
(RETURNS SETOF RECORD).
I have a table which contains a list of input values for this SRF.
I want to write SQL which will return all the data found there. A
simple test case:
a tble
CREATE TEMP TABLE list (n int);
INSERT INTO list VALUES (1),(3),(5),(6);
CREATE OR REPLACE FUNCTION test_set (IN OUT n int, OUT bar text)
RETURNS SETOF RECORD AS $$ my $n = shift; for my $x (1..$n) { return_next { n=> $x, bar => '#' x $x } }; return
undef;
$$ LANGUAGE PLperl;
Now, this works fine:
SELECT select * from test_set(4);
n | bar
---+------1 | #2 | ##3 | ###4 | ####
And this works fine too:
SELECT generate_series(1,n) FROM list;generate_series
----------------- 1 1 2 3 1 2
3 4 5 1 2 3 4 5
6
However I would like to write a code which will work with test_set()
as it does with generate_series:
SELECT test_set(n) FROM list;
ERROR: set-valued function called in context that cannot accept a set
...for which I've tried subselects and JOINs but somehow I fail to
make it work, for instance:
SELECT * FROM list, test_set(n);
ERROR: function expression in FROM may not refer to other relations
of same query level
Is there an obvious solution (except wrapping it in some PL function)?
If it is not clear unitl that point, I want to do equivalent of:
SELECT * FROM test_set(1) UNION ALL SELECT * FROM test_set(3) UNION ALL SELECT * FROM test_set(5) UNION ALL SELECT *
FROMtest_set(6);
...except dynamically using "n" column from "list" table.
Of course this is a test case, not the original problem. :-)
Regards, Dawid
