Re: plpgsql return select from multiple tables

On Wed, Sep 10, 2008 at 5:43 PM, Filip Rembiałkowski
<plk(dot)zuber(at)gmail(dot)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