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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP

--=-1NorercR+shwkG4z93G3
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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?
> >
>=20
> 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.

--=-1NorercR+shwkG4z93G3
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/gAzo6DETLow6vwwRAk16AJ9hFpqf0WNOow9joG4atfa2RPz66QCe Mfi0
MxLNhTanPgpwRLAzzHM2a9o=
=a0e6
-----END PGP SIGNATURE-----

--=-1NorercR+shwkG4z93G3--

Re: 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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: How to manipulate a cursor returned by a PL/pgSQL in a PHP

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

This is the "head" of function:
--------------
create or replace function match(refcursor,int8) returns=20
refcursor as ' DECLARE
vid ALIAS FOR $2;
vcursor ALIAS FOR $1;
BEGIN
(...)
return vcursor;
(...)
--------------
=20
And this is php call:
--------------
$conn =3D pg_pconnect ($conn_string);
$qry =3D "BEGIN; SELECT=20
matche('cursorsalida',$identificador); fetch all in cursorsalida; ";
$result =3D pg_query ($conn, $qry);
$num =3D pg_num_rows($result);
for ($i=3D0; $i < $num; $i++) {
$r =3D 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 fr=
om=20
a stored procedure. There is no support for binding a PHP var to a cursor=
=20
like the Oracle functions offer. (OCINewCursor, OCIFreeCursor)

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

That's all I was saying,

cheers,

Andy



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org