Skip site navigation (1) Skip section navigation (2)

Re: Temporary Tables

From: <tony_caduto(at)amsoftwaredesign(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Temporary Tables
Date: 2005-03-31 22:12:58
Message-ID: 200503312212.j2VMCw9n007933@mail1.atl.registeredsite.com (view raw or flat)
Thread:
Lists: pgsql-general
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 
> 


pgsql-general by date

Next:From: Cristian PrietoDate: 2005-03-31 22:26:08
Subject: Help with case in select
Previous:From: tony_cadutoDate: 2005-03-31 22:03:39
Subject: Re: Temporary Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group