Cursors and PHP

[Steve Manes]

I found very little information on the web about how to return a cursor
from plpgsql to PHP using PEAR::DB. Here's what I managed to tack
together. It works but I want to make sure I'm not overlooking the
obvious or doing something boneheaded here, like setting myself up for a
database crash under load. Here's an example:

========================
PLPGSQL:

CREATE OR REPLACE FUNCTION get_user_data(REFCURSOR, INTEGER)
RETURNS REFCURSOR AS '

DECLARE
v_cursor ALIAS FOR $1;
v_user_id ALIAS FOR $2;

BEGIN

OPEN v_cursor FOR
SELECT *
FROM users
WHERE user_id = v_user_id;

RETURN v_cursor;

END;

' LANGUAGE 'plpgsql';

==========================
PHP:

$cursor = 'my_user';

$query = "SELECT get_user_data('$cursor', $user_id);FETCH ALL IN
$cursor;";

$res =& $dbh->query($query);

if (DB::isError($res)) {
... process the error
}

$row = $res->fetchRow(DB_FETCHMODE_ASSOC);

$dbh->query("CLOSE $cursor;");

$res->free();

return $row;
============================

Question #1: Is this the way to do it?

Question #2: I'm using a persistent database connection. To avoid
leaving an open cursor laying around I make another call to close the
cursor after the result set is fetched. Do I need to do this or will
the cursor be closed implicitly by Apache/PHP after the page is delivered?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Steve Manes <sm****@magpie.com> writes:

$query = "SELECT get_user_data('$cursor', $user_id);FETCH ALL IN
$cursor;"; $res =& $dbh->query($query);
Hm. This will return two different, not-compatible result sets (the
SELECT's output and then the FETCH's). I don't know how PHP is defined
to react to that. It seems that it's just discarding the first result
set and giving you the second, but is that specified behavior or is it
a bug that may get fixed someday? Might be best to do this in two
queries.
Question #2: I'm using a persistent database connection. To avoid
leaving an open cursor laying around I make another call to close the
cursor after the result set is fetched. Do I need to do this or will
the cursor be closed implicitly by Apache/PHP after the page is delivered?

The backend will implicitly close the cursor at transaction end. If the
connection-pooling code is set up to issue a COMMIT after the page is
processed, you need not close the cursor separately.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Steve Manes]

Tom Lane wrote:

Steve Manes <sm****@magpie.com> writes:

$query = "SELECT get_user_data('$cursor', $user_id);FETCH ALL IN
$cursor;";

$res =& $dbh->query($query);

Hm. This will return two different, not-compatible result sets (the
SELECT's output and then the FETCH's).

Without the FETCH in that compound PHP query however, nothing gets
returned from the SELECT. I'm not sure why but maybe it's because
PEAR::DB doesn't have any direct support for cursors so it's just
grabbing a result set.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[scott.marlowe]

I'm not sure why you're doing it the exact way you are, but you basically
just call the same commands within a pg_query as you would on the psql
command line to make it work:

begin
declare mycurs cursor as select * from table
fetch 10
rollback / commit


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org