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" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] 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!
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
