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
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 |