Functions, composite types and Notice

From: "Hakan Kocaman" <Hakan(dot)Kocaman(at)digame(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Functions, composite types and Notice
Date: 2007-02-01 07:25:31
Message-ID: 84AAD313D71B1D4F9EE20E739CC3B6ED01664757@ATLANTIK-CL.intern.digame.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shoaib Mir 2007-02-01 08:25:11 Re: How can I list the function.
Previous Message Harvey, Allan AC 2007-02-01 04:46:37 How can I list the function.