Re: Temp Table Within PLPGSQL Function - Something Awry

On Tuesday 16 January 2007 10:10, "Lenorovitz, Joel"
<Joel.Lenorovitz@usap.gov> wrote:
> Greetings,
>
> I am trying to work with a TEMP TABLE within a plpgsql function and I
> was wondering if anyone can explain why the function below, which is
> fine syntactically, will work as expected the first time it is called,
> but will err out as shown on subsequent calls.

The query plans for all the references to the table get cached the first
time the function is run in a session.  These cached plans include the
table's oid.  This oid is not the same after you drop and recreate the
table, unfortunately, and the cached plans are not invalidated.

You can fix this a few ways.

Use EXECUTE QUERY for all queries that reference the table.

Or ...

Don't drop the table at the end of the function.  Use something like this at
the beginning instead:

BEGIN
    TRUNCATE temp_table;
EXCEPTION
    WHEN undefined_table THEN
         CREATE TEMP TABLE temp_table (field type, ...);
END;

OTHER code;

This will work better for you, although the table will continue to exist
between calls in the same session.

--
"A government that robs Peter to pay Paul can always depend upon the support
of Paul." - George Bernard Shaw