Обсуждение: Functions, composite types and Notice

Hi group,

got a question regarding the different kinds calling a function
returning record.
This is 8.1.3 on gnu/linux.

Consider this function:

CREATE OR REPLACE FUNCTION public.check_notice(
IN in_a int,
IN in_b text,
OUT out_a int,
OUT out_b text
)
RETURNS record as
$BODY$
DECLARE
BEGIN
-- Init
RAISE NOTICE '---- Init';
RAISE NOTICE '---- in_a % ----',in_a;
RAISE NOTICE '---- in_b % ----',in_b;
RAISE NOTICE '---- out_a % ----',out_a;
RAISE NOTICE '---- out_b % ----',out_B;
out_a:=in_a;
out_b:=in_b;
-- assignment
RAISE NOTICE '---- assignment';
RAISE NOTICE '---- in_a % ----',in_a;
RAISE NOTICE '---- in_b % ----',in_b;
RAISE NOTICE '---- out_a % ----',out_a;
RAISE NOTICE '---- out_b % ----',out_B;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

You can call this function like this :
=# select public.check_notice(2,'hello');
NOTICE: ---- Init
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a <NULL> ----
NOTICE: ---- out_b <NULL> ----
NOTICE: ---- assignment
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a 2 ----
NOTICE: ---- out_b hello ----
check_notice
--------------
(2,hello)
(1 row)

Thats OK.

If you want to get the OUT-Params each as columns, you got to call it
this way:
=# select (public.check_notice(2,'hello')).*;
NOTICE: ---- Init
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a <NULL> ----
NOTICE: ---- out_b <NULL> ----
NOTICE: ---- assignment
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a 2 ----
NOTICE: ---- out_b hello ----
NOTICE: ---- Init
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a <NULL> ----
NOTICE: ---- out_b <NULL> ----
NOTICE: ---- assignment
NOTICE: ---- in_a 2 ----
NOTICE: ---- in_b hello ----
NOTICE: ---- out_a 2 ----
NOTICE: ---- out_b hello ----
out_a | out_b
-------+-------
2 | hello
(1 row)

It looks like the function is evaluated twice.
In general the function seems to got evaluated for each OUT-Param.
Is this intended ?
Are their other ways to get the OUT-Params as columns ?
Any hints to the docs?
This would be very convenient, i got a function with 4 OUT-Params and
don't want to pay this price for convenience.

As a side note:
I'm glad to have problems like this.
With the other product i didnt even got the chance :~)

Best regards

Hakan Kocaman

"Hakan Kocaman" <Hakan.Kocaman@digame.de> writes:
> If you want to get the OUT-Params each as columns, you got to call it
> this way:
> =# select (public.check_notice(2,'hello')).*;

Try this way instead:

    select * from public.check_notice(2,'hello');

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, February 01, 2007 5:26 PM
> To: Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Functions, composite types and Notice
>
>
> "Hakan Kocaman" <Hakan.Kocaman@digame.de> writes:
> > If you want to get the OUT-Params each as columns, you got
> to call it
> > this way:
> > =# select (public.check_notice(2,'hello')).*;
>
> Try this way instead:
>
>     select * from public.check_notice(2,'hello');
>
>             regards, tom lane
>

Thanks Tom,

i'm a little bit ashamed :~)

Perhaps i can excuse my blindness with the fact,
that i want to feed the function with 3 params,
that i gather from 2 tables.

so i call the function now like this (obfuscated):
select
    public.check_notice(t1.a,t1.b,t2.c)
from
    public.tab1 t1,
    public.tab2 t2

I'm not clear how i can use the mentioned syntax with this kind of
query.
I can't put the function and the tables on the same level(FROM-Clause),
is their any other way?

Thanks a lot

Hakan *goes buying "SQL for dummies"*

"Hakan Kocaman" <Hakan.Kocaman@digame.de> writes:
>> Try this way instead:
>> select * from public.check_notice(2,'hello');

> so i call the function now like this (obfuscated):
> select
>     public.check_notice(t1.a,t1.b,t2.c)
> from
>     public.tab1 t1,
>     public.tab2 t2

> I'm not clear how i can use the mentioned syntax with this kind of
> query.

No, you can't at the moment; you have to use the way you're doing it.
There's been some speculation that SQL2003's LATERAL syntax might fix
this problem, but no one's dug into it deeply enough to even be sure
of that, let alone figure out what it'll take to implement it.

If you're trying to avoid multiple evaluation of the function, the best
way is to use "OFFSET 0" as an optimization fence to prevent flattening
of a subquery.  I get what seems to be the right thing from

select (x).* from (select sumprod(f1,f2) as x from foo offset 0) ss;

            regards, tom lane

Thanks Tom.

That did it.
Theirs always something to learn, when i read you.

Best regards

Hakan



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, February 02, 2007 4:04 PM
> To: Hakan Kocaman
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Functions, composite types and Notice
>
>
> "Hakan Kocaman" <Hakan.Kocaman@digame.de> writes:
> >> Try this way instead:
> >> select * from public.check_notice(2,'hello');
>
> > so i call the function now like this (obfuscated):
> > select
> >     public.check_notice(t1.a,t1.b,t2.c)
> > from
> >     public.tab1 t1,
> >     public.tab2 t2
>
> > I'm not clear how i can use the mentioned syntax with this kind of
> > query.
>
> No, you can't at the moment; you have to use the way you're doing it.
> There's been some speculation that SQL2003's LATERAL syntax might fix
> this problem, but no one's dug into it deeply enough to even be sure
> of that, let alone figure out what it'll take to implement it.
>
> If you're trying to avoid multiple evaluation of the
> function, the best
> way is to use "OFFSET 0" as an optimization fence to prevent
> flattening
> of a subquery.  I get what seems to be the right thing from
>
> select (x).* from (select sumprod(f1,f2) as x from foo offset 0) ss;
>
>             regards, tom lane
>