Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: David Fetter <david(at)fetter(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jaime Casanova <systemguards(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice
Date: 2005-11-22 23:20:37
Message-ID: 200511222320.jAMNKb102634@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
TODO has:

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

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

David Fetter wrote:
> 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(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-bugs by date

Next:From: David FetterDate: 2005-11-22 23:34:53
Subject: Re: BUG #2051: CREATE TEMP TABLE AS SELECT doesn't play nice with ON COMMIT DROP
Previous:From: apcoDate: 2005-11-22 20:38:06
Subject: BUG #2066: installer error: code 2755.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group