Thread: How to manipulate a cursor returned by a PL/pgSQL in a PHP script?

Hi.
My function returns a cursor and I don't know how to
fetch the cursor in my PHP script.
Anybody could help me?

=====
--
S�bastien BAUDRY - Database expert & Project manager
BAUDRY ENGINEERING BT. H-1025 Budapest Verhalom utca. 31/D. I/8.
@.: sebastien.baudry@baudry-engineering.com
mobile.: +36 30 392 0279 - tel: +36 1 326 0241 - fax: +36 1 326 0241
http://www.baudry-engineering.com

On Sunday 05 October 2003 01:32, Sebastien BAUDRY wrote:
> Hi.
> My function returns a cursor and I don't know how to
> fetch the cursor in my PHP script.
> Anybody could help me?
>

PHP does not directly support cursors under PostgreSQL.  :-(

From some quick looking on the web it seems you can hack your way around it in
PHP by using the ODBC functions (which do support cursors).

Are you porting an application from a different RDBMS (like Oracle) by any
chance?

Again from the web, I have found that one reason for the lack of cursor
support is that it is not as critical for performance (as it would be in
Oracle).   Maybe the use of LIMIT and OFFSET in postgres would help you.
(Those are not supported in Oracle ... correct me if I am wrong).

Andy

On Sun, 2003-10-05 at 04:38, Andrew J. Kopciuch wrote:
> On Sunday 05 October 2003 01:32, Sebastien BAUDRY wrote:
> > Hi.
> > My function returns a cursor and I don't know how to
> > fetch the cursor in my PHP script.
> > Anybody could help me?
> >
>
> PHP does not directly support cursors under PostgreSQL.  :-(

Huh? Cursors in PostgreSQL are accessible via the standard query
interface within PHP.

It's 2 queries, one to create the cursor, one to request the number of
tuples. PHP does not need any special logic to handle this.

pg_query('DECLARE thecursor CURSOR FOR ....')

pg_query('FETCH ...');

The fetch will act just like a select in regards to returned data.

On Sunday 05 October 2003 06:22, Rod Taylor wrote:
> On Sun, 2003-10-05 at 04:38, Andrew J. Kopciuch wrote:
> > On Sunday 05 October 2003 01:32, Sebastien BAUDRY wrote:
> > > Hi.
> > > My function returns a cursor and I don't know how to
> > > fetch the cursor in my PHP script.
> > > Anybody could help me?
> >
> > PHP does not directly support cursors under PostgreSQL.  :-(
>
> Huh? Cursors in PostgreSQL are accessible via the standard query
> interface within PHP.
>
> It's 2 queries, one to create the cursor, one to request the number of
> tuples. PHP does not need any special logic to handle this.
>
> pg_query('DECLARE thecursor CURSOR FOR ....')
>
> pg_query('FETCH ...');
>
> The fetch will act just like a select in regards to returned data.

Yes.  I should have elaborated a bit more.

I think this is in reference to his earlier post mentioning a REF CURSOR from
a stored procedure.  There is no support for binding a PHP var to a cursor
like the Oracle functions offer. (OCINewCursor, OCIFreeCursor)

You can always manage things directly with the query interface.  I think he
wants to manage things with the PHP interface.  And you can't with the
PostgreSQL functions provided.  AFAIK.

That's all I was saying,

cheers,

Andy

Hi.
I finally found out how to fetch the cursor returned by a PostGreSQL function in my PHP script.
Here from the Postgres archives: http://archives.postgresql.org/pgsql-php/2002-11/msg00054.php

This is the "head" of function:
 --------------
 create or replace function match(refcursor,int8) returns
 refcursor as ' DECLARE
    vid ALIAS FOR $2;
    vcursor ALIAS FOR $1;
 BEGIN
 (...)
 return vcursor;
 (...)
 --------------

 And this is php call:
 --------------
    $conn = pg_pconnect ($conn_string);
    $qry = "BEGIN; SELECT
 matche('cursorsalida',$identificador); fetch all in cursorsalida; ";
     $result = pg_query ($conn, $qry);
     $num = pg_num_rows($result);
     for ($i=0; $i < $num; $i++) {
       $r = pg_fetch_row($result, $i);
 (...)
 ---------------

You have to pass a refcursor as input parameter.

-----Message d'origine-----
De : Andrew J. Kopciuch [mailto:akopciuch@olympusproject.org]
Envoyé : Sunday, October 05, 2003 5:22 PM
À : Rod Taylor
Cc : sebastien.baudry@baudry-engineering.com; Sebastien BAUDRY;
pgsql-php@postgresql.org
Objet : Re: [PHP] How to manipulate a cursor returned by a PL/pgSQL in a
PHP


On Sunday 05 October 2003 06:22, Rod Taylor wrote:
> On Sun, 2003-10-05 at 04:38, Andrew J. Kopciuch wrote:
> > On Sunday 05 October 2003 01:32, Sebastien BAUDRY wrote:
> > > Hi.
> > > My function returns a cursor and I don't know how to
> > > fetch the cursor in my PHP script.
> > > Anybody could help me?
> >
> > PHP does not directly support cursors under PostgreSQL.  :-(
>
> Huh? Cursors in PostgreSQL are accessible via the standard query
> interface within PHP.
>
> It's 2 queries, one to create the cursor, one to request the number of
> tuples. PHP does not need any special logic to handle this.
>
> pg_query('DECLARE thecursor CURSOR FOR ....')
>
> pg_query('FETCH ...');
>
> The fetch will act just like a select in regards to returned data.

Yes.  I should have elaborated a bit more.

I think this is in reference to his earlier post mentioning a REF CURSOR from
a stored procedure.  There is no support for binding a PHP var to a cursor
like the Oracle functions offer. (OCINewCursor, OCIFreeCursor)

You can always manage things directly with the query interface.  I think he
wants to manage things with the PHP interface.  And you can't with the
PostgreSQL functions provided.  AFAIK.

That's all I was saying,

cheers,

Andy