Re: Temporary tables

Look at the syntax for CREATE TABLE AS

You can CREATE TEMP TABLE <tablename> AS SELECT ...

This temp table will be removed automagically

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of SHELTON,MICHAEL
(Non-HP-Boise,ex1)
Sent: Thursday, January 10, 2002 3:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Temporary tables


Hello,

I am attempting to optimize a number of queries that are dependant on a
first query (big join) that is expensive to run.  Instead of having the join
be executed for each subsequent query I would like to put the results of the
first join into a temp table.  I found syntax for creating a temp table in
the idocs (SELECT <foo> INTO <tmp_table> FROM <table>) and this works fine.
My problem is that the table isn't very "temporary".  Is there a way to have
the table drop automagically when I'm done with it.  I'm accessing it
through PHP 4.0 via apache on a 7.1.3 db.  I would really like it to drop
when I close the connection.

A 2nd question is what about concurrency?  Is there a way to hide this temp
table from other concurrent connections to the db (I thought I remember
reading an earlier post about this) so that you don't get name conflicts
with the temp table?

Thanks,

Mike Shelton

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)