Re: About index for temporay table in a plpgsql function

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