pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jean-Christophe Roux <jcxxr@yahoo.com>
To: Marc McIntyre <mmcintyre@squiz.net>
Cc: pgsql-php@postgresql.org
Subject: Re: plpgsql returning array
Date: Mon, 16 Oct 2006 19:02:33 -0700 (PDT)
Message-ID: <20061017020233.88472.qmail@web35311.mail.mud.yahoo.com> (raw)

Marc,
Thanks for the 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 codes. 

----- Original Message ----
From: Marc McIntyre <mmcintyre@squiz.net>
To: Jean-Christophe Roux <jcxxr@yahoo.com>
Cc: pgsql-php@postgresql.org
Sent: Sunday, October 15, 2006 2:11:30 AM
Subject: Re: [PHP] 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.
> <?php
>             $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
>



view thread (5+ messages)  latest in thread

Message-ID: <20061017020233.88472.qmail@web35311.mail.mud.yahoo.com>
Permalink:  ../20061017020233.88472.qmail@web35311.mail.mud.yahoo.com/
Also on:    postgresql.org/message-id/20061017020233.88472.qmail@web35311.mail.mud.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: jcxxr@yahoo.com, mmcintyre@squiz.net
  Subject: Re: plpgsql returning array
  In-Reply-To: <20061017020233.88472.qmail@web35311.mail.mud.yahoo.com>
  References: <20061017020233.88472.qmail@web35311.mail.mud.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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