Re: BUG #2185: function compilation error with "Create [TEMP] table?

On 1/19/06, marc mamin <m(dot)mamin(at)gmx(dot)net> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2185
> Logged by: marc mamin
> Email address: m(dot)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
>

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 ;)