calling a stored postgres function from php

Question

I originally posted the question here: http://stackoverflow.com/questions/2536727/calling-a-stored-postgres-function-from-php

I accepted the answer, but, it actually isn't working for me... Here's the original question:

I have a function in postgres, and when I'm at the pg prompt, I just do:

SELECT zp('zc',10,20,90);
FETCH ALL FROM zc;

I'm wondering how to do this from php?

I thought I could just do:

$q = pg_query("SELECT zp('zc',10,20,90)");

But, how do I "fetch" from that query?

I'm still not entirely certain how to do this...

I tried

$q = pg_query("SELECT zp('zc',10,20,90)");
$f = pg_query("FETCH ALL FROM zc");
while($row = pg_fetch_array($f)) ... 

and

while($row = pg_fetch_all($f)) ...

and

$q = pg_query("SELECT zp('zc',10,20,90);FETCH ALL FROM zc;")
while($frow = pg_fetch_array($q)) ... 

Answer 1

This works:

Create a function that returns a CURSOR:

CREATE FUNCTION myfunc(refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM tbl_name;
    RETURN NEXT $1;
END;
$$ LANGUAGE plpgsql;

And than the PHP-code:

<?php
ini_set('display_errors', true);
error_reporting(E_ALL);
$con = pg_connect('host=localhost port=5432 user=**** password=**** dbname=****');

pg_query($con, "BEGIN;");
pg_query($con, "SELECT * FROM myfunc('a');");

$result = pg_query($con, "FETCH ALL FROM a;");

echo '<pre>';
print_r(pg_fetch_all($result));
echo '</pre>';

pg_query($con, "COMMIT;");
?>

If you have any problems, what problems do you have? Any errors?