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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
