From: "vijaykumar M" <m_vijaykumar@hotmail.com>
To: jinujosein@yahoo.com, pgsql-sql@postgresql.org
Subject: Re: Temporary tables
Date: Mon, 29 Sep 2003 05:16:49 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY2-F50EiLFkSOMF5Y0002892d@hotmail.com>

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@yahoo.com>
>To: pgsql-sql@postgresql.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!