Search Postgresql Archives

Re: About index for temporay table in a plpgsql function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux