Re: out of memory for query result

On Sat, Oct 22, 2005 at 06:15:59PM -0400, Allen Fair wrote:
> From my googling, it seems the Perl DBD driver for Postgres does *not*
> support the cursor (see below). I hope someone can refute this!
>
> I am otherwise looking for code to implement Postgres cursors in Perl. I
> can not find the "DECLARE CURSOR" defined in the Perl DBI documentation
> either. Thanks Martijn for your reply, it helped me dig deeper.

Well, DBI doesn't support doing the cursor bit for you. But you can use
cursors just fine. Your code is almost there:

> $dbh = DBI->connect("DBI:Pg:dbname=$dbName;host=$host",
> $dbUser, $dbPassword,
> { RaiseError => 0, AutoCommit => 0, PrintError => 1 });
> $sth = $dbh->prepare("declare csr cursor for $sqlstatement");
> $sth->execute(@statement_parms) or die $DBI::errstr;

for(;;)
{
$sth = $dbh->prepare("fetch 1000 from csr");
$sth->execute();
last if( $sth->rows == 0 );

> while (my $hr = $sth->fetchrow_hashref) {
> # do something wonderful
> }
> $sth->finish();

}

> Is this a Perl only restriction? How about Python or Ruby?

It's not a restriction. No other language does it automatically either.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.