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