Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP - Mailing list pgsql-bugs

On Thu, Nov 17, 2005 at 05:32:43PM -0500, Tom Lane wrote:
> David Fetter <david@fetter.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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!