Re: problem with creating/dropping tables and plpgsql ?

"\(::\) Bob Ippolito" <bob(at)redivi(dot)com> writes:
> semantic=# DROP table ttmptable;
> DROP
> semantic=# create temp table ttmptable(lookup_id int, rating int);
> CREATE
> semantic=# SELECT doEverythingTemp(20706,2507);
> ERROR: Relation 4348389 does not exist

Yeah, temp tables and plpgsql functions don't coexist very well yet.
(plpgsql tries to cache query plans, and at the moment there's no
mechanism to let it flush obsolete plans when a table is deleted.)

What you'll need to do is create a temp table that lasts for the whole
session and is re-used by each successive call of the plpgsql function.
You don't need to worry about dropping the temp table at session exit;
that's what temp tables are for, after all, to go away automatically.
So, just delete all its contents at entry or exit of the function,
and you can re-use it each time through.

regards, tom lane