From: | Adrian Klaver <aklaver(at)comcast(dot)net> |
---|---|
To: | Colin Wetherbee <cww(at)denterprises(dot)org> |
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 21:49:06 |
Message-ID: | 200803131449.06645.aklaver@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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...
> ^
>
> Colin
Try CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b) ;
--
Adrian Klaver
aklaver(at)comcast(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Colin Wetherbee | 2008-03-13 21:51:53 | Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP |
Previous Message | Colin Wetherbee | 2008-03-13 21:46:15 | Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP |