I have a function that needs to return results from more than 1 table. Is this possible to do so in Postgresql (9.1 now, planning migration to 9.2 soon)? If so, how.
For example
create or replace function f1()
returns ???
as $body$ begin
return query select x.*
from users x;
return query select x.*
from user_preferences x join users y using(user_id) ; -- more where clauses
return query select x.*
from user_permissions x join users y using(user_id) ; -- more clauses
end; $body$ language 'plpgsql';
The alternatives I considered were: (a) Doing a cartesian product of users and user_preferences (etc.) but that will lead to a gigantic result table and a truly convoluted code. (b) First doing a select on users and then iterating the list of users and depending on their attributes, doing additional iterations, but that will lead to way too many database calls. And, why shall I write code that SQL is so superbly good at?
Is there a clean cut way of getting multiple resultsets out of a pg function? I have searched the postgresql documentation but could not find any guidance.
Thanks a lot.
