Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    28

    Unanswered: temporary tables

    hi all,

    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

  2. #2
    Join Date
    Apr 2003
    Location
    Tunisia
    Posts
    192

    Thumbs up views

    I haven't understood very much the prob, but I'll recomand you to use views ! that's better !
    Open up
    Take a look to my Blog http://www.rundom.com/karim2k

  3. #3
    Join Date
    May 2003
    Posts
    28
    i have solved the problem by creating the following function. and call the function and crete temp table only if it is already exists.

    CREATE FUNCTION istableexist(varchar) RETURNS bool AS '

    DECLARE

    BEGIN

    /* check the table exist in database and is visible*/
    PERFORM relname,relnamespace FROM pg_class
    WHERE relkind = ''r''
    AND Upper(relname) = Upper($1)
    AND pg_table_is_visible(oid);

    IF FOUND THEN
    RETURN TRUE;
    ELSE
    RETURN FALSE;
    END IF;

    END;'
    LANGUAGE 'plpgsql';


    jinujose

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •