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
