plpgsql return select from multiple tables

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