I'm pretty new to postgresql.. I'm using a fresh compile/install of postgresql 7.1.2 without any special options.. but here's my 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*rating FROM 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 have ANY idea how I can either redesign this query or make the create/drop thing work properly?

Thanks,
(::) Bob Ippolito

Fri, 20 Jul 2001 06:27:28 -0400
"\(::\) Bob Ippolito" <bob@redivi.com>
problem with creating/dropping tables and plpgsql ?
"\(::\) 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

Fri, 20 Jul 2001 11:53:31 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: problem with creating/dropping tables and plpgsql ? 