Re: temp tables ORACLE/PGSQL

This is not entirely correct. We use temp tables all the time in PLpgsql functions and never have to use
execute. We have found that you have to use EXECUTE only in certain circumstances.

stored procedures that use temporary tables are more
> painful to write - you need to use EXECUTE for any SQL that references a
> temporary table - read the Porting From Oracle section of the PostgreSQL
> manual. I'd recommend rereading it several times.

we use this in all our functions that use temp tables, and we use PG Lightning Admin, which refreshes the
connection each time you run a query which gets around a lot of issues with temp tables.

CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS pg_catalog.bool AS
$BODY$
DECLARE

BEGIN

/* check the table exist in database and is visible*/
perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);

IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com