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