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?
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? |