Thread: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
David Gagnon
Date:
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; Thanks for your help David
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Craig Ringer
Date:
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
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
kevin@kevinkempterllc.com
Date:
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.....
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Adrian Klaver
Date:
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; > > > Thanks for your help > David I am missing something here. What is wrong with the above statement? -- Adrian Klaver aklaver@comcast.net
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Colin Wetherbee
Date:
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
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Adrian Klaver
Date:
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@comcast.net
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Colin Wetherbee
Date:
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
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Tom Lane
Date:
Colin Wetherbee <cww@denterprises.org> writes: > Adrian Klaver wrote: >> 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: Seems to be there in 8.2 and up. regards, tom lane
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Colin Wetherbee
Date:
Tom Lane wrote: > Colin Wetherbee <cww@denterprises.org> writes: >> Adrian Klaver wrote: >>> 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: > > Seems to be there in 8.2 and up. In 8.2: cww=# BEGIN; BEGIN cww=# CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b); SELECT cww=# COMMIT; COMMIT cww=# \d foo Did not find any relation named "foo". Handy! Colin
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Colin Wetherbee
Date:
Colin Wetherbee wrote: > Tom Lane wrote: >> Colin Wetherbee <cww@denterprises.org> writes: >>> Adrian Klaver wrote: >>>> 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: >> >> Seems to be there in 8.2 and up. > > In 8.2: > > cww=# BEGIN; > BEGIN > cww=# CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b); > SELECT > cww=# COMMIT; > COMMIT > cww=# \d foo > Did not find any relation named "foo". FWIW, the output of \h CREATE TABLE in both 8.1 and 8.2 suggest that this should be possible. Colin
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Tom Lane
Date:
Colin Wetherbee <cww@denterprises.org> writes: > Colin Wetherbee wrote: >> cww=# CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b); > FWIW, the output of \h CREATE TABLE in both 8.1 and 8.2 suggest that > this should be possible. "\h CREATE TABLE AS" is what describes this variant ... regards, tom lane
Re: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP
From
Colin Wetherbee
Date:
Tom Lane wrote: > Colin Wetherbee <cww@denterprises.org> writes: >> Colin Wetherbee wrote: >>> cww=# CREATE TEMP TABLE foo ON COMMIT DROP AS (SELECT 1 AS a, 2 AS b); > >> FWIW, the output of \h CREATE TABLE in both 8.1 and 8.2 suggest that >> this should be possible. > > "\h CREATE TABLE AS" is what describes this variant ... Ah, yes, of course. :) Colin