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