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
Show quoted text
-----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 t2I'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 fromselect (x).* from (select sumprod(f1,f2) as x from foo offset 0) ss;
regards, tom lane