pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to manipulate a cursor returned by a PL/pgSQL function in a PHP script?
6+ messages / 5 participants
[nested] [flat]

* How to manipulate a cursor returned by a PL/pgSQL function in a PHP script?
@ 2003-10-05 07:29 Sebastien Baudry <sebastien.baudry@baudry-engineering.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Sebastien Baudry @ 2003-10-05 07:29 UTC (permalink / raw)
  To: pgsql-php

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* How to manipulate a cursor returned by a PL/pgSQL in a PHP script?
@ 2003-10-05 07:32 Sebastien BAUDRY <sbaudry@yahoo.com>
  2003-10-05 08:38 ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Andrew J. Kopciuch <akopciuch@olympusproject.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Sebastien BAUDRY @ 2003-10-05 07:32 UTC (permalink / raw)
  To: pgsql-php

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script?
  2003-10-05 07:32 How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Sebastien BAUDRY <sbaudry@yahoo.com>
@ 2003-10-05 08:38 ` Andrew J. Kopciuch <akopciuch@olympusproject.org>
  2003-10-05 12:22   ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Rod Taylor <rbt@rbt.ca>
  0 siblings, 1 reply; 6+ messages in thread

From: Andrew J. Kopciuch @ 2003-10-05 08:38 UTC (permalink / raw)
  To: sebastien.baudry@baudry-engineering.com, Sebastien BAUDRY <sbaudry@yahoo.com>; pgsql-php

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP
  2003-10-05 07:32 How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Sebastien BAUDRY <sbaudry@yahoo.com>
  2003-10-05 08:38 ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Andrew J. Kopciuch <akopciuch@olympusproject.org>
@ 2003-10-05 12:22   ` Rod Taylor <rbt@rbt.ca>
  2003-10-05 15:21     ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Andrew J. Kopciuch <akopciuch@olympusproject.org>
  0 siblings, 1 reply; 6+ messages in thread

From: Rod Taylor @ 2003-10-05 12:22 UTC (permalink / raw)
  To: akopciuch@olympusproject.org; +Cc: sebastien.baudry@baudry-engineering.com, Sebastien BAUDRY <sbaudry@yahoo.com>; pgsql-php

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.

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP
  2003-10-05 07:32 How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Sebastien BAUDRY <sbaudry@yahoo.com>
  2003-10-05 08:38 ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Andrew J. Kopciuch <akopciuch@olympusproject.org>
  2003-10-05 12:22   ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Rod Taylor <rbt@rbt.ca>
@ 2003-10-05 15:21     ` Andrew J. Kopciuch <akopciuch@olympusproject.org>
  2003-10-06 08:17       ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Sebastien Baudry <sbaudry@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Andrew J. Kopciuch @ 2003-10-05 15:21 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: sebastien.baudry@baudry-engineering.com, Sebastien BAUDRY <sbaudry@yahoo.com>; pgsql-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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP
  2003-10-05 07:32 How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Sebastien BAUDRY <sbaudry@yahoo.com>
  2003-10-05 08:38 ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Andrew J. Kopciuch <akopciuch@olympusproject.org>
  2003-10-05 12:22   ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Rod Taylor <rbt@rbt.ca>
  2003-10-05 15:21     ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Andrew J. Kopciuch <akopciuch@olympusproject.org>
@ 2003-10-06 08:17       ` Sebastien Baudry <sbaudry@yahoo.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Sebastien Baudry @ 2003-10-06 08:17 UTC (permalink / raw)
  To: pgsql-php

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-10-06 08:17 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-05 07:29 How to manipulate a cursor returned by a PL/pgSQL function in a PHP script? Sebastien Baudry <sebastien.baudry@baudry-engineering.com>
2003-10-05 07:32 How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Sebastien BAUDRY <sbaudry@yahoo.com>
2003-10-05 08:38 ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP script? Andrew J. Kopciuch <akopciuch@olympusproject.org>
2003-10-05 12:22   ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Rod Taylor <rbt@rbt.ca>
2003-10-05 15:21     ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Andrew J. Kopciuch <akopciuch@olympusproject.org>
2003-10-06 08:17       ` Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP Sebastien Baudry <sbaudry@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox