Thread: plpgsql return select from multiple tables

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 ...


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;


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

Artis Caune escribió:
> 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 ...

Hmm, maybe

select email, backend from emails, backends where email.id = 1 and
backend.id = 1;
?

You don't need a plpgsql function for this ...

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Wed, Sep 10, 2008 at 5:26 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Hmm, maybe
>
> select email, backend from emails, backends where email.id = 1 and
> backend.id = 1;
> ?
>
> You don't need a plpgsql function for this ...

Ops, forget to mention that this function is not so simple and use
some plpgsql features.
Here is one of them:
http://dpaste.com/hold/77192/




--
regards,
Artis Caune

<----. CCNA
<----|====================
<----' didii FreeBSD

2008/9/10 Artis Caune <artis.caune@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Filip Rembiałkowski

Artis Caune escribió:

> Ops, forget to mention that this function is not so simple and use
> some plpgsql features.

Ah, right, you only forgot to mention that other 99% of the
requirements.

What's wrong with your first example?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Wed, Sep 10, 2008 at 5:43 PM, Filip Rembiałkowski
<plk.zuber@gmail.com> wrote:
> 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;

We need some logic in selects (because applications like postfix can
do just simple queries):
- select email
- if not found then return empty
- if email.type is 1 then return foo
- if email.type is 2 then return bar

'IF FOUND THEN' or 'IF variable = X THEN' features are only in plpgsql, i think.
Didn't know that sql functions also can be definied with "SECURITY
DEFINER". We use them, so query_user can only select from function and
can not see the whole table/tables. I'll check sql functions.


> 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}');

Yes, it should return only one row. I can not use "return query" without SETOF.
Or should I create my_type, select into my_type_variable and return
my_type_variable?





--
regards,
Artis Caune

<----. CCNA
<----|====================
<----' didii FreeBSD

On Wed, Sep 10, 2008 at 5:56 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Ah, right, you only forgot to mention that other 99% of the
> requirements.
:)

> What's wrong with your first example?

It works, but those type casts "TRUE::BOOLEAN AS forwards" and selects
with "AS (email VARCHAR, backend VARCHAR)" are confusing me.





--
regards,
Artis Caune

<----. CCNA
<----|====================
<----' didii FreeBSD

Artis Caune wrote:

> 'IF FOUND THEN' or 'IF variable = X THEN' features are only in plpgsql, i think.

You can frequently achieve similar effects with COALESCE and/or CASE
expressions in normal SQL. This can be a LOT faster.

--
Craig Ringer