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

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