Re: Bug #748: Temp tables don't work in functions

Matt Sergeant (msergeant(at)startechgroup(dot)co(dot)uk) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Temp tables don't work in functions

Long Description
When you use a temporary table in a function, the first time through it compiles the temporary table into an id for the table. Unfortunately it then tries to use the same id value on subsequent invocations, causing it to try and reference a non-existing relation on the second time through.

I hear this is a known bug, but I needed to get an idea if someone is working on this, or if it's already fixed in CVS, or if this is considered low importance or what.

If it is fixed in CVS, please mail me and I'll try it out.

Sample Code
mydb=> create or replace function foo() returns integer as '
mydb'> begin
mydb'> create temporary table fooo (bar integer not null);
mydb'> insert into fooo values ( 23 );
mydb'> drop table fooo;
mydb'> return 24;
mydb'> end;
mydb'> ' language 'plpgsql';
CREATE
mydb=> select foo();
foo
-----
24
(1 row)

mydb=> select foo();
NOTICE: Error occurred while executing PL/pgSQL function foo
NOTICE: line 3 at SQL statement
ERROR: Relation 1156252 does not exist
mydb=>

No file was uploaded with this report

The workaround is now in the FAQ, use pl/pgsql EXECUTE on any query
dealing with the temp table.

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

pgsql-bugs(at)postgresql(dot)org wrote:
> Matt Sergeant (msergeant(at)startechgroup(dot)co(dot)uk) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> Temp tables don't work in functions
>
> Long Description
> When you use a temporary table in a function, the first time through it compiles the temporary table into an id for the table. Unfortunately it then tries to use the same id value on subsequent invocations, causing it to try and reference a non-existing relation on the second time through.
>
> I hear this is a known bug, but I needed to get an idea if someone is working on this, or if it's already fixed in CVS, or if this is considered low importance or what.
>
> If it is fixed in CVS, please mail me and I'll try it out.
>
> Sample Code
> mydb=> create or replace function foo() returns integer as '
> mydb'> begin
> mydb'> create temporary table fooo (bar integer not null);
> mydb'> insert into fooo values ( 23 );
> mydb'> drop table fooo;
> mydb'> return 24;
> mydb'> end;
> mydb'> ' language 'plpgsql';
> CREATE
> mydb=> select foo();
> foo
> -----
> 24
> (1 row)
>
> mydb=> select foo();
> NOTICE: Error occurred while executing PL/pgSQL function foo
> NOTICE: line 3 at SQL statement
> ERROR: Relation 1156252 does not exist
> mydb=>
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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