Re: Function Returning SETOF Problem - Mailing list pgsql-general

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