plpgsql temporary table problem

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