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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lvaningen@esncc.com
Subject: Re: Can't Get SETOF Function to Work
In-Reply-To: <EKEMKEFLOMKDDLIALABIMEILCEAA.lvaningen@esncc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox