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