Re: out of memory for query result

Allen Fair <allen(at)cyberdesk(dot)com> writes:

> 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.
>
> The following code does not work, but I'll keep trying! (I just added
> the declare phrase.)
> $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;

I think you need to DECLARE the cursor first, then for your loop do:

<loop>
FETCH 100 FROM csr;
<loop calling fetchrow_hashref() 100 times or until it returns undef>
<process the row>
</loop>
</loop>

You can execute FETCH once for each row, but it'll be faster to batch
it up as above.

Read up on DECLARE and FETCH in the SQL docs. I don't know of any
reason why you can't use them from Perl; it's just not done
automatically behind the scenes.

-Doug