Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004
    Posts
    43

    Unanswered: temporary tables

    I have a function :
    Code:
    CREATE or replace FUNCTION UTIL.get_ids (sn TEXT, tn TEXT, fid integer) RETURNS SETOF INTEGER AS'
    begin
        create temp table ids(_id integer unique) without oids on commit drop;
        insert into ids values(fid);
        drop table ids cascade;
        return;
    end;
    'LANGUAGE plpgSQL;
    and it generates error on second call :
    Code:
    dmitri=# select * from UTIL.get_ids('ORG', 'Organisations',1672);
    NOTICE:  CREATE TABLE / UNIQUE will create implicit index "ids__id_key" for table "ids"
    CONTEXT:  SQL query "create temp table ids(_id integer unique) without oids on commit drop"
    PL/pgSQL function "get_ids" line 2 at SQL statement
     get_ids
    ---------
    (0 rows)
    
    dmitri=# select * from UTIL.get_ids('ORG', 'Organisations',1672);
    NOTICE:  CREATE TABLE / UNIQUE will create implicit index "ids__id_key" for table "ids"
    CONTEXT:  SQL query "create temp table ids(_id integer unique) without oids on commit drop"
    PL/pgSQL function "get_ids" line 2 at SQL statement
    ERROR:  relation with OID 12753003 does not exist
    CONTEXT:  SQL query "insert into ids values( $1 )"
    PL/pgSQL function "get_ids" line 3 at SQL statement
    d
    Why this going on?
    PS sorry for my english

  2. #2
    Join Date
    Jun 2004
    Posts
    31
    I had a similar problem with 7.4.6 -- I don't know if this was fixed in 8.0.

    I never pursued it, just coded around it. It seems that the first time the function is called, Pg compiles in the OID of the table being referenced. When the function is called again later, the OID points to a table that no longer exists, and the code breaks.

    Hope this helps.

Posting Permissions

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