Re: TEMPORARY TABLE in a PL/pgSQL function

On Fri, 2005-10-07 at 13:11 +0200, Tjibbe wrote:
> CREATE OR REPLACE FUNCTION test_temp_table() RETURNS void AS $$
> BEGIN
> CREATE TABLE temp_table AS SELECT * FROM objects;
> PERFORM * FROM temp_table; -- without this line no problems
> DROP TABLE temp_table;
> RETURN;
> END
> $$ LANGUAGE plpgsql;
>
> SELECT test_temp_table();
> SELECT test_temp_table();
>
>
> Why can't I execute this function twice in the same database connection?
>
> I get the following error:
>
> ERROR: relation with OID 169873 does not exist
> CONTEXT: SQL statement "SELECT * FROM temp_table"
> PL/pgSQL function "test_temp_table" line 4 at perform

The second time around, the function is cached and tries to use the oid
from the first time. Since the table has been deleted and recreated,
the oid is different.

You need to do
EXECUTE 'SELECT * FROM temp_table';
which will re-evaluate the command each time it is run.

Oliver Elphick