Re: plpgsql temporary table problem

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(at)austrics(dot)com(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026