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

Quoting Craig Ringer <craig@postnewspapers.com.au>:

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Create temp table xyx_tab (
col1    char(10),
col2    integer
);

insert into xyz_tab.....