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
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
+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 make sure that autocommit is off. Then declare your
+cursor. Now you are able to issue queries against the cursor, and select
+against your queries. This typically results in a double loop, like this:
+
+ $dbh->{AutoCommit} = 0;
+ $dbh->do("DECLARE csr CURSOR 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.
+ }
+ }
+
=head2 Datatype bool
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.
cheers
andrew
