From: | Ron St-Pierre <rstpierre(at)syscor(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Function Returning SETOF Problem |
Date: | 2003-12-18 00:11:29 |
Message-ID: | 3FE0F0B1.5080502@syscor.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-12-18 00:26:20 | Re: Function Returning SETOF Problem |
Previous Message | Peter Eisentraut | 2003-12-17 23:16:27 | Re: restore error - language "plperlu" is not trusted |