Re: TEMPORARY TABLE in a PL/pgSQL function

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
>
>
>
>