Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: David Gagnon <dgagnon(at)siunik(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
Date: 2008-03-13 18:14:21
Message-ID: 47D96EFD.6000502@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

David Gagnon wrote:

> I just what a way to create a TEMP for the current transaction only.
> If possible I don't want to create the TEMP table first, specify all
> column types, etc.

Well, you can get half way there with SELECT ... INTO TEMPORARY TABLE
tablename, eg:

SELECT 1 AS n, TEXT 'Fred' AS name INTO TEMPORARY TABLE sometable ;

or

SELECT group_id, count(member_id) AS member_count FROM group_membership
INTO TEMPORARY TABLE group_membership_counts;
GROUP BY group_id;

etc.

This still won't drop on commit, though it will drop on disconnect. You
can manually drop it earlier. Different connections see different temp
tables so there's no naming conflict.

I'd personally like an on commit drop option for temp tables, but I can
imagine a variety of reasons why it might not be done that way.

--
Craig Ringer

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vivek Khera 2008-03-13 18:18:43 Re: Recomendations on raid controllers raid 1+0
Previous Message Clodoaldo 2008-03-13 18:10:31 Re: Reindex does not finish 8.2.6