From: Mario Splivalo <mario.splivalo@mobart.hr>
To: pgsql-sql@postgresql.org
Subject: Re: Using Temporary Tables in postgres functions
Date: Fri, 26 Jan 2007 11:47:05 +0100
Message-ID: <1169808425.3045.15.camel@localhost.localdomain> (raw)
In-Reply-To: <20070125160038.GB16529@phlogiston.dyndns.org>
References: <1169735954.31872.38.camel@localhost.localdomain>
<20070125160038.GB16529@phlogiston.dyndns.org>
On Thu, 2007-01-25 at 11:00 -0500, Andrew Sullivan wrote:
> On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote:
> > When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
> > as a function language), I can't because postgres can't find that
> > temporary table. Consider this example:
>
> You need to build the temp table and EXECUTE the statement. The
> problem is that the plan is cached for later re-use. Since the
> cached plan has the id of a table that no longer exists, you get this
> error. See the PL/pgSQL part of the manual for more on this.
Yes, I'm well aware of that, and learned to live with it :) The only
drawback is that my plpgsql code looks realy uqly, because of the
creation of the string variables containing the actuall SQL code that
deals with the temporary tables used. Therefore I'm trying not to use
temp tables as much as I can. A table-type variables would be realy nice
thing to have, I guess they would exist only in memory, and for some
complicated OLTP stuff those could be realy realy hand.
Just a wish, in a way :)
Mike
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: mario.splivalo@mobart.hr
Subject: Re: Using Temporary Tables in postgres functions
In-Reply-To: <1169808425.3045.15.camel@localhost.localdomain>
* 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