--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 <mmcintyre [at] squiz.net>=0ATo: Jean-Christophe Roux <jcxxr [at] yahoo.com>=0ACc: =
pgsql-php [at] 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> <?php=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
<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:times new roman, new york, times, serif;=
font-size:12pt"><DIV style=3D"FONT-SIZE: 12pt; FONT-FAMILY: times new roman=
, new york, times, serif">=0A<DIV style=3D"FONT-SIZE: 12pt; FONT-FAMILY: ti=
mes new roman, new york, times, serif">Marc,</DIV>=0A<DIV style=3D"FONT-SIZ=
E: 12pt; FONT-FAMILY: times new roman, new york, times, serif">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. <BR></DIV>=0A<DIV style=3D"FONT-SIZE: 12pt; FONT-FAMILY: times new ro=
man, new york, times, serif">----- Original Message ----<BR>From: Marc McIn=
tyre <mmcintyre [at] squiz.net><BR>To: Jean-Christophe Roux <jcxxr [at] yaho=
o.com><BR>Cc: pgsql-php [at] postgresql.org<BR>Sent: Sunday, October 15, 2006=
2:11:30 AM<BR>Subject: Re: [PHP] plpgsql returning array<BR><BR>=0A<DIV>Yo=
u should use a set returning function. Your function can be rewritten <BR>a=
s a set returning function like so:<BR><BR>CREATE OR REPLACE FUNCTION foo()=
<BR> RETURNS SETOF text AS<BR>$BODY$<BR>declare<BR> &=
nbsp; a text;<BR> b text;<BR> =
arr text[];<BR> i INTEGER :=3D 0;<BR>begin<BR>=
a =3D 'a';<BR> b =3D 'b';<BR=
> arr[0] =3D a;<BR> arr[1] =
=3D b;<BR> <BR> FOR i IN 0..array_upper(=
arr, 1) LOOP<BR> RETURN NEXT=
arr[i];<BR> END LOOP;<BR><BR>end;<BR>$BODY$<BR>&nbs=
p; LANGUAGE 'plpgsql' VOLATILE;<BR><BR><BR>And to call the function:<B=
R><BR>marc=3D# select * from foo();<BR>foo<BR>-----<BR>a<BR>b<BR>(2 rows)<B=
R><BR>Notice that to return a value within the set you use the construct <B=
R>"RETURN NEXT". Set
returning functions are generally faster than array <BR>returning function=
s, and are also more useful. For instance you can use <BR>set returning fun=
ctions in sub selects like so:<BR><BR>Select * from table where col in (sel=
ect * from foo())<BR><BR>You can also use a where clause within the select =
statement ie.<BR><BR>marc=3D# select * from foo() as x where x =3D 'a';<BR>=
x<BR>---<BR>a<BR>(1 row)<BR><BR>So a set returning function is like a deriv=
ed table.<BR><BR>In php, the result from a set returning function would be =
the same as <BR>querying a table.<BR><BR>Jean-Christophe Roux wrote:<BR>>=
; Hello,<BR>> in Postgresql, I have a function like this<BR>><BR>>=
CREATE OR REPLACE FUNCTION foo()<BR>> RETURNS text[] AS<BR>=
> $BODY$<BR>> declare<BR>> a text;<BR>>=
b text;<BR>> arr text[]=
;<BR>> begin<BR>> a =3D 'a';<BR>> &nb=
sp; b =3D
'b';<BR>> arr[0] =3D a;<BR>> &nbs=
p; arr[1] =3D b;<BR>> return arr;<BR>> =
end;<BR>> $BODY$<BR>> LANGUAGE 'plpgsql' VOLATILE;<BR>>=
;<BR>> In PHP I would like to read my returned arr.<BR>> <?php<BR>=
> =
; $message =3D pg_fetch_result(pg_query("select foo()"),0,0);<BR>> =
print_r(=
$message);<BR>> ?><BR>> prints something like:<BR>> [0:1]=3D{a,=
b}<BR>><BR>><BR>> I could parse that results, as a string but I'd =
rather have a nice <BR>> array. Any idea how I should change the functio=
n and/or the php codes?<BR>> Thanks in advance<BR>> Jean<BR>></DIV=
></DIV><BR></DIV></div><br></body></html>
--0-571368672-1161050553=:85347--
