pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

record to columns: syntax question and strange behaviour
3+ messages / 2 participants
[nested] [flat]

* record to columns: syntax question and strange behaviour
@ 2009-10-27 16:08  Marc Mamin <M.Mamin@intershop.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Marc Mamin @ 2009-10-27 16:08 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: record to columns: syntax question and strange behaviour
@ 2009-10-27 17:20  Thomas Pundt <mlists@rp-online.de>
  parent: Marc Mamin <M.Mamin@intershop.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Thomas Pundt @ 2009-10-27 17:20 UTC (permalink / raw)
  To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: record to columns: syntax question and strange behaviour
@ 2009-10-27 18:46  Marc Mamin <M.Mamin@intershop.de>
  parent: Thomas Pundt <mlists@rp-online.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Marc Mamin @ 2009-10-27 18:46 UTC (permalink / raw)
  To: thomas.pundt@rp-online.de; +Cc: pgsql-sql


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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2009-10-27 18:46 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-27 16:08 record to columns: syntax question and strange behaviour Marc Mamin <M.Mamin@intershop.de>
2009-10-27 17:20 ` Thomas Pundt <mlists@rp-online.de>
2009-10-27 18:46   ` Marc Mamin <M.Mamin@intershop.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox