Thread: Cursors and PHP

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?

Steve Manes <smanes@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

Tom Lane wrote:

> Steve Manes <smanes@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.

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