Re: Using Temporary Tables in postgres functions

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