Re: use of temporary tables in functions

On Wed, 17 Apr 2002, Administrator wrote:

> Hi,
>
> While using a temporary table in a function, it seems that after
> dropping the table it stays in the functions cache.
>
> create function tmp_fn() returns integer as'
> begin
> create temporary table test(id integer primary key, x_val integer);
> insert into test (id, x_val) values(1,2);
> drop table test;
> return 1;
> end;'
> language 'plpgsql';
>
> The next time it comes back with a message "Relation 128863 does not
> exist". How can we avoid the oid of the table from being left in the
> cache?

When using temporary tables from functions, you'll currently need to use
EXECUTE to run all the queries on that table (in this case, all of your
queries) as a workaround.