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 <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
