Re: looping over the rows in a table

On Nov 9, 2007 6:12 PM, Rajarshi Guha <rguha@indiana.edu> wrote:
> Hi, this is slightly offtopic, but is based on Postgres:
>
> I have a table with 10M rows and I have a Python script using psycopg
> that needs to look at each row of the table. My current strategy is
> to do in the Python script
>
> cursor.execute("select acol from atable")
> while True:
>     ret = cursor.fetchone()
>     if not ret: break
>
> However if I understand correctly Postgres will basically try and
> return *all* the rows of the table as the result set, thus taking a
> long time and probably running out of memory.
>
> Is there a way I can modify the SQL or do something on the Postgres
> side, so that I can loop over all the rows in the table?

Assuming you can't do the work you need in SQL or a stored procedure
or something, yes.

Look up Declare Cursor.  I think 8.3 introduces updateable cursors.
don't know if you need that or not with what you're doing.