record to columns: syntax question and strange behaviour

[Marc Mamin-2]

Hello,

how should I retrieve the result from a function with some OUT paramenters?

(PG is  8.3.7)

here a short example to illustrate my question:

CREATE OR REPLACE FUNCTION test (In a int, OUT b int, OUT c int) AS
$BODY$
BEGIN
  b:=a+1;
  c:=a+2;
  raise notice 'done: %', a;
END

$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE

select column1, test(column1) FROM (values(1),(2)) foo

1, (2,3)
2, (3,4)

NOTICE:  done: 1
NOTICE:  done: 2

What I want is just

1,2,3
2,3,4

Following returns the expected result, but the function is called for each OUT parameter:

select column1, (test(column1)).* FROM  (values(1),(2)) foo

=>

1,2,3
2,3,4

NOTICE:  done: 1
NOTICE:  done: 1
NOTICE:  done: 2
NOTICE:  done: 2

Is there a way to avoid it ???

Thanks,

Marc Mamin

[Thomas Pundt]

Hi,

Marc Mamin schrieb:

> how should I retrieve the result from a function with some OUT
> paramenters?
>
> (PG is  8.3.7)
>
> here a short example to illustrate my question:
>
> CREATE OR REPLACE FUNCTION test (In a int, OUT b int, OUT c int) AS
> $BODY$
> BEGIN
>   b:=a+1;
>   c:=a+2;
>   raise notice 'done: %', a;
> END
>
> $BODY$
>   LANGUAGE 'plpgsql' IMMUTABLE

IMO easiest would be to include a   RETURNS SETOF record   in the
function declaration and a   return next;   statement in the function
body. E.g.


CREATE OR REPLACE FUNCTION test (In a int, OUT b int, OUT c int)
RETURNS SETOF record
AS
$BODY$
BEGIN
   b:=a+1;
   c:=a+2;
   return next;
END
$BODY$
   LANGUAGE 'plpgsql'

and then issue

SELECT * FROM test(1);

Ciao,
Thomas

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Marc Mamin-2]

Hello,

Your proposal unfortunately does not work if you try to query more than one value and want additional columns in the results,
like in

select column1,test(column1) FROM (values(1),(2)) foo

cheers,

Marc Mamin


>IMO easiest would be to include a   RETURNS SETOF record   in the
>function declaration and a   return next;   statement in the function
>body. E.g.
>
>
>CREATE OR REPLACE FUNCTION test (In a int, OUT b int, OUT c int)
>RETURNS SETOF record
>AS
>$BODY$
>BEGIN
>   b:=a+1;
>   c:=a+2;
>   return next;
>END
>$BODY$
>   LANGUAGE 'plpgsql'
>
>and then issue
>
>SELECT * FROM test(1);