The following bug has been logged online:

Bug reference: 2185
Logged by: marc mamin
Email address: m.mamin (AT) gmx (DOT) net
PostgreSQL version: 8.1
Operating system: DB Server: Linux Client: windows XP
Description: function compilation error with "Create [TEMP] table?
Details:

within a function, when I:
- use create temp table ,
- do anyting with this table
- drop that table,

The first call to that function works, but further calls fail. Rebuilding
the function before each call fix the issue.
I guess that the function is not yet compiled at the first call, and that
further calls use a compiled version....

Cheers, Marc

Here the steps to repeat the bug:
---------------------------------

CREATE OR REPLACE FUNCTION bugtest()
RETURNS int AS
$BODY$


BEGIN


create temp table bugt(i int);
insert into bugt values(1);
drop table bugt;


RETURN 0;


END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


select bugtest();
-->0
select bugtest();
-->ERROR: relation with OID 52284 does not exist
-->CONTEXT: SQL statement "insert into bugt values(1)"
-->PL/pgSQL function "bugtest" line 9 at SQL statement

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On 1/19/06, marc mamin <m.mamin (AT) gmx (DOT) net> wrote:
that is a known issue, do it this way

CREATE OR REPLACE FUNCTION bugtest() RETURNS int AS
$BODY$

BEGIN

execute 'create temp table bugt(i int)';
execute 'insert into bugt values(1)';
execute 'drop table bugt';

RETURN 0;


END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq