PostgreSQL 8.3 tem****ary tables & stored functions

Hello,
I started working on PostgreSQL 8.2 and I used some tem****ary tables to
pass 
data between stored functions. To avoid any issue, according to this post 
(http://archives.postgresql.org/pgsql-bugs/2007-07/msg00067.php)
I used
lots 
of EXECUTE statement to cause the compilation of every statement involving

tem****ary tables.

Now I'm performing a move of my code to PostgreSQL 8.3 in order to keep 
advantage of some new features such as "Automatically re-plan cached
queries 
when table definitions change or statistics are updated" 
(http://www.postgresql.org/docs/8.3/static/release-8-3.html).

So I rewrote my code and changed some functions removing the execute 
statement.

That way my expected result would have been:
 - having volatile functions the query plan shouldn't be cached
 - between different calls (with different temp tables) plpgsql should 
understand when I'm working on a different OID and should recompile the
query

But actually I'm experiencing some issues because some statements does not

work at all, in particular, I can read from tem****ary tables without any 
issue but my DELETEs fail silently.

I've reproduced the issue with the following code:

-- set up database and functions
--------------------------------------------
-- psql -p 5433
DROP DATABASE testtemp;
CREATE DATABASE testtemp;
-- createlang -p 5433 plpgsql testtemp
-- psql -p 5433 -d testtemp

DROP FUNCTION IF EXISTS testexecute(VARCHAR(255));
DROP FUNCTION IF EXISTS testdynamic(VARCHAR(255));

CREATE FUNCTION testexecute(str VARCHAR(255)) RETURNS VOID AS $$
DECLARE myoid BIGINT DEFAULT NULL;
BEGIN
	EXECUTE $body$ SELECT tableoid FROM pg_attribute WHERE attrelid 
= 'testtemptable'::regclass LIMIT 1;$body$ INTO myoid;
	RAISE NOTICE 'testexecute on %',myoid;
	EXECUTE 'DELETE FROM testtemptable WHERE name='|| quote_literal(str) ||
';';
	RETURN;
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

CREATE FUNCTION testdynamic(str VARCHAR(255)) RETURNS VOID AS $$
DECLARE myoid BIGINT DEFAULT NULL;
BEGIN
	SELECT tableoid INTO myoid FROM pg_attribute WHERE attrelid 
= 'testtemptable'::regclass LIMIT 1;
	RAISE NOTICE 'testdynamic on %',myoid;
	DELETE FROM testtemptable WHERE name=str;
	RETURN;
END;
$$ LANGUAGE PLPGSQL VOLATILE SECURITY INVOKER;

-- execute the following steps twice
----------------------------------------
CREATE TEM****ARY TABLE testtemptable(myid BIGINT PRIMARY KEY,name 
VARCHAR(255));
SELECT tableoid FROM pg_attribute WHERE attrelid =
'testtemptable'::regclass;
INSERT INTO testtemptable(myid,name) VALUES(1,'teststring1');
INSERT INTO testtemptable(myid,name) VALUES(2,'teststring2');
INSERT INTO testtemptable(myid,name) VALUES(3,'teststring3');
INSERT INTO testtemptable(myid,name) VALUES(4,'teststring4');
INSERT INTO testtemptable(myid,name) VALUES(5,'teststring5');
INSERT INTO testtemptable(myid,name) VALUES(6,'teststring6');
INSERT INTO testtemptable(myid,name) VALUES(7,'teststring7');
INSERT INTO testtemptable(myid,name) VALUES(8,'teststring8');
INSERT INTO testtemptable(myid,name) VALUES(9,'teststring9');

SELECT testexecute('teststring6');
SELECT testexecute('teststring8');
SELECT testdynamic('teststring2');
SELECT testdynamic('teststring4');
SELECT * FROM testtemptable;

DROP TABLE testtemptable;

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


As you can see testexecute work fine, testdynamic instead got null table 
reference, but it fails silently.

Is there something wrong on what I'm expecting from new PostgreSQL
version, or 
is there some misconfiguration?

Thanks,

Gabriele Messineo	

-- 
Sent via pgsql-novice mailing list (pgsql-novice@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice