Thread: use of temporary tables in functions

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?

Thanks,
Alfred

www.traveljadoo.com - we add magic to your travel

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.