Re: Temporary tables

Hi,

Try the below steps ..

1. Write one procedure to create tempory table (generic purpose)
**********************************************************************
CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS '
DECLARE
L_SchemaName name;
BEGIN
EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);'';
SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname
=''temp_table_gen'';
RETURN L_SchemaName;
END;
' LANGUAGE 'plpgsql';
**********************************************************************

2. Call the above (generic) procedure to get the temporary table schema
name.. by using that schema name ..you can check whether the (real)
temporary table is exists or not.

**************************************************************************
select into L_SchemaName * from SP_CREATE_TEMP_TABLE(); -- get the
schemaname
execute ''drop table temp_table_gen;''; -- drop the temptable
select schemaname into L_Schema from pg_stat_user_tables where
relname=''temp_total_count'' and schemaname =''''||L_SchemaName||'''';
if (L_Schema is null) then
EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT
NUMERIC);'';
ELSE
EXECUTE ''DELETE FROM temp_total_count;'';
END IF;
**************************************************************************

I hope this will help u to solve these temporary table issues..

With Regards
Vijay

>From: "George A.J" <jinujosein(at)yahoo(dot)com>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: [SQL] Temporary tables
>Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT)
>
>
>hi,
>
>I am using postgresql 7.3.2. Is there any function to determine
>whether a table exists in the database.Or is there any function
>that returns the current temp schema.
>I am using a pl/pgsql function that create and drop a temporary table.
>The procedure run correctly for the first time for each database
>connection.
>If I run the same procedure second time in the same connection it produces
>the error
>
>"ERROR: pg_class_aclcheck: relation 219389 not found
>WARNING: Error occurred while executing PL/pgSQL function testFun
>WARNING: line 20 at SQL statement "
>
>Here is the function ....
>
>---------------------------------------------------------
>CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int
>AS
>'
>DECLARE
> --Aliases for parameters
> vSBAcNo ALIAS FOR $1;
> --local variables
> vRow RECORD;
>
>BEGIN
> -- create a tempory table to hold the numbers
> CREATE TABLE tempTable
> (
> testNo int
> ) ;
>
> for vRow IN select Entryno from EntryTable LOOP
>
> return next vRow.Entryno;
>
> insert into tempTable values( vRow.Entryno);
>
> end loop;
>
> drop table tempTable;
>
> return;
>
>END;'
>
>LANGUAGE 'plpgsql';
>
>-------------------------------------------------------------
>
>If i commented the "insert into tempTable values( vRow.Entryno);" line
>the function works correctly. The problem is the oid of tempTable is kept
>when
>the function is first executed. the next execution creates another table
>with
>different oid. So the insert fails.
>
>I want to check whether the temporary table exist. If exist do not create
>the
>temporary table in subsequent calls and do not dorp it. This will solve the
>problem.
>
>When i searched the pg_class i found the temp table name more than once.
>ie, a temporary table is created for each connection.I cannot distingush
>the temp tables. But the tables are in different schema.
>Is there a method to get the current temporary schema? How postgres
>distinguish
>this temp tables?.Is there a way to distinguish temporary tables.
>The entries in pg_class table is same except the schema.
>When i used the current_schema() function it returns public.
>
>There is a lot of functions that uses temporary tables. I think that there
>is
>an option when creating temp tables in postgres 7.4 . But no way to use 7.4
>now it is a working database.
>
>can i write a function to check the existance of the temporary table...
>please help...
>
>jinujose
>
>
>---------------------------------
>Do you Yahoo!?
>The New Yahoo! Shopping - with improved product search

_________________________________________________________________
Keep up with the pace of change. Register for My Tech Ed.
http://server1.msn.co.in/sp03/teched/index.asp Realise your potential!