Can't Get SETOF Function to Work
От | Lane Van Ingen |
---|---|
Тема | Can't Get SETOF Function to Work |
Дата | |
Msg-id | EKEMKEFLOMKDDLIALABIMEILCEAA.lvaningen@esncc.com обсуждение исходный текст |
Ответы |
Re: Can't Get SETOF Function to Work
|
Список | pgsql-sql |
Have tried perhaps 20+ alternatives on this plpgsql function, but can't get it to work. Suspect I have done something wrong either with RETURN values, creating of a type, or (most likely) use of ROW(). I am trying to fix the returned rows from enclosing reults in parenthesis; found a similar incident in archives, but there is not enough detail to help me figure out what is wrong: http://archives.postgresql.org/pgsql-sql/2005-10/threads.php#00321 Can someone spot what I am doing wrong? Here is what I have: CREATE TYPE typ_history_rec_format AS ( interface integer, updatedTime timestamp(3), rftype integer, rfspeed bigint) ; CREATE OR REPLACE FUNCTION router_history() RETURNS SETOF typ_history_rec_format AS $BODY$ DECLARE returnValue RECORD; workarea RECORD; work_interface integer; work_rftype integer; BEGIN FOR workarea IN select '1' AS seq, if_id AS interface, updated_time AS updatedTime, link_type AS rftype, 0 ASrfspeed FROM rf_type_history union select '2' AS seq, if_id AS interface, updated_time AS updatedTime, 0 ASrftype, speed AS rfspeed FROM rf_speed_history order by 2,3,1 LOOP if workarea.seq = 1 then work_interface:= workarea.interface; work_rftype := workarea.rftype; else if workarea.interface = work_interfacethen select into returnValue ROW(workarea.interface,workarea.updatedTime, work_rftype,workarea.rfspeed); RETURN NEXT returnValue; end if; end if; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; WHAT GETS RETURNED: Query: select * from router_history(); Result: ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "router_history" line 29 at return next
В списке pgsql-sql по дате отправления: