Function Returning SETOF Problem

[Ron St-Pierre]

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 updateCurrentDa ta():
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "updatecurrentc ata" 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 updateCurrentDa ta() 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.seco nd, third=rec.third , grandttl=rec.gr andttl,
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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

Re: Function Returning SETOF Problem


On Wed, 17 Dec 2003, Ron St-Pierre wrote:
[color=blue]
> 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 updateCurrentDa ta():
> ERROR: set-valued function called in context that cannot accept a set
> CONTEXT: PL/pgSQL function "updatecurrentc ata" line 6 at return next
> I've googled and tried variations on the function, but without success.
> Can anyone help?[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Ron St-Pierre]

Re: Function Returning SETOF Problem

Stephan Szabo wrote:
[color=blue]
>On Wed, 17 Dec 2003, Ron St-Pierre wrote:
>
>
>[color=green]
>>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 updateCurrentDa ta():
>> ERROR: set-valued function called in context that cannot accept a set
>> CONTEXT: PL/pgSQL function "updatecurrentc ata" line 6 at return next
>>I've googled and tried variations on the function, but without success.
>>Can anyone help?
>>
>>[/color]
>
>This probably means that you're calling it like:
> select updateCurrentDa ta();
>and you'll need to instead call it with the function in the FROM clause,
>something like:
> select * from updateCurrentDa ta();
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org
>
>
>
>[/color]
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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Re: Function Returning SETOF Problem

Ron St-Pierre <rstpierre@sysc or.com> writes:[color=blue]
> 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 updateCurrentDa ta():
> ERROR: set-valued function called in context that cannot accept a set[/color]

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

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

Re: Function Returning SETOF Problem

On Wed, 17 Dec 2003, Ron St-Pierre wrote:
[color=blue]
> Stephan Szabo wrote:
>[color=green]
> >On Wed, 17 Dec 2003, Ron St-Pierre wrote:
> >
> >
> >[color=darkred]
> >>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 updateCurrentDa ta():
> >> ERROR: set-valued function called in context that cannot accept a set
> >> CONTEXT: PL/pgSQL function "updatecurrentc ata" line 6 at return next
> >>I've googled and tried variations on the function, but without success.
> >>Can anyone help?
> >>
> >>[/color]
> >
> >This probably means that you're calling it like:
> > select updateCurrentDa ta();
> >and you'll need to instead call it with the function in the FROM clause,
> >something like:
> > select * from updateCurrentDa ta();
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org
> >
> >
> >
> >[/color]
> aha, that's part of it. I now get this error:
> ERROR: wrong record type supplied in RETURN NEXT
> Any ideas on this one?[/color]

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Ron St-Pierre]

Re: Function Returning SETOF Problem

Stephan Szabo wrote:
<snip>
[color=blue][color=green]
>>and you'll need to instead call it with the function in the FROM clause,[color=darkred]
>>> >something like:
>>> > select * from updateCurrentDa ta();
>>> >[/color]
>>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.
>>
>>[/color][/color]
</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 updateSecondary Data () 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.seco nd, third=rec.third , grandttl=rec.gr andttl,
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.seco nd, third=rec.third , grandttl=rec.gr andttl,
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 "currentlis t"
CONTEXT: PL/pgSQL function "updatesecondar ydata " line 7 at SQL
statement

Any ideas on what I'm doing wrong this time?

TIA
Ron



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Re: Function Returning SETOF Problem

Ron St-Pierre <rstpierre@sysc or.com> writes:[color=blue]
> : I get this error:
> ERROR: infinite recursion detected in rules for relation "currentlis t"[/color]

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

Re: Function Returning SETOF Problem


On Thu, 18 Dec 2003, Ron St-Pierre wrote:
[color=blue]
> Stephan Szabo wrote:
> <snip>
>[color=green][color=darkred]
> >>and you'll need to instead call it with the function in the FROM clause,
> >>> >something like:
> >>> > select * from updateCurrentDa ta();
> >>> >
> >>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.
> >>
> >>[/color][/color]
> </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[/color]

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)
[color=blue]
> <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.seco nd, third=rec.third , grandttl=rec.gr andttl,
> 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 "currentlis t"
> CONTEXT: PL/pgSQL function "updatesecondar ydata " line 7 at SQL
> statement[/color]

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?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

Re: Function Returning SETOF Problem

Ron St-Pierre wrote:[color=blue]
> Here are my record type and function:[/color]

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.
[color=blue]
> END LOOP;
> RETURN 1;
> END;
> ' LANGUAGE 'plpgsql';[/color]

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

HTH,

Joe


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ron St-Pierre]

Re: Function Returning SETOF Problem

Stephan Szabo wrote:
<snip>
[color=blue]
>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)
>
>[/color]
</snip>
Okay, fixed that ...

<snip>
[color=blue]
>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?
>
>[/color]
</snip>

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

Ron



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster