On 1/19/06, marc mamin <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: > > Bug reference: 2185 > Logged by: marc mamin > Email address: [EMAIL PROTECTED] > 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 >
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