From: | "Lane Van Ingen" <lvaningen(at)esncc(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Error Returned by A Function |
Date: | 2006-01-10 16:16:05 |
Message-ID: | EKEMKEFLOMKDDLIALABIAEAPCHAA.lvaningen@esncc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Forgot to mention, I am using Windows 2003, PostgreSQL version 8.0.1.
-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Lane Van Ingen
Sent: Tuesday, January 10, 2006 10:43 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Error Returned by A Function
Hi all, I have a short function below that is returning me an error, and I
can't figure out what I should do to fix it. Can anyone help? Archives have
not helped. The error I get is:
select * from current_neighbors(2);
ERROR: cannot assign non-composite value to a row variable
CONTEXT: PL/pgSQL function "current_neighbors" line 15 at assignment
------------------
CREATE TYPE typ_remote_net AS
(remote_net varchar);
CREATE OR REPLACE FUNCTION current_neighbors(integer)
RETURNS SETOF typ_remote_net AS
$BODY$
DECLARE
work_if_id ALIAS FOR $1;
first_record char(1);
last_remote varchar;
last_neighbor_state integer;
output_count integer := 0;
returnValue typ_remote_net;
workarea adns_neighbor_history%ROWTYPE;
BEGIN
returnValue := 'none';
first_record := 'Y';
FOR workarea IN
select if_id, updated_time, remote_net, neighbor_state,
last_checked
from adns_neighbor_history
where if_id = work_if_id
order by BY 1,3,2
loop
if first_record = 'N' then
if workarea.remote_net = last_remote then
if workarea.neighbor_state = last_neighborstate then
-- same values, no action required
NULL;
else
-- store latest neighbor state
last_neighbor_state := workarea.neighbor_state;
end if;
else
-- see if last remote needs to be reported
if last_neighborstate > 0 then
returnValue = last_remote;
RETURN NEXT returnValue;
output_count := output_count + 1;
end if;
last_remote := workarea.remote_net;
last_neighbor_state := workarea.neighbor_state;
end if;
else
first_record = 'N';
last_remote := workarea.remote_net;
last_neighbor_state := workarea.neighbor_state;
end if;
end loop;
if (last_neighbor_state > 0) then
RETURN NEXT returnValue;
else
if output_count = 0 then
returnValue := 'none';
RETURN NEXT returnValue;
end if;
end if;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-10 16:33:39 | Re: Error Returned by A Function |
Previous Message | Tom Lane | 2006-01-10 15:57:53 | Re: Preventing access of user1 to user2's database |