plpgsql returning array

--0-455368344-1160857721=:15808
Content-Type: text/plain; charset=ascii
Content-Transfer-Encoding: quoted-printable

Hello,=0Ain Postgresql, I have a function like this CREATE OR REPLACE =
FUNCTION foo()=0A RETURNS text[] AS=0A$BODY$=0Adeclare=0A a text;=0A =
b text;=0A arr text[];=0Abegin=0A a =3D 'a';=0A b =3D 'b';=0A =
arr[0] =3D a;=0A arr[1] =3D b;=0A return arr;=0Aend;=0A$BODY$=0A LAN=
GUAGE 'plpgsql' VOLATILE; In PHP I would like to read my returned arr.=
=0A ),0,0);=0A print_r($message);=0A?>=0Aprints something like:=0A[0=
:1]=3D{a,b} =0AI could parse that results, as a string but I'd rather =
have a nice array. Any idea how I should change the function and/or the php=
codes? =0AThanks in advance=0AJean
--0-455368344-1160857721=:15808
Content-Type: text/html; charset=ascii
Content-Transfer-Encoding: quoted-printable

ad>

Re: plpgsql returning array

You should use a set returning function. Your function can be rewritten
as a set returning function like so:

CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF text AS
$BODY$
declare
a text;
b text;
arr text[];
i INTEGER := 0;
begin
a = 'a';
b = 'b';
arr[0] = a;
arr[1] = b;

FOR i IN 0..array_upper(arr, 1) LOOP
RETURN NEXT arr[i];
END LOOP;

end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


And to call the function:

marc=# 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 functions, and are also more useful. For instance you can use
set returning functions in sub selects like so:

Select * from table where col in (select * from foo())

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

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

So a set returning function is like a derived 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 = 'a';
> b = 'b';
> arr[0] = a;
> arr[1] = b;
> return arr;
> end;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> In PHP I would like to read my returned arr.
> > $message = pg_fetch_result(pg_query("select foo()"),0,0);
> print_r($message);
> ?>
> prints something like:
> [0:1]={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 function and/or the php codes?
> Thanks in advance
> Jean
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: plpgsql returning array

--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. ----- 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 =0AYou should use a set returni=
ng function. Your function can be rewritten =0Aas a set returning function =
like so: CREATE 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; end;=0A$BODY$=0A LANGUAGE 'p=
lpgsql' VOLATILE; =0AAnd to call the function: marc=3D# select * =
from foo();=0Afoo=0A-----=0Aa=0Ab=0A(2 rows) Notice 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: Select * from table where col in (select * from foo()=
) You 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) So a set returning function is like a derived table. In php, t=
he result from a set returning function would be the same as =0Aquerying a =
table. Jean-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

ad>

Re: plpgsql returning array

On Sun, Oct 15, 2006 at 04:11:30PM +1000, Marc McIntyre wrote:
> You should use a set returning function. Your function can be rewritten
> as a set returning function like so:

Isn't there a way to return an array? SRFs are great, but not always the
best tool.

BTW, neither function should be marked as VOLATILE... they're actually
IMMUTABLE as written.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: plpgsql returning array

Jim C. Nasby wrote:
> Isn't there a way to return an array? SRFs are great, but not always the
> best tool.
>
>
There's no way to nicely map an array from a plpgsql SF to a php array.
(Ie. the driver won't do that for you, AFAIK)
I find that array returning functions are useful when calling from other
functions, and set returning functions
are great when you wish to get a result set back to php.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq