Cursors and PHP

From: Steve Manes <smanes(at)magpie(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Cursors and PHP
Date: 2004-04-17 15:18:52
Message-ID: 40814ADC.6030503@magpie.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2004-04-17 15:42:20 Re: build contrib
Previous Message Jan Wieck 2004-04-17 13:46:12 Re: [HACKERS] Remove MySQL Tools from Source?