Skip site navigation (1) Skip section navigation (2)

faq 4.20: pl/pgsql temporary tables create/drop

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <pgsql-docs(at)postgresql(dot)org>
Subject: faq 4.20: pl/pgsql temporary tables create/drop
Date: 2005-02-04 14:06:10
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A760F@Herge.rcsinc.local (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-docs
The PostgreSQL FAQ currently suggests using dynamic SQL as a workaround
for the table OID caching problem of temp tables in pg/pgsql functions.
While this is ok, it fails to suggest that besides the initial
create/drop statements, every statement that touches the table must also
be dynamic.

With 8.0 comes pl/pgsql exception handlers...in the beginning of
function execution one might do the following:
    begin   
        begin
            delete from temp_table; -- temp table
        exception
            when others then
                perform create temp temp_table [...]
        end;
As long as the table structure does not change between function
executions, this can be a more elegant approach to dealing with this
problem.  Pre 8.0, I would have suggested to initialize all temporary
tables in a special function, but this still requires special handling
code when the connection gets broken, etc.  I think it would be helpful
to erstwhile pl/pgsql developers to list this alternative method here.

Merlin

Responses

pgsql-docs by date

Next:From: Bruce MomjianDate: 2005-02-04 17:03:46
Subject: Re: faq 4.20: pl/pgsql temporary tables create/drop
Previous:From: Mark KirkwoodDate: 2005-02-04 01:18:35
Subject: Re: Instructions for Linux ipc config

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group