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

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