Re: About index for temporay table in a plpgsql function - Mailing list pgsql-general

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.