Re: Index Problem?

Ron St-Pierre wrote:
> I am using postgres 7.4.1 and have a problem with a plpgsql function.
> When I run the function on the production server it takes approx 33
> minutes to run. I dumped the DB and copied it to a similarly configured
> box and ran the function and it ran in about 10 minutes. Can anyone
> offer advice on tuning the function or my database? Here are the
> lengthy, gory details.
>
> F u n c t i o n
> It updates seven columns of a table 1 to 4 times daily. Current data =
> 42,000 rows, new data = 30,000 rows.
>
> CREATE TYPE employeeType AS (empID INTEGER, updateDate DATE, bDate
> INTEGER, val1 NUMERIC, val2 NUMERIC, val3 NUMERIC, val4 NUMERIC, favNum
> NUMERIC);
>
> CREATE OR REPLACE FUNCTION updateEmployeeData() RETURNS SETOF
> employeeType AS '
> DECLARE
> rec RECORD;
> BEGIN
> FOR rec IN SELECT empID, updateDate, bDate, val1, val2, val3, val4, favNum FROM newData LOOP
> RETURN NEXT rec;
> UPDATE currentData SET val1=rec.val1, val2=rec.val2, val3=rec.val2, val4=rec.val4, favNum=rec.favNum, updateDate=rec.updateDate
> WHERE empID=rec.empID;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';

Can't you handle this with a simple update query?

UPDATE
currentData
SET
val1 = newData.val1,
val2 = newData.val2,
val3 = newData.val3,
val4 = newData.val4,
favNum = newData.favNum,
updateDate = newData.updateDate
FROM
newData
WHERE
newDate.empID = currentData.empID

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje