Re: Temporary Tables

You don't need to use execute if you create your temp tables like this:

CREATE TEMP TABLE mytest
(

)WITHOUT OIDS ON COMMIT DELETE ROWS

Then use the follwoing function(author unknown) to see if the temp table already exists:

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

Here is a example using the iftableexists function:

CREATE or REPLACE FUNCTION annual.spann_get_status_list()
RETURNS pg_catalog.refcursor AS
$BODY$
DECLARE
return_cursor refcursor;

BEGIN
--give the cursor a name, this is the name we will use to ref the cursor on the client
return_cursor = 'return_cursor';
--create temp table,but first check if it exists.
--If it already exists for this session we will not recreate
IF iftableexists('temp_get_status_list_an') THEN
RAISE NOTICE 'temp table already exists';
ELSE
CREATE TEMP TABLE temp_get_status_list_an
(
STATUS_ID SMALLINT,
DESCRIPTION VARCHAR(50))WITHOUT OIDS ON COMMIT DELETE ROWS;
END IF;
INSERT INTO temp_get_status_list_an
(
STATUS_ID,DESCRIPTION
)
SELECT status_id, description
FROM annual.annual_status;

-- Open the cursor on the temp table
OPEN return_cursor FOR SELECT * FROM temp_get_status_list_an ORDER BY 1;
-- Return the pointer back to the caller
RETURN return_cursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Another key is not to use PG ADMIN III, it uses the same connection to the database for every query you run, a better choice is PG Lighting admin (http://www.amsoftwaredesign.com)
Lightning admin refreshes the connection each time you run your query,hence refreshing the cached objects.

>
> From: "Joseph M. Day" <jday(at)gisolutions(dot)us>
> Date: 2005/03/31 Thu AM 11:25:02 EST
> To: <Patrick(dot)FICHE(at)AQSACOM(dot)COM>, <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] Temporary Tables
>
> Thanks, I thought there might be a way to force it not to do this.
>
> So I guess for my example I am going to need to create another temporary
> table to retrieve the results of my query, which of course I will also
> have to be created via EXECUTE, since EXECUTE will not work in this
> situation to store data in sTableName.
>
> Any other more elegant ideas to retrieve the data from this?
> -------------------
> Select Into sTableName TableName From tmp_tblJoin Where ID = 1;
>
> Thanks for the help!
>
> Joe,
>
>
> -----Original Message-----
> From: Patrick(dot)FICHE(at)AQSACOM(dot)COM [mailto:Patrick(dot)FICHE(at)AQSACOM(dot)COM]
> Sent: Thursday, March 31, 2005 10:06 AM
> To: jday(at)gisolutions(dot)us; pgsql-general(at)postgresql(dot)org
> Subject: RE: [GENERAL] Temporary Tables
>
>
>
> You can find this in the FAQ
>
>
> 4.26) Why can't I reliably create/drop temporary tables in PL/PgSQL
> functions?
>
>
> PL/PgSQL caches function contents, and an unfortunate side effect is
> that if a PL/PgSQL function accesses a temporary table, and that table
> is later dropped and recreated, and the function called again, the
> function will fail because the cached function contents still point to
> the old temporary table. The solution is to use EXECUTE for temporary
> table access in PL/PgSQL. This will cause the query to be reparsed every
> time.
>
> So as written, the best solution is to use EXECUTE for all queries using
> temporary tables....
>
> ------------------------------------------------------------------------
> -------------------
> Patrick Fiche
> email : patrick(dot)fiche(at)aqsacom(dot)com
> tél : 01 69 29 36 18
> ------------------------------------------------------------------------
> -------------------
>
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Joseph M. Day
> Sent: jeudi 31 mars 2005 17:50
> 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
>