Stephan Szabo wrote:
<snip>
>>and you'll need to instead call it with the function in the FROM clause,
>>> >something like:
>>> > select * from updateCurrentData();
>>> >
>>aha, that's part of it. I now get this error:
>> ERROR: wrong record type supplied in RETURN NEXT
>>Any ideas on this one?
>>
>>
>>That sounds like a mismatch between the record in rec and your declared
>>output type, but I couldn't say for sure without a complete example
>>including the table declarations really.
>>
>>
</snip>
You were right again. The order of columns in my record_type was
different than my select. Now when I run the script I get the following
error:
ERROR: relation "rec" does not exist
Here are my record type and function:
CREATE TYPE place_finish AS (first NUMERIC, second NUMERIC, third
NUMERIC, grandttl INTEGER, lname TEXT, fname TEXT);
CREATE OR REPLACE FUNCTION updateSecondaryData () RETURNS SETOF
place_finish AS '
DECLARE
rec RECORD;
updstmt TEXT;
BEGIN
FOR rec IN SELECT first, second, third, grandttl, lname,
fname FROM dailyList LOOP
RETURN NEXT rec;
updstmt := ''UPDATE currentList SET first=rec.first,
second=rec.second, third=rec.third, grandttl=rec.grandttl,
lname=rec.lname, fname=rec.fname WHERE lname=rec.lname AND
fname=rec.fname;'';
EXECUTE updstmt;
END LOOP;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
If I modify the function and try to run the update statement directly
<snip>
FOR rec IN SELECT first, second, third, grandttl, lname,
fname FROM dailyList LOOP
RETURN NEXT rec;
UPDATE currentList SET first=rec.first,
second=rec.second, third=rec.third, grandttl=rec.grandttl,
lname=rec.lname, fname=rec.fname WHERE lname=rec.lname AND fname=rec.fname;
END LOOP;
</snip>
: I get this error:
ERROR: infinite recursion detected in rules for relation "currentlist"
CONTEXT: PL/pgSQL function "updatesecondarydata " line 7 at SQL
statement
Any ideas on what I'm doing wrong this time?
TIA
Ron
