X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8A665D1B50A for ; Sat, 27 Sep 2003 13:32:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 21479-04 for ; Sat, 27 Sep 2003 10:31:40 -0300 (ADT) Received: from web14911.mail.yahoo.com (web14911.mail.yahoo.com [216.136.225.249]) by svr1.postgresql.org (Postfix) with SMTP id D4950D1B501 for ; Sat, 27 Sep 2003 10:31:35 -0300 (ADT) Message-ID: <20030927133139.42953.qmail@web14911.mail.yahoo.com> Received: from [202.9.168.99] by web14911.mail.yahoo.com via HTTP; Sat, 27 Sep 2003 06:31:39 PDT Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT) From: "George A.J" Subject: Temporary tables To: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1127839853-1064669499=:42630" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/348 X-Sequence-Number: 15326 --0-1127839853-1064669499=:42630 Content-Type: text/plain; charset=us-ascii 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 --0-1127839853-1064669499=:42630 Content-Type: text/html; charset=us-ascii

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 --0-1127839853-1064669499=:42630--