Re: plpgsql returning array

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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



[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux