Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: msergeant(at)startechgroup(dot)co(dot)uk, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #748: Temp tables don't work in functions
Date: 2002-08-27 14:20:08
Message-ID: 200208271420.g7REK8621120@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2002-08-27 14:42:03
Subject: Bug #749: one NULL function parameter makes all other NULL
Previous:From: pgsql-bugsDate: 2002-08-27 09:58:33
Subject: Bug #748: Temp tables don't work in functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group