From: "Lane Van Ingen" <lvaningen@esncc.com>
To: <pgsql-sql@postgresql.org>
Subject: Can't Get SETOF Function to Work
Date: Tue, 1 Nov 2005 16:38:12 -0500
Message-ID: <EKEMKEFLOMKDDLIALABIMEILCEAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

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