[SQL] record to columns: syntax question and strange behaviour

[Marc Mamin]

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

Re: [SQL] record to columns: syntax question and strange behaviour

[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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] record to columns: syntax question and strange behaviour

[Marc Mamin]

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);