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

Re: TEMPORARY TABLE in a PL/pgSQL function

From: "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br>
To: olly(at)lfix(dot)co(dot)uk
Cc: Tjibbe <tjibbe(at)hotmail(dot)com>,PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: TEMPORARY TABLE in a PL/pgSQL function
Date: 2005-12-14 19:05:08
Message-ID: 43A06CE4.1080302@planit.com.br (view raw or flat)
Thread:
Lists: pgsql-novice
Hi, I have a similar case
but instead of the statement

PERFORM * FROM temp_table;  -- without this line no problems

I have a SELECT INTO inside the plpgsql function

SELECT * INTO rec FROM temp_table;

That return the same error as Tjibbe got.
I try to use something like

EXEC 'SELECT * INTO rec FROM temp_table';

But I now postgres (8.1.1) returns a error

EXEC of SELECT ... INTO is not implemented yet.

There are another aproach to make the expression re-evalueted ?

Thank's in advance

Oliver Elphick wrote:

>On Fri, 2005-10-07 at 13:11 +0200, Tjibbe wrote:
>  
>
>>CREATE OR REPLACE FUNCTION test_temp_table() RETURNS void AS $$
>>BEGIN
>>CREATE TABLE temp_table AS SELECT * FROM objects;
>>PERFORM * FROM temp_table;  -- without this line no problems
>>DROP TABLE temp_table;
>>RETURN;
>>END
>>$$ LANGUAGE plpgsql;
>>
>>SELECT test_temp_table();
>>SELECT test_temp_table(); 
>>
>>
>>Why can't I execute this function twice in the same database connection?
>>
>>I get the following error:
>>
>>ERROR:  relation with OID 169873 does not exist
>>CONTEXT:  SQL statement "SELECT  * FROM temp_table"
>>PL/pgSQL function "test_temp_table" line 4 at perform
>>    
>>
>
>The second time around, the function is cached and tries to use the oid
>from the first time.  Since the table has been deleted and recreated,
>the oid is different.
>
>You need to do
>   EXECUTE 'SELECT * FROM temp_table';
>which will re-evaluate the command each time it is run.
>
>Oliver Elphick
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>  
>

In response to

Responses

pgsql-novice by date

Next:From: Charlie BrightDate: 2005-12-14 19:10:43
Subject: Help please
Previous:From: Jaime CasanovaDate: 2005-12-14 14:26:47
Subject: Re: Sequence on a char(6) column

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