Re: Temporary Tables

Create your temp tables like this:

CREATE TEMP TABLE mytest
(

)WITHOUT OIDS ON COMMIT DELETE ROWS

PG holds onto the temp table for the duration of the connection, when the connection ends all temp tables are dropped. This means you can simply reuse the same tables for the duration of the connection.

In my functions before I create the temp table I check to see if it exists with function below, if the table already exists I don't try and create it.
You can also run into issues where you have to do your SQL in execute statements so it can use the proper OID for the temp table. We have been using this method with temp tables and it works great.

(author unknown)

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

>
> From: "Joseph M. Day" <jday(at)gisolutions(dot)us>
> Date: 2005/03/31 Thu AM 10:49:37 EST
> To: <pgsql-general(at)postgresql(dot)org>
> Subject: [GENERAL] Temporary Tables
>
> I am having some problems understanding how the temp tables work in PG.
> I have a relatively lengthy function I am creating that makes frequent
> use of temporary tables.
>
> I am dropping and recreating the temp tables on each run. If I run the
> procedure the first time via psql it seems to run fine. If I try to
> immediately run it again I get the following message:
>
> Error: relation with OID 22938 does not exist
>
> The query it is running is the following:
>
> Select Into sTableName TableName From tmp_tblJoin Where ID = 1;
>
> Nothing special about it other than "tmp_tblJoin" is defined as a
> temporary table.
>
> I do understand what is happening (I think). There is a stale pointer to
> the previous instance of the temp table (that no longer exists) which is
> causing the function to blow up. My question is how to I stop it from
> storing the OID of the old reference.
>
> I am relatively new to PG, but have years of experience with MSSQL and
> never had to deal with these type of issues. I am using plpqsql and
> explicitly setting it to volatile.
>
> Any help may save the last couple strands of hair on my head :-)
>
> Joe,
>
>
> --------------------------------------------
> Joseph M. Day
> Global Innovative Solutions
>
>
>
>