calling a stored postgres function from php
I originally posted the question here: How do I fetch 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...
$q = pg_query("SELECT zp('zc',10,20,90)"); $f = pg_query("FETCH ALL FROM zc"); while($row = pg_fetch_array($f)) ...
while($row = pg_fetch_all($f)) ...
$q = pg_query("SELECT zp('zc',10,20,90);FETCH ALL FROM zc;") while($frow = pg_fetch_array($q)) ...
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?