diff options
| author | Joachim Tingvold <joachim@tingvold.com> | 2015-04-01 03:44:19 +0200 |
|---|---|---|
| committer | root <root@einstein.tg15.gathering.org> | 2015-04-01 03:44:19 +0200 |
| commit | ee87dca5e68e0f8521163b5603717742b248e6b1 (patch) | |
| tree | 4bd0a403093e8b9c9eb3fe594bd010258ee7e407 /sql | |
| parent | 14e0453b2e74e7a93a092a2cfec46dabbcd2ab65 (diff) | |
NMS-changes
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/nms.sql | 50 |
1 files changed, 50 insertions, 0 deletions
diff --git a/sql/nms.sql b/sql/nms.sql index 18291d1..737b602 100644 --- a/sql/nms.sql +++ b/sql/nms.sql @@ -145,6 +145,56 @@ ALTER FUNCTION public.get_current_datarate() OWNER TO nms; -- Name: get_datarate(); Type: FUNCTION; Schema: public; Owner: nms -- +CREATE TYPE operstatuses AS ( + switch integer, + ifdescr char(16), + operstatus integer, + last_poll_time timestamp with time zone +); +CREATE FUNCTION get_operstatus() RETURNS SETOF operstatuses + LANGUAGE plpgsql + AS $$ +DECLARE + num_entries INTEGER; + poll polls; + last_poll polls; + ret operstatuses; +BEGIN + num_entries := 0; + last_poll.switch = -1; + + FOR poll IN select * from polls where time >= now() - '15 minutes'::interval and time < now() order by switch,ifdescr,time LOOP + IF poll.switch <> last_poll.switch OR poll.ifdescr <> last_poll.ifdescr THEN + IF num_entries >= 1 THEN + ret.switch := last_poll.switch; + ret.ifdescr := last_poll.ifdescr; + ret.operstatus := last_poll.operstatus; + ret.last_poll_time := last_poll.time; + return next ret; + END IF; + num_entries := 1; + ELSE + num_entries := num_entries + 1; + END IF; + last_poll.switch := poll.switch; + last_poll.ifdescr := poll.ifdescr; + last_poll.time := poll.time; + last_poll.operstatus := poll.operstatus; + END LOOP; + -- pah, and once more, for the last switch/port... + IF num_entries >= 1 THEN + ret.switch := last_poll.switch; + ret.ifdescr := last_poll.ifdescr; + ret.operstatus := last_poll.operstatus; + ret.last_poll_time := last_poll.time; + return next ret; + END IF; + + RETURN; +END; +$$; + + CREATE FUNCTION get_datarate() RETURNS SETOF datarate LANGUAGE plpgsql AS $$ |