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  (Michael Fuhr <mike@fuhr.org>)
Список 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 по дате отправления:

Предыдущее
От: "Mark R. Dingee"
Дата:
Сообщение: Re: PGSQL encryption functions
Следующее
От: "Mark R. Dingee"
Дата:
Сообщение: Re: PGSQL encryption functions