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

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