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 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...
                                                          ^

Colin