Re: Temporary table in pl/pgsql

    OK, suppose in his function :
    - if it does not exist, he creates the temp table, with ON COMMIT DELETE
ROWS
    - if it does exists, he truncates it just to be sure

    So the next execution of the function will find the temp table, it will
have the same OID, all is well.

    Now :

BEGIN
execute the function (and creates the table)
ROLLBACK
execute the function

    The Rollback will have rolled back the table creation too. Now when he
executes the function again, will it get the cached plan with the rolled
back table's OID ?


On Fri, 13 Apr 2007 22:55:49 +0200, Merlin Moncure <mmoncure@gmail.com>
wrote:

> On 4/13/07, Raymond O'Donnell <rod@iol.ie> wrote:
>> Hello again all,
>>
>> I'm using a temporary table in a pl/PgSQL function, and I've read the
>> bit in the FAQ about using EXECUTE to force the table-creation query to
>> be re-evaluated each time the function is called.
>>
>> However, it doesn't seem to work for me. The first time I call the
>> function, all is well; the second (and every subsequent) time, the
>> function bombs with the 'relation with OID XXXXX does not exist' error -
>> this seems to imply that the EXECUTE statement is getting cached along
>> with the rest of the function, which according to the docs ain't
>> supposed to happen :-(
>
> in addition to the 'create table' stmt, all queries that touch the
> table must also be dynamic.  postgresql 8.3 will have improved plan
> invalidation which will (aiui) remove this requirement.
>
> merlin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings