pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
plpgsql returning array
5+ messages / 3 participants
[nested] [flat]

* plpgsql returning array
@ 2006-10-14 20:28  Jean-Christophe Roux <jcxxr@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jean-Christophe Roux @ 2006-10-14 20:28 UTC (permalink / raw)
  To: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql returning array
@ 2006-10-15 06:11  Marc McIntyre <mmcintyre@squiz.net>
  parent: Jean-Christophe Roux <jcxxr@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Marc McIntyre @ 2006-10-15 06:11 UTC (permalink / raw)
  To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: pgsql-php

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
>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql returning array
@ 2006-10-17 02:02  Jean-Christophe Roux <jcxxr@yahoo.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jean-Christophe Roux @ 2006-10-17 02:02 UTC (permalink / raw)
  To: Marc McIntyre <mmcintyre@squiz.net>; +Cc: pgsql-php

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
>



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql returning array
@ 2006-10-18 22:44  Jim C. Nasby <jim@nasby.net>
  parent: Marc McIntyre <mmcintyre@squiz.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Jim C. Nasby @ 2006-10-18 22:44 UTC (permalink / raw)
  To: Marc McIntyre <mmcintyre@squiz.net>; +Cc: Jean-Christophe Roux <jcxxr@yahoo.com>; pgsql-php

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)



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: plpgsql returning array
@ 2006-10-19 11:37  Marc McIntyre <mmcintyre@squiz.net>
  parent: Jim C. Nasby <jim@nasby.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Marc McIntyre @ 2006-10-19 11:37 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: Jean-Christophe Roux <jcxxr@yahoo.com>; pgsql-php

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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2006-10-19 11:37 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-14 20:28 plpgsql returning array Jean-Christophe Roux <jcxxr@yahoo.com>
2006-10-15 06:11 ` Marc McIntyre <mmcintyre@squiz.net>
2006-10-18 22:44   ` Jim C. Nasby <jim@nasby.net>
2006-10-19 11:37     ` Marc McIntyre <mmcintyre@squiz.net>
2006-10-17 02:02 Re: plpgsql returning array Jean-Christophe Roux <jcxxr@yahoo.com>

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