Re: DBD::Pg/perl question, kind of...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Okay, I don't have any postgresql tables big enough to verify this is
doing what I think it is (namely, only keeping one row from my result
set in memory at a time), and I still don't really know much about
cursors or pg, but this appears to be doing what I want to do:

$dbh->do('BEGIN WORK;');
$dbh->do('DECLARE my_cur CURSOR FOR SELECT * FROM my_table ORDER BY
account_id;');
my $sth = $dbh->prepare(qq{FETCH FORWARD 1 FROM my_cur});

$sth->execute;
while (my $href = $sth->fetchrow_hashref) {
     my $field1 = $href->{field1};
     my $account_id = $href->{account_id};

     ## do stuff

     $sth->execute;
}

$dbh->do("COMMIT WORK;");

really the only thing that doesn't look DBI-ish about it is calling
$sth->execute at the end of the while loop... like to fill up the
statement handler with the data to fetchrow_hashref on the next time
around.

comments?

On Mar 12, 2007, at 12:49 PM, A.M. wrote:

>
> On Mar 12, 2007, at 15:33 , Neal Clark wrote:
>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Thanks for all the replies everyone. Not really knowing what a
>> cursor is, I suppose I have some work to do. I can do the SELECT/
>> LIMIT/OFFSET approach but that seems like kind of a headache, esp.
>> when its hard to predict what # of rows will max out memory... I'd
>> have to keep that number pretty small, effectively making the same
>> exact query over and over, which sounds pretty slow.
>>
>> I'm not really using pgsql yet, so a lot of this is beyond me, I'm
>> just thinking ahead as I start to migrate from mysql...
>
> Don't use LIMIT/OFFSET. The cursor is exactly what you want. A
> cursor effectively caches the query results on the server side and
> feeds the results to the client on demand.
>
> Cheers,
> M
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (Darwin)

iD8DBQFF9bhGOUuHw4wCzDMRAsfsAKCt+mtj0ITygdzenTCEZSA/1UibHwCgqPVe
rKEOlx3dCWD50C2kQ7nzhRc=
=RUbR
-----END PGP SIGNATURE-----