Re: plpgsql temporary table problem - Mailing list pgsql-general

This is a known problem.  You need to use EXECUTE CREATE TABLE in
PL/pgSQL so that the saved plpgsql function is not used and it is
reparsed for each call.  I believe every reference to the TEMP table has
to be an EXECUTE.  I think this will be fixed in 7.3 because the temp
tables will exist in a separate schema.

---------------------------------------------------------------------------

Geoff Russell wrote:
> Hi people,
>
> I seem to have a problem with repeated temporary table creation in
> a plpgsql function. The problem is illustrated below.
>
> I'm running 7.2.1 on Linux. I should add that I just upgraded from 7.1.3
> and
> had no problems - congratulations to you all.  (I have no idea on
> whether this
> problem occurred on 7.1.3, its part of new code I've just written.)
>
> The second "select parent_copy(...) " statement below gives the message:
>
> NOTICE:  Error occurred while executing PL/pgSQL function parent_copy
> NOTICE:  line 17 at SQL statement
> ERROR:  Relation 2398261 does not exist
>
> Cheers,
> Geoff Russell
>
> /*
>  * sample illustrating either a "create temp table ... " problem
>  * or perhaps just something I don't understand!
>  */
>
> /* first make some tables and data */
> drop sequence parent_id_seq;
> drop table parent;
> drop table child;
> create table parent (
>      id serial,
>      data integer
> );
> drop table child;
> create table child (
>      parent_id integer,
>      data integer
> );
> insert into parent (data) values(1);
> insert into child (parent_id,data) values(1,1);
> insert into child (parent_id,data) values(1,2);
> insert into child (parent_id,data) values(1,3);
> /* now a pgsql function to copy children to a new parent  */
> drop FUNCTION parent_copy (integer);
> CREATE FUNCTION parent_copy (integer) RETURNS integer as '
> DECLARE
>      fromid alias for $1;
>      newparentid integer;
> BEGIN
>      select into newparentid nextval(''parent_id_seq'');
>      /* make the new parent and add it to table
>       * (NOTE:
>       *    it would be really nice to use a RECORD variable as follows.
>       *    parentrecord RECORD;
>       *    select into parentrecord select * from parent where
> id=fromid;
>       *    parentrecord.id=newparentid;
>       *    insert into parent parentrecord;
>       * )
>       */
>      insert into parent (id,data) values (newparentid,5);
>      create temp table tmpchild as select * from child where
> parent_id=fromid ;
>      update tmpchild set parent_id=newparentid;
>      insert into child select * from tmpchild;
>      drop table tmpchild;
>      return newparentid;
> END; '
> LANGUAGE 'plpgsql';
>
> select parent_copy(1);
> select parent_copy(2);
> select * from parent;
> select * from child;
>
>
> --
> Geoff,
>
> geoff@austrics.com.au            | Phone: +618-8332-5069
> 6 Fifth Ave, St Morris, SA 5068  |   Fax: +618-8364-1543
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026