Thread: temp tables in functions?

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?

My alternative is to re-write the query as a self joins and
subqueries. I can do that, but it's somewhat less readable.

On Wed, Feb 07, 2007 at 20:40:09 -0800,
  jws <jsacksteder@gmail.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.

Something like this will help you:

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

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

On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote:
> Something like this will help you:
>
> execute immediate 'create temporary table test (a number) on commit drop';

PL/pgSQL doesn't recognize "immediate" and number isn't a PostgreSQL
type so the above yields a syntax error.  Also, EXECUTE isn't
necessary for the CREATE TABLE statement, although as Bruno mentioned
EXECUTE will be necessary for other statements due to plan caching.
And ON COMMIT DROP won't help if you call the function multiple
times in the same transaction.

--
Michael Fuhr

Agreed :) I guess missed out some details from there as I just thought he needed to drop a temp table inside a function like this:

CREATE OR REPLACE function tempfunc (int) returns int
AS
 $$
  begin
  execute 'create temporary table test (a numeric) on commit drop';
   execute 'INSERT INTO test  values (1);';
return 1;
end;
$$ LANGUAGE 'plpgsql'

 .... used number by mistake so sorry for any inconvenience caused as I was trying it with EnterpriseDB (where 'number 'is added for Oracle compatibility)

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