create temporary table from cursor

Question

Is there any way, in PostgreSQL accessed from Python using SQLObject, to create a temporary table from the results of a cursor?

Previously, I had a query, and I created the temporary table directly from the query. I then had many other queries interacting w/ that temporary table.

Now I have much more data, so I want to only process 1000 rows at a time or so. However, I can't do CREATE TEMP TABLE ... AS ... from a cursor, not as far as I can see. Is the only thing to do something like:

rows = cur.fetchmany(1000);
cur2 = conn.cursor()
cur2.execute("""CREATE TEMP TABLE foobar (id INTEGER)""")
for row in rows:
    cur2.execute("""INSERT INTO foobar (%d)""" % row)

or is there a better way? This seems awfully inefficient.

Answer 1

I haven't used PostgreSQL but I know that to insert the results of a stored procedure you would do:

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

Taken from here.

So could you maybe do something similar. Maybe send it the cursor results as a whole so something like:

CREATE TEMP TABLE foobar (id INTEGER)
INSERT INTO foobar 'rows'

Answer 2

Well Postgres is reading the cursor record by record and you're just getting 1000 of those with the fetchmany call and loading them into memory. I'm not sure how you would really expect what you're asking to work.

A better performing version of that would make sure that all those INSERTS were wrapped in a single BEGIN and END so that its one transaction.

Is there a reason for the cursor as opposed to just adding a column via row_number() into a temporary table to start off with - so that its ordered?

Answer 3

I ended up doing this:

        sql.execute(connection, """
INSERT INTO blah VALUES %s;""" % (
    ", ".join("(%d)" % hid for hid in hids)))

instead of 1000 separate inserts. Still don't know a better way, but this works well enough.