pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan't Get SETOF Function to Work
3+ messages / 2 participants
[nested] [flat]
* Can't Get SETOF Function to Work
@ 2005-11-01 21:38 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Lane Van Ingen @ 2005-11-01 21:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can't Get SETOF Function to Work
@ 2005-11-02 00:44 Michael Fuhr <mike@fuhr.org>
parent: Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2005-11-02 00:44 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can't Get SETOF Function to Work
@ 2005-11-02 00:55 Lane Van Ingen <lvaningen@esncc.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: Lane Van Ingen @ 2005-11-02 00:55 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-11-02 00:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-01 21:38 Can't Get SETOF Function to Work Lane Van Ingen <lvaningen@esncc.com>
2005-11-02 00:44 ` Michael Fuhr <mike@fuhr.org>
2005-11-02 00:55 ` Lane Van Ingen <lvaningen@esncc.com>
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