Обсуждение: Can't Get SETOF Function to Work

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

On Tue, Nov 01, 2005 at 04:38:12PM -0500, Lane Van Ingen wrote:
> 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().
> ...
> CREATE OR REPLACE FUNCTION router_history()
>   RETURNS SETOF typ_history_rec_format AS
> $BODY$
> DECLARE
>   returnValue        RECORD;

Try declaring returnValue as typ_history_rec_format instead of RECORD.

>         select into returnValue ROW(workarea.interface,workarea.updatedTime,
>           work_rftype,workarea.rfspeed);

Get rid of the record constructor:
   select into returnValue workarea.interface, workarea.updatedTime,            work_rftype, workarea.rfspeed;

Using your code with the above two changes and some canned data, I
got the function to work (where "work" means it ran and returned
some rows -- I didn't look closely at the logic to see if the results
were actually correct ;-).

-- 
Michael Fuhr

It worked perfectly! Thank you so much for your help!

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Tuesday, November 01, 2005 7:45 PM
To: Lane Van Ingen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Can't Get SETOF Function to Work


On Tue, Nov 01, 2005 at 04:38:12PM -0500, Lane Van Ingen wrote:
> 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().
> ...
> CREATE OR REPLACE FUNCTION router_history()
>   RETURNS SETOF typ_history_rec_format AS
> $BODY$
> DECLARE
>   returnValue        RECORD;

Try declaring returnValue as typ_history_rec_format instead of RECORD.

>         select into returnValue
ROW(workarea.interface,workarea.updatedTime,
>           work_rftype,workarea.rfspeed);

Get rid of the record constructor:
   select into returnValue workarea.interface, workarea.updatedTime,            work_rftype, workarea.rfspeed;

Using your code with the above two changes and some canned data, I
got the function to work (where "work" means it ran and returned
some rows -- I didn't look closely at the logic to see if the results
were actually correct ;-).

--
Michael Fuhr