Re: out of memory for query result

Allen Fair <allen@cyberdesk.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