From: Peter T. Brown <peter@memeticsystems.com>
To: 'SHELTON,MICHAEL \(Non-HP-Boise,ex1\)' <michael_shelton@non.hp.com>
To: pgsql-sql@postgresql.org
Subject: Re: Temporary tables
Date: Fri, 11 Jan 2002 09:17:48 -0800
Message-ID: <002101c19ac3$e4d50330$7d00000a@PETER> (raw)
In-Reply-To: <FB60DFB2C0E24449AC0C21F743B935410150F91F@xboi02.boi.hp.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter@memeticsystems.com, michael_shelton@non.hp.com
Subject: Re: Temporary tables
In-Reply-To: <002101c19ac3$e4d50330$7d00000a@PETER>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox