Обсуждение: problem with creating/dropping tables and plpgsql ?

I'm pretty new to postgresql.. I'm using a fresh compile/install of postgresql 7.1.2 without any special options.. but
here'smy problem:
 

semantic=# create temp table ttmptable(lookup_id int, rating int);
CREATE
semantic=# SELECT doEverythingTemp(20706,2507);doeverythingtemp 
------------------               1
(1 row)
semantic=# DROP table ttmptable;
DROP
semantic=# create temp table ttmptable(lookup_id int, rating int);
CREATE
semantic=# SELECT doEverythingTemp(20706,2507);
ERROR:  Relation 4348389 does not exist

--- schema --

CREATE FUNCTION doEverythingTemp(int,int) RETURNS int AS '
DECLARE rrec RECORD; userid int; lookupid int; rrating int; ruser int;
BEGIN userid := $1; lookupid := $2; FOR rrec IN SELECT webuser_id,rating FROM rating WHERE webuser_id!=userid AND
lookup_id=lookupid;    rrating:=rrec.rating;     ruser:=rrec.webuser_id;     INSERT INTO ttmptable SELECT
lookup_id,rrating*ratingFROM rating WHERE webuser_id=ruser AND lookup_id!=lookupid; END LOOP; RETURN 1;
 
END;' LANGUAGE 'plpgsql'

Table "rating"Attribute  |  Type   |                         Modifier                         
-------------+---------+----------------------------------------------------------
webuser_id  | integer | not null default '0'
category_id | integer | not null default '0'
lookup_id   | integer | not null default '0'
rating      | integer | not null default '0'
rating_id   | integer | not null default nextval('"rating_rating_id_seq"'::text)
Indices: rating_category_id_idx,        rating_lookup_id_idx,        rating_rating_id_key,
rating_webuser_id_idx


I've tried regular tables, creating the table from within the function, and a few other things.. no luck.  Does anyone
haveANY idea how I can either redesign this query or make the create/drop thing work properly?
 

Thanks,
(::) Bob Ippolito

"\(::\) Bob Ippolito" <bob@redivi.com> writes:
> semantic=# DROP table ttmptable;
> DROP
> semantic=# create temp table ttmptable(lookup_id int, rating int);
> CREATE
> semantic=# SELECT doEverythingTemp(20706,2507);
> ERROR:  Relation 4348389 does not exist

Yeah, temp tables and plpgsql functions don't coexist very well yet.
(plpgsql tries to cache query plans, and at the moment there's no
mechanism to let it flush obsolete plans when a table is deleted.)

What you'll need to do is create a temp table that lasts for the whole
session and is re-used by each successive call of the plpgsql function.
You don't need to worry about dropping the temp table at session exit;
that's what temp tables are for, after all, to go away automatically.
So, just delete all its contents at entry or exit of the function,
and you can re-use it each time through.
        regards, tom lane