BUG #3450: Multiple Stored procedure calls cause issue with temp tables...

The following bug has been logged online:

Bug reference: 3450
Logged by: Chris Bowlby
Email address: excalibur(at)accesswave(dot)ca
PostgreSQL version: 8.0.11
Operating system: SUSE Linux Enterprise Server 9 SP2
Description: Multiple Stored procedure calls cause issue with temp
tables...
Details:

Using a temporary table of the same name in repeated calls to a stored
procedure are causing OID failure issues, it can be re-created using the
following useless example:

----------------------------------------
CREATE TABLE test1
( id SERIAL,
name TEXT,

PRIMARY KEY(id));

INSERT INTO test1(name) VALUES('Marc');
INSERT INTO test1(name) VALUES('Chris');
INSERT INTO test1(name) VALUES('Bob');
INSERT INTO test1(name) VALUES('Robert');
INSERT INTO test1(name) VALUES('Sally');
INSERT INTO test1(name) VALUES('Paul');

CREATE OR REPLACE FUNCTION get_name_id(text) RETURNS INTEGER AS '
DECLARE
search_name ALIAS FOR $1;
return_id RECORD;
tbl_oid RECORD;

BEGIN

CREATE TEMPORARY TABLE t_name AS SELECT id, name FROM test1 WHERE name =
search_name;
SELECT INTO tbl_oid OID FROM pg_class WHERE relname = ''t_name'';
CREATE TEMPORARY TABLE t_id AS SELECT id FROM t_name;

SELECT INTO return_id id FROM t_id;

DROP TABLE t_id;
DROP TABLE t_name;

return return_id.id;
END;
' LANGUAGE plpgsql;

select get_name_id('Marc');
select get_name_id('Marc');

-----------------------------------------

The error is as follows:

test=# select get_name_id('Marc');
get_name_id
-------------
1
(1 row)

test=# select get_name_id('Marc');
ERROR: relation with OID 74623 does not exist
CONTEXT: SQL statement "CREATE TEMPORARY TABLE t_id AS SELECT id FROM
t_name"
PL/pgSQL function "get_name_id" line 10 at SQL statement