Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D258B9FB43A for ; Mon, 16 Oct 2006 23:02:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 66034-02 for ; Mon, 16 Oct 2006 23:02:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web35311.mail.mud.yahoo.com (web35311.mail.mud.yahoo.com [66.163.179.105]) by postgresql.org (Postfix) with SMTP id 338329FA085 for ; Mon, 16 Oct 2006 23:02:35 -0300 (ADT) Received: (qmail 88474 invoked by uid 60001); 17 Oct 2006 02:02:33 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=WplT+RqluwSJEjl2ZF5QAzrZEmD5asQ5HAI0u0lfwakd0z1Sk8U7Wvk6/MJWavxi25eC8Pg2+ldSjUln4VWAavx03qM8DeAhxQRgy3wO0srej8WqWFlzOHFtnmqRkyXRRRdTX9Vn1Rhg77xwc4HMX9O+udgK4zZmlixzHxrcNDg= ; Message-ID: <20061017020233.88472.qmail@web35311.mail.mud.yahoo.com> Received: from [74.98.189.48] by web35311.mail.mud.yahoo.com via HTTP; Mon, 16 Oct 2006 19:02:33 PDT Date: Mon, 16 Oct 2006 19:02:33 -0700 (PDT) From: Jean-Christophe Roux Subject: Re: plpgsql returning array To: Marc McIntyre Cc: pgsql-php@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-571368672-1161050553=:85347" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.496 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200610/16 X-Sequence-Number: 3616 --0-571368672-1161050553=:85347 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Marc,=0AThanks for the tip. It does the job! This "return setof/next" is qu= ite new to me; I am going to spend some time to understand it and see how i= t can improve codes. =0A=0A----- Original Message ----=0AFrom: Marc McIntyr= e =0ATo: Jean-Christophe Roux =0ACc: = pgsql-php@postgresql.org=0ASent: Sunday, October 15, 2006 2:11:30 AM=0ASubj= ect: Re: [PHP] plpgsql returning array=0A=0A=0AYou should use a set returni= ng function. Your function can be rewritten =0Aas a set returning function = like so:=0A=0ACREATE OR REPLACE FUNCTION foo()=0A RETURNS SETOF text AS=0A= $BODY$=0Adeclare=0A a text;=0A b text;=0A arr text[];=0A i INTE= GER :=3D 0;=0Abegin=0A a =3D 'a';=0A b =3D 'b';=0A arr[0] =3D a;= =0A arr[1] =3D b;=0A =0A FOR i IN 0..array_upper(arr, 1) LOOP=0A = RETURN NEXT arr[i];=0A END LOOP;=0A=0Aend;=0A$BODY$=0A LANGUAGE 'p= lpgsql' VOLATILE;=0A=0A=0AAnd to call the function:=0A=0Amarc=3D# select * = from foo();=0Afoo=0A-----=0Aa=0Ab=0A(2 rows)=0A=0ANotice that to return a v= alue within the set you use the construct =0A"RETURN NEXT". Set returning f= unctions are generally faster than array =0Areturning functions, and are al= so more useful. For instance you can use =0Aset returning functions in sub = selects like so:=0A=0ASelect * from table where col in (select * from foo()= )=0A=0AYou can also use a where clause within the select statement ie.=0A= =0Amarc=3D# select * from foo() as x where x =3D 'a';=0Ax=0A---=0Aa=0A(1 ro= w)=0A=0ASo a set returning function is like a derived table.=0A=0AIn php, t= he result from a set returning function would be the same as =0Aquerying a = table.=0A=0AJean-Christophe Roux wrote:=0A> Hello,=0A> in Postgresql, I hav= e a function like this=0A>=0A> CREATE OR REPLACE FUNCTION foo()=0A> RETUR= NS text[] AS=0A> $BODY$=0A> declare=0A> a text;=0A> b text;=0A> = arr text[];=0A> begin=0A> a =3D 'a';=0A> b =3D 'b';=0A> arr[0]= =3D a;=0A> arr[1] =3D b;=0A> return arr;=0A> end;=0A> $BODY$=0A> = LANGUAGE 'plpgsql' VOLATILE;=0A>=0A> In PHP I would like to read my return= ed arr.=0A> $message =3D pg_fetch_result(pg_query("se= lect foo()"),0,0);=0A> print_r($message);=0A> ?>=0A> prints som= ething like:=0A> [0:1]=3D{a,b}=0A>=0A>=0A> I could parse that results, as a= string but I'd rather have a nice =0A> array. Any idea how I should change= the function and/or the php codes?=0A> Thanks in advance=0A> Jean=0A>=0A= =0A --0-571368672-1161050553=:85347 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
=0A
Marc,
=0A
Thanks for t= he tip. It does the job! This "return setof/next" is quite new to me; I am = going to spend some time to understand it and see how it can improve c= odes.
=0A
----- Original Message ----
From: Marc McIn= tyre <mmcintyre@squiz.net>
To: Jean-Christophe Roux <jcxxr@yaho= o.com>
Cc: pgsql-php@postgresql.org
Sent: Sunday, October 15, 2006= 2:11:30 AM
Subject: Re: [PHP] plpgsql returning array

=0A
Yo= u should use a set returning function. Your function can be rewritten
a= s a set returning function like so:

CREATE OR REPLACE FUNCTION foo()=
  RETURNS SETOF text AS
$BODY$
declare
  &= nbsp; a text;
    b text;
   =  arr text[];
    i INTEGER :=3D 0;
begin
=     a =3D 'a';
    b =3D 'b';    arr[0] =3D a;
    arr[1] = =3D b;
  
    FOR i IN 0..array_upper(= arr, 1) LOOP
        RETURN NEXT= arr[i];
    END LOOP;

end;
$BODY$
&nbs= p; LANGUAGE 'plpgsql' VOLATILE;


And to call the function:
marc=3D# select * from foo();
foo
-----
a
b
(2 rows)
Notice that to return a value within the set you use the construct "RETURN NEXT". Set returning functions are generally faster than array
returning function= s, and are also more useful. For instance you can use
set returning fun= ctions in sub selects like so:

Select * from table where col in (sel= ect * from foo())

You can also use a where clause within the select = statement ie.

marc=3D# select * from foo() as x where x =3D 'a';
= x
---
a
(1 row)

So a set returning function is like a deriv= ed table.

In php, the result from a set returning function would be = the same as
querying a table.

Jean-Christophe Roux wrote:
>= ; Hello,
> in Postgresql, I have a function like this
>
>= CREATE OR REPLACE FUNCTION foo()
>   RETURNS text[] AS
= > $BODY$
> declare
>     a text;
>=      b text;
>     arr text[]= ;
> begin
>     a =3D 'a';
> &nb= sp;   b =3D 'b';
>     arr[0] =3D a;
>  &nbs= p;  arr[1] =3D b;
>     return arr;
> = end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>= ;
> In PHP I would like to read my returned arr.
> <?php
= >            = ; $message =3D pg_fetch_result(pg_query("select foo()"),0,0);
> =             print_r(= $message);
> ?>
> prints something like:
> [0:1]=3D{a,= b}
>
>
> I could parse that results, as a string but I'd = rather have a nice
> array. Any idea how I should change the functio= n and/or the php codes?
> Thanks in advance
> Jean
>


--0-571368672-1161050553=:85347--