Обсуждение: Is there a way to elegantly do a : CREATE TEMP TABLE X AS SELECT .... ON COMMIT DROP

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

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

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

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

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

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

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

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

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

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

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

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