From: Richard Huxton <dev@archonet.com>
To: Vijay Kumar <vijay@kinera.com>
To: pgsql-sql@postgresql.org
Subject: Re: Problem with temporary table -- Urgent
Date: Sat, 12 Jul 2003 09:45:35 +0100
Message-ID: <200307120945.36058.dev@archonet.com> (raw)
In-Reply-To: <000801c346be$cf166db0$cf01a8c0@vijay>
References: <000801c346be$cf166db0$cf01a8c0@vijay>
On Thursday 10 Jul 2003 9:39 am, Vijay Kumar wrote:
> Hi,
> We are using postgresql 7.3.3, we are encountering the following problems
> when we used temporary tables.
>
> Here with i'm sending my Sample function.
>
> create or replace function TestTemp_refcur(refcursor) returns refcursor As
[snip]
> lString := lString || '' create temporary table temp_Table( Name
> Numeric);'';
[snip]
> open refc for select * from temp_Table;
[snip]
> psql:test18.sql:25: ERROR: Relation "temp_table" does not exist
Sorry about the delay.
The issue is that plpgsql is compiled, so the "open refc ... temp_Table"
refers to a specific entry in the system-tables. When the temp-table gets
recreated it gets a new OID and so your function can't find it.
Solution:
1. Don't use temp-tables
2. Use EXECUTE "..." which gets parsed when the function is run.
Note that this problem applies to any table that gets dropped and restored. I
try to keep my function code in the same file as the table(s) they rely on,
that way I recreate both when I make changes.
--
Richard Huxton
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: dev@archonet.com, vijay@kinera.com
Subject: Re: Problem with temporary table -- Urgent
In-Reply-To: <200307120945.36058.dev@archonet.com>
* 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