TEMPORARY TABLE in a PL/pgSQL function

From: "Tjibbe" <tjibbe(at)hotmail(dot)com>
To:
Cc: "PostGreSQL" <pgsql-novice(at)postgresql(dot)org>
Subject: TEMPORARY TABLE in a PL/pgSQL function
Date: 2005-10-07 11:11:02
Message-ID: BAY13-DAV1CBB8F6F34CAEB793067FDD840@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Oliver Elphick 2005-10-07 11:58:20 Re: TEMPORARY TABLE in a PL/pgSQL function
Previous Message Michael Fuhr 2005-10-07 07:56:39 Re: Currval function won't work