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...
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)) ...
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?
精彩评论