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