Re: temp tables in functions?

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)

On 2/8/07, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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
>