temporal_value.sql

CREATE TABLE snmp.ifDescr (
  device_id BIGINT NOT NULL REFERENCES devices,
  ifIndex BIGINT NOT NULL,
  ifDescr TEXT,
  valid TSTZRANGE NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'),
  EXCLUDE USING GIST (device_id WITH =, ifIndex WITH =, valid WITH &&),
  EXCLUDE USING GIST (device_id WITH =, ifDescr WITH =, valid WITH &&)
);

CREATE OR REPLACE FUNCTION set_ifdescr(xdevice_id bigint, xifIndex bigint, xifDescr text)
RETURNS TABLE(device_id bigint, ifIndex bigint, ifDescr text, valid tstzrange) AS $$
DECLARE
  cur_ifdescr TEXT;
BEGIN
  cur_ifdescr := NULL;
  SELECT t.ifdescr INTO cur_ifdescr
    FROM snmp.ifdescr t
    WHERE
      t.device_id = xdevice_id AND
      t.ifindex = xifindex AND
      t.valid @> now();
  IF NOT FOUND THEN
    RETURN QUERY
      INSERT INTO snmp.ifdescr AS t (device_id, ifIndex, ifDescr)
        VALUES (xdevice_id, xifIndex, xifDescr)
        RETURNING t.device_id, t.ifIndex, t.ifDescr, t.valid;
  ELSE
    IF cur_ifdescr <> xifDescr THEN
        UPDATE snmp.ifDescr t SET valid = tstzrange(LOWER(t.valid), NOW(), '[)')
          WHERE
            t.device_id = xdevice_id AND
            t.ifIndex = xifIndex AND
            t.valid @> NOW();
        RETURN QUERY
          INSERT INTO snmp.ifdescr AS t (device_id, ifIndex, ifDescr)
            VALUES (xdevice_id, xifIndex, xifDescr)
            RETURNING t.device_id, t.ifIndex, t.ifDescr, t.valid;
    END IF;
  END IF;
END;
$$ LANGUAGE plpgsql;