-----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-----
