Re: plpgsql return select from multiple tables

2008/9/10 Artis Caune <artis(dot)caune(at)gmail(dot)com>:
> Hi,
>
> What is the correct way of writing plpgsql function which needs return
> columns from multiple tables?
>
> e.x.:
> SELECT email FROM emails WHERE id = 1
> SELECT backend FROM backends WHERE id = 1
>
> I need plpgsql function return both email and backend in one line, like:
> SELECT email, backend FROM ...
>

in principle, you don't need procedural language for this:

SELECT
(SELECT email FROM emails WHERE id = 1) as email,
(SELECT backend FROM backends WHERE id = 1) as backend;

>
> I do like this:
>
> CREATE OR REPLACE FUNCTION get_user_data( INT )
> RETURNS SETOF RECORD AS $$
> DECLARE
> v_email RECORD;
> v_backend RECORD;
> BEGIN
> SELECT email
> INTO v_email
> FROM emails
> WHERE id = $1;
>
> SELECT backend
> INTO v_backend
> FROM backends
> WHERE id = $1;
>
> RETURN QUERY SELECT v_email AS email,
> v_backend AS backend;
> END;
> $$ LANGUAGE 'plpgsql' SECURITY DEFINER;

nothing wrong here but this can also be rewritten to pure SQL function
(can be few percent faster and optimizable by planner)

CREATE OR REPLACE FUNCTION get_user_data( INT )
RETURNS SETOF RECORD AS $$
SELECT
(SELECT email FROM emails WHERE id = $1) as email,
(SELECT backend FROM backends WHERE id = $1) as backend
$$ LANGUAGE 'sql' STABLE STRICT SECURITY DEFINER;

one question, why SETOF? this is supposed to always return one row
always, right?
you could create a TYPE and return this. queries would be a bit simpler:

SELECT * FROM get_user_data('${id}');

finally, I am *almost* sure (maybe someone will correct me) that if
you encapsulate this in a function, you will always have some
performance penalty because
SELECT email FROM get_user_data('${id}');
will always scan backends table, even if it's not needed.

for such usage, VIEWs are nicer.

create view user_data as
select u.id, e.email, b.backend
from users u [left?] join emails e on e.id=u.id [left?] join backends
b on b.id = u.id;

and

select * from user_data where id=1;

>
>
> and then doing selects:
> SELECT * FROM get_user_data('${id}') AS (email VARCHAR, backend VARCHAR)
>
>
> Is it okay, there will be a lot of those queries?
>
>
>
>
> --
> regards,
> Artis Caune
>
> <----. CCNA
> <----|====================
> <----' didii FreeBSD
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Filip Rembiałkowski