From: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: JOINing SET returning function.
Date: Tue, 29 Jan 2008 16:29:50 +0100
Message-ID: <758d5e7f0801290729t64bd9ad8oaeb05a521baa6ca5@mail.gmail.com> (raw)
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 * FROM test_set(6);
...except dynamically using "n" column from "list" table.
Of course this is a test case, not the original problem. :-)
Regards,
Dawid
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: qnex42@gmail.com
Subject: Re: JOINing SET returning function.
In-Reply-To: <758d5e7f0801290729t64bd9ad8oaeb05a521baa6ca5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox