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
Views: Raw Message | Whole Thread | Download 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

Browse pgsql-docs by date

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