On Jan 29, 2010, at 7:27 PM, Yannick Gingras wrote:
>
> Greetings, Alchemists,
> what's the best way to work with temp tables on Postgres?
>
> It's fairly easy to have one created:
>
> tmp_foo = Table('tmp_foo',
> metadata,
> Column('id', Integer, unique=True),
> Column('bar', Integer),
> prefixes=['TEMPORARY'])
> tmp_foo.create()
>
> The problem is that if I am not sure that the table was created, I
> can't use it. The following:
>
> tmp_foo.create(checkfirst=True)
>
> does not work. It issues the following SQL that won't find a match
> for temp tables:
>
> select relname from pg_class c
> join pg_namespace n on n.oid=c.relnamespace
> where n.nspname=current_schema() and lower(relname)=%(name)s
>
> One work around would be to use "ON COMMIT DROP" but I don't now how
> to do that since Table() has no `suffixes` parameter.
from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.compiler import compiles
@compiles(CreateTable)
def check_temporary(create, compiler, **kw):
table = create.element
ret = compiler.visit_create_table(create)
if 'TEMPORARY' in table._prefixes:
ret += "ON COMMIT DROP"
return ret
>
> While I'm at it, I might as well state the high level problem that
> pushed me to use temp tables. I'm using Xapian to do full text
> indexing. Xapian is good to give me a list of document ids that I can
> then retrieve from the database but if I want to apply additional
> criteria, I have to do the filtering on the database side. On way to
> do that is with a huge IN clause, the other is with a temp table. I
> like the temp table because I can also use it to order by Xapian
> ranking and do the paging on the alchemy side. I could also duplicate
> all the criteria on the Xapian side but I want to avoid that if
> possible.
I usually go with the IN clause but I wonder if its possible to write PG stored
procedures that can get to xapian as well (since you can write them in python
or any other language).
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
