DO $$
DECLARE
 version_new_ CONSTANT integer := 19;-- new version number --
 ver_ integer;
BEGIN
  LOCK TABLE variables;
  SELECT version_is('Ls') INTO ver_;
  IF ver_ < version_new_ THEN
    -- begin update --

ALTER TABLE device ADD COLUMN ack timestamp with time zone;

DROP FUNCTION device_insert_or_update(bigint, integer);

-- Function: device_insert_or_update(bigint, integer, boolean)

-- DROP FUNCTION device_insert_or_update(bigint, integer, boolean);

CREATE OR REPLACE FUNCTION device_insert_or_update(
    device_id_ bigint, frequency_ integer, is_ack_ boolean)
  RETURNS void AS
$BODY$
BEGIN
IF is_ack_ IS TRUE THEN
  BEGIN
    INSERT INTO device(device_id, frequency, online, busy, ack)
      VALUES (device_id_, frequency_, now(), now(), now());
  EXCEPTION WHEN unique_violation THEN
    UPDATE device SET frequency = frequency_, online=now(), busy=now(), ack=now() WHERE device_id = device_id_;
  END;
ELSE
  BEGIN
    INSERT INTO device(device_id, frequency, online, busy)
      VALUES (device_id_, frequency_, now(), now());
  EXCEPTION WHEN unique_violation THEN
    UPDATE device SET frequency = frequency_, online=now(), busy=now() WHERE device_id = device_id_;
  END;
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION device_insert_or_update(bigint, integer, boolean)
  OWNER TO su;

    -- end update --
    PERFORM version_set('Ls', version_new_);
  END IF;
END$$;