Re: [BUGS] BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice

#1

TODO has:

o Add ON COMMIT capability to CREATE TABLE AS ... SELECT

---------------------------------------------------------------------------

David Fetter wrote:

Quote:

On Thu, Nov 17, 2005 at 05:32:43PM -0500, Tom Lane wrote:
David Fetter <david (AT) fetter (DOT) org> writes:
On Thu, Nov 17, 2005 at 04:37:14PM -0500, Tom Lane wrote:
CREATE TEMP TABLE foo ... ON COMMIT DROP;

It's that first little elipsis mark that's the problem. Is there
something really clever I've been missing on how to do a dynamic
table creation?

Oh, you're worried about what to do if you don't know the output
column set of the query? OK, that is a bit harder, but I think it's
still a corner case. How much are you really going to get done with
the table if you don't know what columns it has?

My use case is when I have a system of audit tables that look like
this:

CREATE TABLE foo (
...
);

CREATE TABLE foo_audit (
foo_audit_id BIGSERIAL PRIMARY KEY,
foo_actor TEXT,
foo_timestamp TIMESTAMP,
foo_action char(1) CHECK foo_action IN('D','I','U'),
old_foo foo,
new_foo foo
);

with appropriate TRIGGERs, etc. to make that happen. It nice feature
of being partitionable via constraint exclusion.

This is in aid of a system for making it possible to ALTER foo while
preserving the data in foo_audit.

Cheers,
D
--
David Fetter david (AT) fetter (DOT) org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice - 11-22-2005 , 05:20 PM