Thread: Function Returning SETOF Problem

On a daily basis I place a lot of data into the empty table dailyList,
and from that data update certain fields in currentList. I thought that
using a function would be a good way to do this(?). However I get the
following error when I run updateCurrentData():
   ERROR:  set-valued function called in context that cannot accept a set
   CONTEXT:  PL/pgSQL function "updatecurrentcata" line 6 at return next
I've googled and tried variations on the function, but without success.
Can anyone help?

Here's the function:
CREATE TYPE place_finish AS (first NUMERIC, second NUMERIC, third
NUMERIC, grandttl INTEGER, lname TEXT, fname TEXT);

CREATE OR REPLACE FUNCTION updateCurrentData() 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';


Thanks
Ron

ps postgres 7.4, debian stable

On Wed, 17 Dec 2003, Ron St-Pierre wrote:

> On a daily basis I place a lot of data into the empty table dailyList,
> and from that data update certain fields in currentList. I thought that
> using a function would be a good way to do this(?). However I get the
> following error when I run updateCurrentData():
>    ERROR:  set-valued function called in context that cannot accept a set
>    CONTEXT:  PL/pgSQL function "updatecurrentcata" line 6 at return next
> I've googled and tried variations on the function, but without success.
> Can anyone help?

This probably means that you're calling it like:
 select updateCurrentData();
and you'll need to instead call it with the function in the FROM clause,
something like:
 select * from updateCurrentData();

Stephan Szabo wrote:

>On Wed, 17 Dec 2003, Ron St-Pierre wrote:
>
>
>
>>On a daily basis I place a lot of data into the empty table dailyList,
>>and from that data update certain fields in currentList. I thought that
>>using a function would be a good way to do this(?). However I get the
>>following error when I run updateCurrentData():
>>   ERROR:  set-valued function called in context that cannot accept a set
>>   CONTEXT:  PL/pgSQL function "updatecurrentcata" line 6 at return next
>>I've googled and tried variations on the function, but without success.
>>Can anyone help?
>>
>>
>
>This probably means that you're calling it like:
> select updateCurrentData();
>and you'll need to instead call it with the function in the FROM clause,
>something like:
> select * from updateCurrentData();
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>
aha, that's part of it. I now get this error:
   ERROR:  wrong record type supplied in RETURN NEXT
Any ideas on this one?

TIA
Ron

Ron St-Pierre <rstpierre@syscor.com> writes:
> On a daily basis I place a lot of data into the empty table dailyList,
> and from that data update certain fields in currentList. I thought that
> using a function would be a good way to do this(?). However I get the
> following error when I run updateCurrentData():
>    ERROR:  set-valued function called in context that cannot accept a set

You're probably doing
    SELECT updateCurrentData();
where you should be doing
    SELECT * FROM updateCurrentData();

There are some cases where you can invoke set-valued functions in the
target list rather than in the FROM list, but this isn't one of 'em.

            regards, tom lane

On Wed, 17 Dec 2003, Ron St-Pierre wrote:

> Stephan Szabo wrote:
>
> >On Wed, 17 Dec 2003, Ron St-Pierre wrote:
> >
> >
> >
> >>On a daily basis I place a lot of data into the empty table dailyList,
> >>and from that data update certain fields in currentList. I thought that
> >>using a function would be a good way to do this(?). However I get the
> >>following error when I run updateCurrentData():
> >>   ERROR:  set-valued function called in context that cannot accept a set
> >>   CONTEXT:  PL/pgSQL function "updatecurrentcata" line 6 at return next
> >>I've googled and tried variations on the function, but without success.
> >>Can anyone help?
> >>
> >>
> >
> >This probably means that you're calling it like:
> > select updateCurrentData();
> >and you'll need to instead call it with the function in the FROM clause,
> >something like:
> > select * from updateCurrentData();
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
> >
> >
> 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.

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

Ron St-Pierre <rstpierre@syscor.com> writes:
> : I get this error:
>    ERROR:  infinite recursion detected in rules for relation "currentlist"

So what kind of rules have you got on "currentlist"?  I don't believe
that complaint has anything to do with your plpgsql function.

            regards, tom lane

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?

Ron St-Pierre wrote:
> Here are my record type and function:

Note -- you could make it easier for people to help, and hence increase
your chances of getting help, if your sample code is complete. I.e.
provide the needed table definition(s) and even some sample data (INSERT
statements) so we don't have to reverse engineer those things.

>           END LOOP;
>           RETURN 1;
>       END;
>   ' LANGUAGE 'plpgsql';

The line "RETURN 1;" ought to be just "RETURN;"

HTH,

Joe

Stephan Szabo wrote:
<snip>

>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>
Okay, fixed that ...

<snip>

>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?
>
>
</snip>

Yes, I did have a rule on the table which I had completely forgotten about. I removed it and the function works
properlynow. 
Thanks Stephan and Tom!

Ron