Re: Function Returning SETOF Problem
От | Ron St-Pierre |
---|---|
Тема | Re: Function Returning SETOF Problem |
Дата | |
Msg-id | 3FE1D144.2010800@syscor.com обсуждение исходный текст |
Ответ на | Re: Function Returning SETOF Problem (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
Ответы |
Re: Function Returning SETOF Problem
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Function Returning SETOF Problem (Stephan Szabo <sszabo@megazone.bigpanda.com>) Re: Function Returning SETOF Problem (Joe Conway <mail@joeconway.com>) |
Список | 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
В списке pgsql-general по дате отправления: