Can't Get SETOF Function to Work

From: "Lane Van Ingen" <lvaningen(at)esncc(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Can't Get SETOF Function to Work
Date: 2005-11-01 21:38:12
Message-ID: EKEMKEFLOMKDDLIALABIMEILCEAA.lvaningen@esncc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 AS rfspeed
FROM rf_type_history
union
select '2' AS seq, if_id AS interface, updated_time AS updatedTime,
0 AS rftype, 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_interface then
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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mark R. Dingee 2005-11-01 22:00:50 Re: PGSQL encryption functions
Previous Message Mark R. Dingee 2005-11-01 21:30:51 Re: PGSQL encryption functions