From: | Tuukka Norri <tuukka(dot)norri(at)karppinen(dot)fi> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Joining with set returning functions |
Date: | 2007-07-15 15:22:34 |
Message-ID: | F3451935-BC33-492E-92A3-BA6BAD3DDDD6@karppinen.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
Can tables be joined with set returning functions? For example, it is
indeed possible to perform queries such as this:
SELECT oid, do_something (oid) FROM pg_class WHERE relkind = 'r';
Here, do_something returns only one record. What I'd like to do
instead is to select from a function that returns multiple records
like this:
SELECT f.* FROM do_something (c.oid) f, pg_class c WHERE c.relkind =
'r';
SELECT f.* FROM (SELECT oid FROM pg_class WHERE relkind = 'r') c,
do_something (c.oid) f;
SELECT f.* FROM (SELECT oid FROM pg_class WHERE relkind = 'r') c LEFT
JOIN do_something (c.oid) f;
SELECT f.* FROM (SELECT oid FROM pg_class WHERE relkind = 'r') c
RIGHT JOIN do_something (c.oid) f;
etc.
What I'm expecting is that the database would call do_something with
each oid found in pg_class and then return an union of all the rows
returned by the function. However, I keep getting error messages,
such as the following:
function expression in FROM may not refer to other relations of same
query level
invalid reference to FROM-clause entry HINT: There is an entry for
table "c", but it cannot be referenced from this part of the query.
I can't figure out how to move the select from pg_class to another
level. I could write another PL/pgSQL function to select the rows and
then call do_something for each row, but I'm not sure if this was
noticed by the query optimizer. Any hints on this matter?
--
Best regards,
Tuukka
From | Date | Subject | |
---|---|---|---|
Next Message | angga erwina | 2007-07-15 16:03:47 | slony over LAN and VPN |
Previous Message | Devrim GÜNDÜZ | 2007-07-15 09:13:29 | Re: smtp |