Re: [svn:DBD-Pg] r9821 - in DBD-Pg/trunk: . t

On 8/7/07, Andrew Dunstan <[email protected]> wrote:
> Ben Tilly wrote:
> > -Although PostgreSQL has a cursor concept, it has not been used in the current
> > +Although PostgreSQL supports cursors, they have not been used in the current
> >  implementation. Cursors in PostgreSQL can only be used inside a transaction
> >  block. Because only one transaction block at a time is allowed, this would
> >  have implied the restriction not to use any nested C<SELECT> statements. Hence
[...]
> It is no longer true that cursors only work inside a single transaction.
> Postgres now has "WITH HOLD" cursors that persist to the end of a
> session if not closed. This has been true since 7.4, which is quite a
> long time, really. See docs on "DECLARE" for details.

Good point.  Here is a patch that takes that into account.

Cheers,
Ben

--- Pg.pm.bak   2007-08-07 09:34:33.000000000 -0700
+++ Pg.pm       2007-08-07 11:12:14.000000000 -0700
@@ -3401,13 +3401,32 @@

 =head2 Cursors

-Although PostgreSQL has a cursor concept, it has not been used in the current
-implementation. Cursors in PostgreSQL can only be used inside a transaction
-block. Because only one transaction block at a time is allowed, this would
-have implied the restriction not to use any nested C<SELECT> statements. Hence
-the C<execute> method fetches all data at once into data structures located in
-the front-end application. This approach must to be considered when selecting
-large amounts of data!
+Although PostgreSQL supports cursors, they have not been used in the current
+implementation. When DBD::Pg was defined, cursors in PostgreSQL could only be
+used inside a transaction block. Because only one transaction block at a time
+is allowed, this would have implied the restriction not to use any nested
+C<SELECT> statements. Therefore the C<execute> method fetches all data at
+once into data structures located in the front-end application. This fact
+must to be considered when selecting large amounts of data!
+
+You can use cursors in your application, but you'll need to do a little
+work.  First you must declare your cursor.  Now you can issue queries against
+the cursor, then select against your queries.  This typically results in a
+double loop, like this:
+
+  # Note: WITH HOLD is only available from PostgreSQL 7.4 on.  It is not
+  # needed if AutoCommit is off.
+  $dbh->do("DECLARE csr CURSOR WITH HOLD FOR $sql");
+  while (1) {
+    my $sth = $dbh->prepare("fetch 1000 from csr");
+    $sth->execute;
+    last if 0 == $sth->rows;
+
+    while (my $row = $sth->fetchrow_hashref) {
+      # Do something with the data.
+    }
+  }
+  $dbh->do("CLOSE csr");

 =head2 Datatype bool