Re: temp tables in functions?

Something like this will help you:

execute immediate 'create temporary table test (a number) on commit drop';

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/8/07, Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
>
> On Wed, Feb 07, 2007 at 20:40:09 -0800,
> jws <jsacksteder(at)gmail(dot)com> wrote:
> > Having developed a complex query, I want to wrap it up as a function
> > so that it can take a parameter and return a set of rows. This query
> > is currently written as multiple sql statements that create a few
> > interstitial temp tables that are then joined. If I put this into a
> > function definition, do those temp tables get dropped automatically
> > when the function returns?
>
> See: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html
> So, the answer is no.
>
> Also note that currently Postgres will cache information about tables
> used in functions and this may not work well when you are dropping and
> recreating tables with the same name in the same session. For that kind
> of thing you need to use EXECUTE to avoid caching.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>