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
