Thread: About index for temporay table in a plpgsql function

Hello,

A question about index for temporary table.

Postgresql 8.0.1, in a pl/pgsql function:
begin
...
-- Part I
create temporal table t1 as ((select ... from .. left join ..... where
... ) union (select .. from ... left join ... where ...  ));
create index idx_t1_cols on t1(col1, col2) tablespace ts1;

...
-- Part II
insert into tableX
(
  select ...
  from tableY
  left join t1 using (col1, col2)
  where ... ...
)
...
end;

In the above example, the index for t1 can speed up the left join query
in part II (although, there is no analyze done after the creation of
index), right?

The temporary table will be dropped automatically at the end of the
function, right?
By the way, is there anythings need to pay especially attention about
temporary table under a function please?

Thanks a lot,
Ying

Emi Lu wrote:
>
>
> The temporary table will be dropped automatically at the end of the
> function, right?
>

A temp table will be dropped at the end of the connection. You can reuse
it by adding ON COMMIT DELETE ROWS and then check
if it exists in your function with this other function:
http://www.milwaukeesoft.com/forums/viewtopic.php?t=79

You can also use ON COMMIT in a couple of different ways:

ON COMMIT

    The behavior of temporary tables at the end of a transaction block
    can be controlled using ON COMMIT. The three options are:

    PRESERVE ROWS

        No special action is taken at the ends of transactions. This is
        the default behavior.

    DELETE ROWS

        All rows in the temporary table will be deleted at the end of
        each transaction block. Essentially, an automatic TRUNCATE
        <http://www.postgresql.org/docs/8.1/interactive/sql-truncate.html>
        is done at each commit.

    DROP

        The temporary table will be dropped at the end of the current
        transaction block.

Hello,

>> The temporary table will be dropped automatically at the end of the
>> function, right?
>>
>
> A temp table will be dropped at the end of the connection. You can
> reuse it by adding ON COMMIT DELETE ROWS and then check
> if it exists in your function with this other function:
> http://www.milwaukeesoft.com/forums/viewtopic.php?t=79
>
> You can also use ON COMMIT in a couple of different ways:
>
> ON COMMIT
>
>    The behavior of temporary tables at the end of a transaction block
>    can be controlled using ON COMMIT. The three options are:
>
>    PRESERVE ROWS
>
>        No special action is taken at the ends of transactions. This is
>        the default behavior.
>
>    DELETE ROWS
>
>        All rows in the temporary table will be deleted at the end of
>        each transaction block. Essentially, an automatic TRUNCATE
>        <http://www.postgresql.org/docs/8.1/interactive/sql-truncate.html>
>        is done at each commit.
>
>    DROP
>
>        The temporary table will be dropped at the end of the current
>        transaction block.


Since the structure of the temporay table is not predefined, in the
function, I use create temporay table AS ( (select ... from ... left
join ... where ...  union select ... from ... left join ... where ... )) .

Where I should put "on commit drop" to the create temporay table as
select ... query?  I tried at the end, but it failded.

Besides, how about setting index on a temporay table, it speeds up query
(although no anayze is run after the index generation on the temporay
table), right?

Thanks a lot,
Ying