From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Lex Berezhny <LBerezhny@DevIS.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: TEMP tables
Date: Sun, 2 Feb 2003 20:58:26 -0500 (EST)
Message-ID: <200302030158.h131wQn11351@candle.pha.pa.us> (raw)
In-Reply-To: <1044235551.2176.29.camel@buddha>
You need to use EXECUTE. See the FAQ, item 4.26.
---------------------------------------------------------------------------
Lex Berezhny wrote:
> 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>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, LBerezhny@DevIS.com
Subject: Re: TEMP tables
In-Reply-To: <200302030158.h131wQn11351@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox