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@fuhr.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
