Thread: BUG #1064: work with temporary table in plpgsql function

BUG #1064: work with temporary table in plpgsql function

From
"PostgreSQL Bugs List"
Date:
The following bug has been logged online:

Bug reference:      1064
Logged by:          Sergey

Email address:      serg.z@list.ru

PostgreSQL version: 7.4

Operating system:   FreeBSD

Description:        work with temporary table in plpgsql function

Details:

If a followed function is called more once inside one session, then occured
this error:

ERROR:  relation with OID 19990 does not exist
CONTEXT:  PL/pgSQL function "test_temp" line 3 at SQL statement


TEST FUNCTION:

CREATE OR REPLACE FUNCTION public.test_temp()
  RETURNS int4 AS
'begin
  create temporary table itable(id int4, name varchar(100));
  insert into itable values(1, \'test\');
  drop table itable;

  return 0;
end;
'
  LANGUAGE 'plpgsql' VOLATILE;

Re: BUG #1064: work with temporary table in plpgsql function

From
Bruce Momjian
Date:
Yes, this is an FAQ.  Use EXECUTE.

---------------------------------------------------------------------------

PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference:      1064
> Logged by:          Sergey
>
> Email address:      serg.z@list.ru
>
> PostgreSQL version: 7.4
>
> Operating system:   FreeBSD
>
> Description:        work with temporary table in plpgsql function
>
> Details:
>
> If a followed function is called more once inside one session, then occured
> this error:
>
> ERROR:  relation with OID 19990 does not exist
> CONTEXT:  PL/pgSQL function "test_temp" line 3 at SQL statement
>
>
> TEST FUNCTION:
>
> CREATE OR REPLACE FUNCTION public.test_temp()
>   RETURNS int4 AS
> 'begin
>   create temporary table itable(id int4, name varchar(100));
>   insert into itable values(1, \'test\');
>   drop table itable;
>
>   return 0;
> end;
> '
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073