Subject: TEMP tables
From: Lex Berezhny <LBerezhny@DevIS.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: 
Message-Id: <1044235551.2176.29.camel@buddha>
Mime-Version: 1.0
Date: 02 Feb 2003 20:25:51 -0500
Content-Transfer-Encoding: 7bit

hi,

I have a plpgsql procedure that needs to create a temporary table, use
it as a stack internally, and then disgard it when the procedure exits.

This works great if I only call this procedure ONCE per session. Calling
it a second time within the same session produces:

WARNING:  Error occurred while executing PL/pgSQL function render
WARNING:  line 8 at SQL statement
ERROR:  Relation 'stack' already exists

(line 8 is the CREATE TEMP TABLE statement)

But when I add DROP TABLE stack at the end (but within) the procedure,
while it works okay the first time around, it produces the following
error after first execution:

WARNING:  Error occurred while executing PL/pgSQL function render
WARNING:  line 9 at SQL statement
ERROR:  pg_class_aclcheck: relation 20900 not found

(line 9 is a SELECT * FROM stack statement)

It seems that after a table is created, dropped and then created again
it's not recognised.

When i do this outside of a procedure it works fine (i can create/drop a
temp table as many times as i want). Is there something magical that
procedures do when a table is created inside of them? (like cache the
relation id, and when you swap it underneath them, they panic?) Just
thoughts. Honestly, I have no clue :-) Which is why I am asking :-)

What are the recommendations or solutions on using temporary tables
inside functions on a per call basis?

thanks a lot,

 - lex

-- 
Lex Berezhny <LBerezhny@DevIS.com>