Thread: record to columns: syntax question and strange behaviour

<p><font face="Arial" size="2">Hello,</font><p><font face="Arial" size="2">how should I retrieve the result from a
functionwith some OUT paramenters?</font><p><font face="Arial" size="2">(PG is  8.3.7)</font><p><font face="Arial"
size="2">herea short example to illustrate my question:</font><p><font face="Arial" size="2">CREATE OR REPLACE FUNCTION
test(In a int, OUT b int, OUT c int) AS</font><br /><font face="Arial" size="2">$BODY$</font><br /><font face="Arial"
size="2">BEGIN</font><br/><font face="Arial" size="2">  b:=a+1;</font><br /><font face="Arial" size="2"> 
c:=a+2;</font><br/><font face="Arial" size="2">  raise notice 'done: %', a;</font><br /><font face="Arial"
size="2">END</font><p><fontface="Arial" size="2">$BODY$</font><br /><font face="Arial" size="2">  LANGUAGE 'plpgsql'
IMMUTABLE</font><br/><br /><br /><p><font face="Arial" size="2">select column1, test(column1) FROM (values(1),(2))
foo</font><p><fontface="Arial" size="2">1, (2,3)</font><br /><font face="Arial" size="2">2, (3,4)</font><p><font
face="Arial"size="2">NOTICE:  done: 1</font><br /><font face="Arial" size="2">NOTICE:  done: 2</font><br /><p><font
face="Arial"size="2">What I want is just</font><p><font face="Arial" size="2">1,2,3</font><br /><font face="Arial"
size="2">2,3,4</font><br /><p><font face="Arial" size="2">Following returns the expected result, but the function is
calledfor each OUT parameter:</font><p><font face="Arial" size="2">select column1, (test(column1)).* FROM 
(values(1),(2))foo</font><p><font face="Arial" size="2">=> </font><p><font face="Arial" size="2">1,2,3</font><br
/><fontface="Arial" size="2">2,3,4 </font><p><font face="Arial" size="2">NOTICE:  done: 1</font><br /><font
face="Arial"size="2">NOTICE:  done: 1</font><br /><font face="Arial" size="2">NOTICE:  done: 2</font><br /><font
face="Arial"size="2">NOTICE:  done: 2</font><p><font face="Arial" size="2">Is there a way to avoid it ???</font><br
/><p><fontface="Arial" size="2">Thanks,</font><p><font face="Arial" size="2">Marc Mamin</font> 

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

<br /><p><font size="2">Hello,<br /><br /> Your proposal unfortunately does not work if you try to query more than one
valueand want additional columns in the results,<br /> like in<br /><br /> select column1,test(column1) FROM
(values(1),(2))foo<br /><br /> cheers,<br /><br /> Marc Mamin<br /><br /><br /> >IMO easiest would be to include a  
RETURNSSETOF record   in the<br /> >function declaration and a   return next;   statement in the function<br />
>body.E.g.<br /> ><br /> ><br /> >CREATE OR REPLACE FUNCTION test (In a int, OUT b int, OUT c int)<br />
>RETURNSSETOF record<br /> >AS<br /> >$BODY$<br /> >BEGIN<br /> >   b:=a+1;<br /> >   c:=a+2;<br />
>  return next;<br /> >END<br /> >$BODY$<br /> >   LANGUAGE 'plpgsql'<br /> ><br /> >and then
issue<br/> ><br /> >SELECT * FROM test(1);<br /><br /></font>