Re: temp tables in functions?

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.