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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Charlie Bright | 2005-12-14 19:10:43 | Help please |
Previous Message | Jaime Casanova | 2005-12-14 14:26:47 | Re: Sequence on a char(6) column |