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 $$
>>CREATE TABLE temp_table AS SELECT * FROM objects;
>>PERFORM * FROM temp_table; -- without this line no problems
>>DROP TABLE temp_table;
>>$$ LANGUAGE plpgsql;
>>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.
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
In response to
pgsql-novice by date
|Next:||From: Charlie Bright||Date: 2005-12-14 19:10:43|
|Subject: Help please|
|Previous:||From: Jaime Casanova||Date: 2005-12-14 14:26:47|
|Subject: Re: Sequence on a char(6) column|