Re: Function Returning SETOF Problem

On Thu, 18 Dec 2003, Ron St-Pierre wrote:

> 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

For the first one, you're making a query string that has lines like
foo = rec.bar
where you really want
foo = <value of rec.bar>

So for execute you want something like
'' ... foo = '' || rec.bar || '' ... ''
(possibly requiring casts)

> <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

As Tom said, this looks like something else. Do you have a rule on
currentlist that also does an update on currentlist, perhaps forcing
certain values or something?