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

Adrian Klaver wrote:
> On Thursday 13 March 2008 2:46 pm, Colin Wetherbee wrote:
>> Adrian Klaver wrote:
>>> On Thursday 13 March 2008 4:49 am, David Gagnon wrote:
>>>> Hi all,
>>>>
>>>>   I think the title says everything:-)
>>>>
>>>>   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.
>>>>
>>>> CREATE TEMP TABLE _T_CR1 AS
>>>> SELECT CRNUM, CRYPNUM, CRMONT, CRDATE, GLNUM, CRRRNUM, CRACNUM, GLDESC
>>>> FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND CR.CRYPNUM =
>>>> CS.CSYPNUM INNER JOIN GL ON CS.CSGLNUM = GL.GLNUM AND 1 = GL.GLSOCTRL
>>>> WHERE CRYPNUM = companyId
>>>>   AND DATEDIFF(DY, CRDATE, GETDATE()) >= _AGELIMITE
>>>> ON COMMIT DROP;
>>> I am missing something here. What is wrong with the above statement?
>> You're missing:
>>
>> cww=# BEGIN;
>> BEGIN
>> cww=# CREATE TEMP TABLE foo AS (SELECT 1 AS a, 2 AS b) ON COMMIT DROP;
>> ERROR:  syntax error at or near "ON" at character 50
>> LINE 1: CREATE TEMP TABLE foo AS (SELECT 1 AS a, 2 AS b) ON COMMIT D...
>>                                                           ^
>
> Try CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b) ;

I don't know what version the OP is using, but that doesn't work on 8.1:

cww=# CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b);
ERROR:  syntax error at or near "ON" at character 23
LINE 1: CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS...
                               ^

I'm jumping in here because temporary ON COMMIT DROP tables created with
AS (SELECT ...) would be handy for me, too. :)

Colin