From M.Mamin@intershop.de Tue Oct 27 16:08:25 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BFD4F633257 for ; Tue, 27 Oct 2009 13:08:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 42285-05 for ; Tue, 27 Oct 2009 16:08:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailgate.intershop.de (mailgate.intershop.de [217.17.202.241]) by mail.postgresql.org (Postfix) with ESMTP id A97816330AC for ; Tue, 27 Oct 2009 13:08:17 -0300 (ADT) Received: from JENMAIL01.ad.intershop.net ([10.0.87.43]) by mailgate.intershop.de (8.14.3/8.13.6) with ESMTP id n9RG884S007734 for ; Tue, 27 Oct 2009 17:08:08 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA571F.AC1982CF" Subject: record to columns: syntax question and strange behaviour Date: Tue, 27 Oct 2009 17:08:07 +0100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: record to columns: syntax question and strange behaviour Thread-Index: AcpXH6w0TZNQMs1ATSKHZgu9ckqksg== From: "Marc Mamin" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/130 X-Sequence-Number: 33329 This is a multi-part message in MIME format. ------_=_NextPart_001_01CA571F.AC1982CF Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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:=3Da+1; c:=3Da+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=20 Following returns the expected result, but the function is called for each OUT parameter: select column1, (test(column1)).* FROM (values(1),(2)) foo =3D>=20 1,2,3 2,3,4=20 NOTICE: done: 1 NOTICE: done: 1 NOTICE: done: 2 NOTICE: done: 2 Is there a way to avoid it ??? Thanks, Marc Mamin ------_=_NextPart_001_01CA571F.AC1982CF Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable record to columns: syntax question and strange behaviour

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:=3Da+1;
  c:=3Da+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

=3D>

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

------_=_NextPart_001_01CA571F.AC1982CF-- From mlists@rp-online.de Tue Oct 27 17:20:56 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 597C16327E0 for ; Tue, 27 Oct 2009 14:20:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51471-06 for ; Tue, 27 Oct 2009 17:20:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.rp-online.de (mail.rp-online.de [149.221.1.199]) by mail.postgresql.org (Postfix) with ESMTP id C7D1763363E for ; Tue, 27 Oct 2009 14:20:48 -0300 (ADT) Received: from [10.1.19.116] (vpn17.1.rp-online.de [10.1.19.116]) by mail.rp-online.de (Postfix) with ESMTP id 2ED3E30928; Tue, 27 Oct 2009 18:20:47 +0100 (CET) Message-ID: <4AE72BEE.5070801@rp-online.de> Date: Tue, 27 Oct 2009 18:20:46 +0100 From: Thomas Pundt Reply-To: thomas.pundt@rp-online.de User-Agent: Thunderbird 2.0.0.23 (Macintosh/20090812) MIME-Version: 1.0 To: Marc Mamin CC: pgsql-sql@postgresql.org Subject: Re: record to columns: syntax question and strange behaviour References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Anti-Virus: Kaspersky Anti-Virus for MailServers 5.5.10/RELEASE, bases: 27102009 #2857262, status: clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200910/131 X-Sequence-Number: 33330 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 From M.Mamin@intershop.de Tue Oct 27 18:51:03 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A81516342F1 for ; Tue, 27 Oct 2009 15:51:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 64263-03 for ; Tue, 27 Oct 2009 18:50:50 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailgate.intershop.de (mailgate.intershop.de [217.17.202.241]) by mail.postgresql.org (Postfix) with ESMTP id C75FB633DD0 for ; Tue, 27 Oct 2009 15:50:52 -0300 (ADT) Received: from JENMAIL01.ad.intershop.net ([10.0.87.43]) by mailgate.intershop.de (8.14.3/8.13.6) with ESMTP id n9RIoc3a022277 for ; Tue, 27 Oct 2009 19:50:45 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CA5736.5FD9CF93" Subject: Re: record to columns: syntax question and strange behaviour Date: Tue, 27 Oct 2009 19:46:50 +0100 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] record to columns: syntax question and strange behaviour Thread-Index: AcpXKdxJASHJkQqSRYe8pg3oV2Mz0wAC/unK References: <4AE72BEE.5070801@rp-online.de> From: "Marc Mamin" To: Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.296 tagged_above=-10 required=5 tests=AWL=-0.187, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/132 X-Sequence-Number: 33331 This is a multi-part message in MIME format. ------_=_NextPart_001_01CA5736.5FD9CF93 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, Your proposal unfortunately does not work if you try to query more than = one value and want additional columns in the results,=20 like in=20 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:=3Da+1; > c:=3Da+2; > return next; >END >$BODY$ > LANGUAGE 'plpgsql' > >and then issue > >SELECT * FROM test(1); ------_=_NextPart_001_01CA5736.5FD9CF93 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable AW: [SQL] record to columns: syntax question and strange = behaviour

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:=3Da+1;
>   c:=3Da+2;
>   return next;
>END
>$BODY$
>   LANGUAGE 'plpgsql'
>
>and then issue
>
>SELECT * FROM test(1);

------_=_NextPart_001_01CA5736.5FD9CF93--