-- Name: ft_upd_lnk_dose2drug(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_dose2drug() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_dose2drug (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_drug_product, fk_dose
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_drug_product, OLD.fk_dose
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_dose2drug() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_identity2ext_id(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_identity2ext_id() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_identity2ext_id (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, id_identity, external_id, fk_origin, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.id_identity, OLD.external_id, OLD.fk_origin, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_identity2ext_id() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_identity2primary_doc(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_identity2primary_doc() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_identity2primary_doc (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_identity, fk_primary_doc
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_identity, OLD.fk_primary_doc
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_identity2primary_doc() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_job2person(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_job2person() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_job2person (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_identity, fk_occupation, activities
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_identity, OLD.fk_occupation, OLD.activities
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_job2person() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_loinc2substance(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_loinc2substance() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_loinc2substance (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_substance, loinc, max_age, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_substance, OLD.loinc, OLD.max_age, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_loinc2substance() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_loinc2test_panel(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_loinc2test_panel() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_loinc2test_panel (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_test_panel, loinc
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_test_panel, OLD.loinc
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_loinc2test_panel() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_org_unit2comm(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_org_unit2comm() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_org_unit2comm (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_org_unit, url, fk_type, is_confidential, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_org_unit, OLD.url, OLD.fk_type, OLD.is_confidential, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_org_unit2comm() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_org_unit2ext_id(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_org_unit2ext_id() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_org_unit2ext_id (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_org_unit, external_id, fk_type, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_org_unit, OLD.external_id, OLD.fk_type, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_org_unit2ext_id() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_pat2vaccination_course(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_pat2vaccination_course() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_pat2vaccination_course (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_patient, fk_course
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_patient, OLD.fk_course
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_pat2vaccination_course() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_person2relative(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_person2relative() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_person2relative (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, id_identity, id_relative, id_relation_type, started, ended
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.id_identity, OLD.id_relative, OLD.id_relation_type, OLD.started, OLD.ended
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_person2relative() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_substance2brand(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_substance2brand() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_substance2brand (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_brand, fk_substance
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_brand, OLD.fk_substance
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_substance2brand() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_tst2norm(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_tst2norm() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_tst2norm (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, id_test, id_norm
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.id_test, OLD.id_norm
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_tst2norm() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_type2item(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_type2item() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_type2item (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_type, fk_item
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_type, OLD.fk_item
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_type2item() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_vaccination_course2schedule(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_vaccination_course2schedule() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_vaccination_course2schedule (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_course, fk_schedule
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_course, OLD.fk_schedule
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_vaccination_course2schedule() OWNER TO "gm-dbo";

--
-- Name: ft_upd_lnk_vaccine2inds(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_lnk_vaccine2inds() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_lnk_vaccine2inds (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, fk_vaccine, fk_indication
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.fk_vaccine, OLD.fk_indication
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_lnk_vaccine2inds() OWNER TO "gm-dbo";

--
-- Name: ft_upd_message_inbox(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_message_inbox() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_message_inbox (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_staff, fk_inbox_item_type, comment, data, importance, fk_patient, ufk_context, due_date, expiry_date
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_staff, OLD.fk_inbox_item_type, OLD.comment, OLD.data, OLD.importance, OLD.fk_patient, OLD.ufk_context, OLD.due_date, OLD.expiry_date
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_message_inbox() OWNER TO "gm-dbo";

--
-- Name: ft_upd_occupation(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_occupation() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_occupation (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, name
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.name
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_occupation() OWNER TO "gm-dbo";

--
-- Name: ft_upd_operation(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_operation() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_operation (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_audit, row_version, modified_when, modified_by, pk, fk_health_issue, fk_encounter, clin_where, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_audit, OLD.row_version, OLD.modified_when, OLD.modified_by, OLD.pk, OLD.fk_health_issue, OLD.fk_encounter, OLD.clin_where, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_operation() OWNER TO "gm-dbo";

--
-- Name: ft_upd_org(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_org() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_org (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description, fk_category, fk_data_source
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description, OLD.fk_category, OLD.fk_data_source
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_org() OWNER TO "gm-dbo";

--
-- Name: ft_upd_org_unit(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_org_unit() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_org_unit (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description, fk_org, fk_address, fk_category
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description, OLD.fk_org, OLD.fk_address, OLD.fk_category
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_org_unit() OWNER TO "gm-dbo";

--
-- Name: ft_upd_paperwork_templates(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_paperwork_templates() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_paperwork_templates (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_template_type, instance_type, name_short, name_long, external_version, gnumed_revision, engine, in_use, filename, data, edit_after_substitution
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_template_type, OLD.instance_type, OLD.name_short, OLD.name_long, OLD.external_version, OLD.gnumed_revision, OLD.engine, OLD.in_use, OLD.filename, OLD.data, OLD.edit_after_substitution
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_paperwork_templates() OWNER TO "gm-dbo";

--
-- Name: ft_upd_patient(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_patient() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_patient (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_identity, edc
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_identity, OLD.edc
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_patient() OWNER TO "gm-dbo";

--
-- Name: ft_upd_prax_geb_paid(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_prax_geb_paid() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_prax_geb_paid (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_fall, paid_amount, paid_when, paid_with
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_fall, OLD.paid_amount, OLD.paid_when, OLD.paid_with
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_prax_geb_paid() OWNER TO "gm-dbo";

--
-- Name: ft_upd_praxis_branch(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_praxis_branch() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_praxis_branch (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_org_unit
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_org_unit
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_praxis_branch() OWNER TO "gm-dbo";

--
-- Name: ft_upd_procedure(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_procedure() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_procedure (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_item, clin_when, fk_encounter, fk_episode, narrative, soap_cat, pk, fk_hospital_stay, clin_end, is_ongoing, fk_org_unit, fk_doc, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_item, OLD.clin_when, OLD.fk_encounter, OLD.fk_episode, OLD.narrative, OLD.soap_cat, OLD.pk, OLD.fk_hospital_stay, OLD.clin_end, OLD.is_ongoing, OLD.fk_org_unit, OLD.fk_doc, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_procedure() OWNER TO "gm-dbo";

--
-- Name: ft_upd_ref_source(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_ref_source() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_ref_source (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_audit, row_version, modified_when, modified_by, pk, name_short, name_long, version, description, source
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_audit, OLD.row_version, OLD.modified_when, OLD.modified_by, OLD.pk, OLD.name_short, OLD.name_long, OLD.version, OLD.description, OLD.source
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_ref_source() OWNER TO "gm-dbo";

--
-- Name: ft_upd_referral(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_referral() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_referral (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_item, clin_when, fk_encounter, fk_episode, narrative, soap_cat, id, fk_referee, fk_form
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_item, OLD.clin_when, OLD.fk_encounter, OLD.fk_episode, OLD.narrative, OLD.soap_cat, OLD.id, OLD.fk_referee, OLD.fk_form
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_referral() OWNER TO "gm-dbo";

--
-- Name: ft_upd_region(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_region() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_region (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, code, country, name
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.code, OLD.country, OLD.name
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_region() OWNER TO "gm-dbo";

--
-- Name: ft_upd_relation_types(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_relation_types() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_relation_types (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, inverse, biological, biol_verified, description
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.inverse, OLD.biological, OLD.biol_verified, OLD.description
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_relation_types() OWNER TO "gm-dbo";

--
-- Name: ft_upd_report_query(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_report_query() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_report_query (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, label, cmd
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.label, OLD.cmd
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_report_query() OWNER TO "gm-dbo";

--
-- Name: ft_upd_staff(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_staff() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_staff (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_identity, db_user, short_alias, comment, is_active
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_identity, OLD.db_user, OLD.short_alias, OLD.comment, OLD.is_active
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_staff() OWNER TO "gm-dbo";

--
-- Name: ft_upd_staff_role(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_staff_role() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_staff_role (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_audit, row_version, modified_when, modified_by, pk, name, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_audit, OLD.row_version, OLD.modified_when, OLD.modified_by, OLD.pk, OLD.name, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_staff_role() OWNER TO "gm-dbo";

--
-- Name: ft_upd_street(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_street() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_street (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, id_urb, name, postcode, suburb, lat_lon
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.id_urb, OLD.name, OLD.postcode, OLD.suburb, OLD.lat_lon
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_street() OWNER TO "gm-dbo";

--
-- Name: ft_upd_substance(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_substance() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_substance (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description, atc, intake_instructions
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description, OLD.atc, OLD.intake_instructions
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_substance() OWNER TO "gm-dbo";

--
-- Name: ft_upd_substance_in_brand(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_substance_in_brand() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_substance_in_brand (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_audit, row_version, modified_when, modified_by, pk, fk_brand, description, atc_code
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_audit, OLD.row_version, OLD.modified_when, OLD.modified_by, OLD.pk, OLD.fk_brand, OLD.description, OLD.atc_code
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_substance_in_brand() OWNER TO "gm-dbo";

--
-- Name: ft_upd_substance_intake(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_substance_intake() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_substance_intake (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_item, clin_when, fk_encounter, fk_episode, narrative, soap_cat, pk, schedule, aim, duration, intake_is_approved_of, is_long_term, discontinued, discontinue_reason, fk_drug_component, comment_on_start, harmful_use_type
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_item, OLD.clin_when, OLD.fk_encounter, OLD.fk_episode, OLD.narrative, OLD.soap_cat, OLD.pk, OLD.schedule, OLD.aim, OLD.duration, OLD.intake_is_approved_of, OLD.is_long_term, OLD.discontinued, OLD.discontinue_reason, OLD.fk_drug_component, OLD.comment_on_start, OLD.harmful_use_type
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_substance_intake() OWNER TO "gm-dbo";

--
-- Name: ft_upd_suppressed_hint(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_suppressed_hint() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_suppressed_hint (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_encounter, fk_hint, suppressed_by, suppressed_when, rationale, md5_sum
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_encounter, OLD.fk_hint, OLD.suppressed_by, OLD.suppressed_when, OLD.rationale, OLD.md5_sum
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_suppressed_hint() OWNER TO "gm-dbo";

--
-- Name: ft_upd_tag_image(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_tag_image() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_tag_image (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description, filename, image
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description, OLD.filename, OLD.image
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_tag_image() OWNER TO "gm-dbo";

--
-- Name: ft_upd_test_org(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_test_org() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_test_org (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_org_unit, fk_adm_contact, fk_med_contact, comment, contact
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_org_unit, OLD.fk_adm_contact, OLD.fk_med_contact, OLD.comment, OLD.contact
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_test_org() OWNER TO "gm-dbo";

--
-- Name: ft_upd_test_panel(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_test_panel() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_test_panel (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_test_panel() OWNER TO "gm-dbo";

--
-- Name: ft_upd_test_result(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_test_result() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_test_result (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_item, clin_when, fk_encounter, fk_episode, narrative, soap_cat, pk, fk_type, val_num, val_alpha, val_unit, val_normal_min, val_normal_max, val_normal_range, val_target_min, val_target_max, val_target_range, abnormality_indicator, norm_ref_group, note_test_org, material, material_detail, fk_intended_reviewer, fk_request, status, source_data, val_grouping
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_item, OLD.clin_when, OLD.fk_encounter, OLD.fk_episode, OLD.narrative, OLD.soap_cat, OLD.pk, OLD.fk_type, OLD.val_num, OLD.val_alpha, OLD.val_unit, OLD.val_normal_min, OLD.val_normal_max, OLD.val_normal_range, OLD.val_target_min, OLD.val_target_max, OLD.val_target_range, OLD.abnormality_indicator, OLD.norm_ref_group, OLD.note_test_org, OLD.material, OLD.material_detail, OLD.fk_intended_reviewer, OLD.fk_request, OLD.status, OLD.source_data, OLD.val_grouping
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_test_result() OWNER TO "gm-dbo";

--
-- Name: ft_upd_test_type(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_test_type() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_test_type (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_test_org, name, comment, reference_unit, loinc, abbrev, fk_meta_test_type
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_test_org, OLD.name, OLD.comment, OLD.reference_unit, OLD.loinc, OLD.abbrev, OLD.fk_meta_test_type
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_test_type() OWNER TO "gm-dbo";

--
-- Name: ft_upd_urb(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_urb() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_urb (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, fk_region, postcode, lat_lon, name
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.fk_region, OLD.postcode, OLD.lat_lon, OLD.name
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_urb() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vacc_indication(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vacc_indication() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vacc_indication (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, description, atcs_single_indication, atcs_combi_indication
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.description, OLD.atcs_single_indication, OLD.atcs_combi_indication
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vacc_indication() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vacc_route(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vacc_route() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vacc_route (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, abbreviation, description
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.abbreviation, OLD.description
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vacc_route() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccination(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccination() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccination (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_item, clin_when, fk_encounter, fk_episode, narrative, soap_cat, pk, fk_provider, fk_vaccine, site, batch_no, reaction
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_item, OLD.clin_when, OLD.fk_encounter, OLD.fk_episode, OLD.narrative, OLD.soap_cat, OLD.pk, OLD.fk_provider, OLD.fk_vaccine, OLD.site, OLD.batch_no, OLD.reaction
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccination() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccination_course(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccination_course() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccination_course (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_recommended_by, fk_indication, is_active, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_recommended_by, OLD.fk_indication, OLD.is_active, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccination_course() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccination_course_constraint(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccination_course_constraint() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccination_course_constraint (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, description
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.description
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccination_course_constraint() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccination_definition(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccination_definition() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccination_definition (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		id, fk_course, is_booster, seq_no, min_age_due, max_age_due, min_interval, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.id, OLD.fk_course, OLD.is_booster, OLD.seq_no, OLD.min_age_due, OLD.max_age_due, OLD.min_interval, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccination_definition() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccination_schedule(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccination_schedule() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccination_schedule (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, name, comment
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.name, OLD.comment
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccination_schedule() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccine(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccine() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccine (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, id_route, is_live, min_age, max_age, comment, fk_drug_product
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.id_route, OLD.is_live, OLD.min_age, OLD.max_age, OLD.comment, OLD.fk_drug_product
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccine() OWNER TO "gm-dbo";

--
-- Name: ft_upd_vaccine_batches(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_vaccine_batches() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_vaccine_batches (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_vaccine, batch_no
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_vaccine, OLD.batch_no
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_vaccine_batches() OWNER TO "gm-dbo";

--
-- Name: ft_upd_waiting_list(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_waiting_list() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	_is_allowed_updater boolean;
BEGIN
	-- is the session user allowed to update data ?
	SELECT gm.account_is_dbowner_or_staff(SESSION_USER) INTO STRICT _is_allowed_updater;
	IF _is_allowed_updater IS FALSE THEN
		RAISE EXCEPTION
			'UPDATE: gm.account_is_dbowner_or_staff(NAME): <%> is neither database owner, nor <postgres>, nor on staff', SESSION_USER
			USING ERRCODE = 'integrity_constraint_violation'
		;
		return NEW;
	END IF;
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_waiting_list (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk, fk_patient, registered, urgency, list_position, comment, area
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk, OLD.fk_patient, OLD.registered, OLD.urgency, OLD.list_position, OLD.comment, OLD.area
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_waiting_list() OWNER TO "gm-dbo";

--
-- Name: ft_upd_xlnk_identity(); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.ft_upd_xlnk_identity() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	NEW.row_version := OLD.row_version + 1;
	NEW.modified_when := CURRENT_TIMESTAMP;
	NEW.modified_by := SESSION_USER;
	INSERT INTO audit.log_xlnk_identity (
		orig_version, orig_when, orig_by, orig_tableoid, audit_action,
		pk_audit, row_version, modified_when, modified_by, pk, xfk_identity, pupic, data
	) VALUES (
		OLD.row_version, OLD.modified_when, OLD.modified_by, TG_RELID, TG_OP,
		OLD.pk_audit, OLD.row_version, OLD.modified_when, OLD.modified_by, OLD.pk, OLD.xfk_identity, OLD.pupic, OLD.data
	);
	return NEW;
END;$$;


ALTER FUNCTION audit.ft_upd_xlnk_identity() OWNER TO "gm-dbo";

--
-- Name: register_table_for_auditing(name, name); Type: FUNCTION; Schema: audit; Owner: gm-dbo
--

CREATE FUNCTION audit.register_table_for_auditing(name, name) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_schema alias for $1;
	_table alias for $2;
BEGIN
	-- does table exist ?
	perform 1 from pg_class where
		relname = _table
			and
		relnamespace = (select oid from pg_namespace where nspname = _schema)
	;
	if not found then
		raise exception 'audit.register_table_for_auditing(): table [%.%] does not exist', _schema, _table;
		return false;
	end if;
	-- already queued for auditing ?
	perform 1 from audit.audited_tables where
		table_name = _table
			and
		schema = _schema;
	if found then
		return true;
	end if;
	-- add definition
	insert into audit.audited_tables (
		schema, table_name
	) values (
		_schema, _table
	);
	return true;
END;$_$;


ALTER FUNCTION audit.register_table_for_auditing(name, name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION register_table_for_auditing(name, name); Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON FUNCTION audit.register_table_for_auditing(name, name) IS 'sanity-checking convenience function for registering a table for auditing';


--
-- Name: get_bill_receiver_identity(integer); Type: FUNCTION; Schema: bill; Owner: gm-dbo
--

CREATE FUNCTION bill.get_bill_receiver_identity(integer) RETURNS integer
    LANGUAGE sql
    AS $_$
select
	value
from (
	select
		id.pk_id,
		id.value::integer
	from
		dem.v_external_ids4identity id
			join dem.identity d_i on (id.value = d_i.pk::text)
	where
		id.pk_type = (select pk from dem.enum_ext_id_types where name = 'bill receiver' and issuer = 'GNUmed')
			and
		id.pk_identity = $1
	union all
	select
		0,
		$1
) me
limit 1;$_$;


ALTER FUNCTION bill.get_bill_receiver_identity(integer) OWNER TO "gm-dbo";

--
-- Name: trf_prevent_empty_bills(); Type: FUNCTION; Schema: bill; Owner: gm-dbo
--

CREATE FUNCTION bill.trf_prevent_empty_bills() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_item_count integer;
	_msg text;
BEGIN
	if TG_OP = 'UPDATE' then
		if OLD.fk_bill IS NULL then
			return NULL;
		end if;
		if OLD.fk_bill IS NOT DISTINCT FROM NEW.fk_bill then
			return NULL;
		end if;
	else
		if OLD.fk_bill is NULL then
			return NULL;
		end if;
	end if;
	-- we now either:
	--	DELETE with .fk_bill NOT NULL
	-- or:
	--	UPDATE with an .fk_bill change (including towards fk_bill = NULL)
	-- let us check whether the (previous) bill still exists
	-- at all or whether we are deleting the bill (and thereby
	-- setting our .fk_bill to NULL)
	-- only works at or below REPEATABLE READ after deletion of bill
	perform 1 from bill.bill where pk = OLD.fk_bill;
	if FOUND is FALSE then
		return NULL;
	end if;
	select count(1) into _item_count
	from bill.bill_item
	where
		fk_bill = OLD.fk_bill
			and
		pk != OLD.pk;
	if _item_count > 0 then
		return NULL;
	end if;
	_msg := '[bill.trf_prevent_empty_bills]: cannot remove (by '
			|| '<' || TG_OP || '>'
			||') the only item (bill.bill_item.pk='
			|| coalesce(OLD.pk::text, '<NULL>'::text)
			|| ') from bill (bill.bill_item.fk_bill=bill.bill.pk='
			|| coalesce(OLD.fk_bill::text, '<NULL>'::text)
			|| ') ';
	raise exception unique_violation using message = _msg;
	return NULL;
END;$$;


ALTER FUNCTION bill.trf_prevent_empty_bills() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_prevent_empty_bills(); Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON FUNCTION bill.trf_prevent_empty_bills() IS 'Prevent bills to become void of items due to deletions/updates of bill items.';


--
-- Name: trf_prevent_mislinked_bills(); Type: FUNCTION; Schema: bill; Owner: gm-dbo
--

CREATE FUNCTION bill.trf_prevent_mislinked_bills() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_doc_patient integer;
	_bill_patient integer;
	_msg text;
BEGIN
	if NEW.fk_doc IS NULL then
		return NEW;
	end if;
	if TG_OP = 'UPDATE' then
		if OLD.fk_doc IS NOT DISTINCT FROM NEW.fk_doc then
			return NEW;
		end if;
	end if;
	-- we now either:
	--	INSERT with .fk_doc NOT NULL
	-- or:
	--	UPDATE with an .fk_bill change to a NON-NULL value
	select pk_patient into _doc_patient
	from blobs.v_doc_med
	where
		pk_doc = NEW.fk_doc;
	select pk_patient into _bill_patient
	from bill.v_bills
	where
		pk_bill = NEW.pk;
	if _doc_patient = _bill_patient then
		return NEW;
	end if;
	_msg := '[bill.trf_prevent_mislinked_bills]: patient mismatch between '
		|| 'bill (pk=' || NEW.pk || ', patient=' || _bill_patient || ') '
		|| 'and invoice (pk=' || NEW.fk_doc || ', patient=' || _doc_patient || ')';
	raise exception integrity_constraint_violation using message = _msg;
	return NULL;
END;$$;


ALTER FUNCTION bill.trf_prevent_mislinked_bills() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_prevent_mislinked_bills(); Type: COMMENT; Schema: bill; Owner: gm-dbo
--

COMMENT ON FUNCTION bill.trf_prevent_mislinked_bills() IS 'Prevent bills to link to invoices of another patient.';


--
-- Name: delete_document(integer, integer); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.delete_document(integer, integer) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_pk_doc alias for $1;
	_pk_encounter alias for $2;
	_del_note text;
	_doc_row record;
	_obj_row record;
	tmp text;
BEGIN
	select * into _doc_row from blobs.doc_med where pk = _pk_doc;
	_del_note := _('Deletion of document') || E':
'
		|| ' '
			|| to_char(_doc_row.clin_when, 'YYYY-MM-DD HH24:MI')
			|| ' "' || (select _(dt.name) from blobs.doc_type dt where pk = _doc_row.fk_type) || '"'
			|| coalesce(' (' || _doc_row.ext_ref || ')', '')
		|| coalesce(E'
 ' || _doc_row.comment, '')
	;
	FOR _obj_row IN select * from blobs.doc_obj where fk_doc = _pk_doc order by seq_idx LOOP
		_del_note := _del_note || E'
'
			|| ' #' || coalesce(_obj_row.seq_idx, '-1') || ': "' || coalesce(_obj_row.comment, '') || E'"
'
			|| ' ' || coalesce(_obj_row.filename, '') || E'
';
	end LOOP;
	insert into clin.clin_narrative
		(fk_encounter, fk_episode, narrative, soap_cat)
	values (
		_pk_encounter,
		_doc_row.fk_episode,
		_del_note,
		NULL
	);
	-- should auto-cascade:
	--delete from blobs.doc_obj where fk_doc = _pk_doc;
	-- should auto-NULL:
	--bill.bill.fk_doc
	delete from blobs.doc_med where pk = _pk_doc;
	return True;
END;$_$;


ALTER FUNCTION blobs.delete_document(integer, integer) OWNER TO "gm-dbo";

--
-- Name: delete_document_part(integer, integer); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.delete_document_part(integer, integer) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_pk_doc_part alias for $1;
	_pk_encounter alias for $2;
	_del_note text;
	_doc_row record;
	_obj_row record;
	tmp text;
BEGIN
	select * into _obj_row from blobs.doc_obj where pk = _pk_doc_part;
	select * into _doc_row from blobs.doc_med where pk = _obj_row.fk_doc;
	_del_note := _('Deletion of part from document') || E':
'
		|| ' '
			|| to_char(_doc_row.clin_when, 'YYYY-MM-DD HH24:MI')
			|| ' "' || (select _(dt.name) from blobs.doc_type dt where pk = _doc_row.fk_type) || '"'
			|| coalesce(' (' || _doc_row.ext_ref || ')', '')
		|| coalesce(E'
 ' || _doc_row.comment, '')
		|| E'
'
		|| ' #' || coalesce(_obj_row.seq_idx, '-1') || ': "' || coalesce(_obj_row.comment, '') || E'"
'
		|| ' ' || coalesce(_obj_row.filename, '') || E'
'
	;
	insert into clin.clin_narrative
		(fk_encounter, fk_episode, narrative, soap_cat)
	values (
		_pk_encounter,
		_doc_row.fk_episode,
		_del_note,
		NULL
	);
	delete from blobs.doc_obj where pk = _pk_doc_part;
	return True;
END;$_$;


ALTER FUNCTION blobs.delete_document_part(integer, integer) OWNER TO "gm-dbo";

--
-- Name: trf_do_not_duplicate_primary_episode_in_link_table(); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.trf_do_not_duplicate_primary_episode_in_link_table() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- if already in doc_med
	perform 1 from blobs.doc_med dm where dm.fk_episode = NEW.fk_episode and dm.pk = NEW.fk_doc_med;
	if FOUND then
		-- skip the insert/update
		return null;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION blobs.trf_do_not_duplicate_primary_episode_in_link_table() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_do_not_duplicate_primary_episode_in_link_table(); Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON FUNCTION blobs.trf_do_not_duplicate_primary_episode_in_link_table() IS 'This trigger function is called before a lnk_doc_med2episode
	 row is inserted or updated. It makes sure the episode does
	 not duplicate the primary episode for this document listed
	 in doc_med. If it does the insert/update is skipped.';


--
-- Name: trf_mark_unreviewed_on_doc_obj_update(); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.trf_mark_unreviewed_on_doc_obj_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if (NEW.data != OLD.data) or ((NEW.data != OLD.data) is NULL) then
		delete from blobs.reviewed_doc_objs where fk_reviewed_row = OLD.pk;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION blobs.trf_mark_unreviewed_on_doc_obj_update() OWNER TO "gm-dbo";

--
-- Name: trf_remove_primary_episode_from_link_table(); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.trf_remove_primary_episode_from_link_table() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- if update
	if TG_OP = 'UPDATE' then
		-- and no change
		if NEW.fk_episode = OLD.fk_episode then
			-- then do nothing
			return NEW;
		end if;
	end if;
	-- if already in link table
	perform 1 from blobs.lnk_doc_med2episode ldm2e where ldm2e.fk_episode = NEW.fk_episode and ldm2e.fk_doc_med = NEW.pk;
	if FOUND then
		-- delete from link table
		delete from blobs.lnk_doc_med2episode where fk_episode = NEW.fk_episode and fk_doc_med = NEW.pk;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION blobs.trf_remove_primary_episode_from_link_table() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_remove_primary_episode_from_link_table(); Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON FUNCTION blobs.trf_remove_primary_episode_from_link_table() IS 'This trigger function is called when a doc_med row
	 is inserted or updated. It makes sure the primary
	 episode listed in doc_med is not duplicated in
	 lnk_doc_med2episode for the same document. If it
	 exists in the latter it is removed from there.';


--
-- Name: trf_set_intended_reviewer(); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.trf_set_intended_reviewer() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_patient integer;
	_pk_provider integer;
BEGIN
	-- explicitely set ?
	if NEW.fk_intended_reviewer is not NULL then
		return NEW;
	end if;
	-- find patient via document
	select
		fk_patient into _pk_patient
	from
		clin.encounter
	where
		clin.encounter.pk = (
			select fk_encounter from blobs.doc_med where pk = NEW.fk_doc
		);
	-- does patient have primary provider ?
	select
		fk_primary_provider into _pk_provider
	from
		dem.identity
	where
		dem.identity.pk = _pk_patient;
	if _pk_provider is not NULL then
		NEW.fk_intended_reviewer := _pk_provider;
		return NEW;
	end if;
	-- else use CURRENT_USER
	select
		pk into _pk_provider
	from
		dem.staff
	where
		dem.staff.db_user = current_user;
	NEW.fk_intended_reviewer := _pk_provider;
	return NEW;
END;$$;


ALTER FUNCTION blobs.trf_set_intended_reviewer() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_set_intended_reviewer(); Type: COMMENT; Schema: blobs; Owner: gm-dbo
--

COMMENT ON FUNCTION blobs.trf_set_intended_reviewer() IS 'Set the default on blobs.doc_obj.fk_intended_reviewer.';


--
-- Name: trf_verify_page_number(); Type: FUNCTION; Schema: blobs; Owner: gm-dbo
--

CREATE FUNCTION blobs.trf_verify_page_number() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	msg text;
begin
	if NEW.seq_idx is NULL then
		return NEW;
	end if;
	perform 1 from blobs.doc_obj where pk <> NEW.pk and fk_doc = NEW.fk_doc and seq_idx = NEW.seq_idx;
	if FOUND then
		msg := '[blobs.trf_verify_page_number]: uniqueness violation: seq_idx [' || NEW.seq_idx || '] already exists for fk_doc [' || NEW.fk_doc || ']';
		raise exception '%', msg;
	end if;
	return NEW;
end;$$;


ALTER FUNCTION blobs.trf_verify_page_number() OWNER TO "gm-dbo";

--
-- Name: create_cfg_item(text, text, text, text, text); Type: FUNCTION; Schema: cfg; Owner: gm-dbo
--

CREATE FUNCTION cfg.create_cfg_item(text, text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
declare
	_option alias for $1;
	_val_type alias for $2;
	_workplace alias for $3;
	_cookie alias for $4;
	_owner alias for $5;
	real_owner text;
	pk_template integer;
	pk_item integer;
begin
	-- check template
	select into pk_template pk from cfg.cfg_template where name = _option and type = _val_type;
	if not FOUND then
		insert into cfg.cfg_template (name, type) values (_option, _val_type);
		select into pk_template currval('cfg.cfg_template_pk_seq');
	end if;
	if _owner is null then
		select into real_owner CURRENT_USER;
	else
		real_owner := _owner;
	end if;
	-- check item
	if _cookie is NULL then
		select into pk_item pk from cfg.cfg_item where
			fk_template = pk_template and
			owner = real_owner and
			workplace = _workplace and
			cookie is null;
	else
		select into pk_item pk from cfg.cfg_item where
			fk_template = pk_template and
			owner = real_owner and
			workplace = _workplace and
			cookie = _cookie;
	end if;
	if FOUND then
		return pk_item;
	end if;
	insert into cfg.cfg_item (
		fk_template, workplace, cookie, owner
	) values (
		pk_template,
		_workplace,
		_cookie,
		real_owner
	);
	select into pk_item currval('cfg.cfg_item_pk_seq');
	return pk_item;
end;$_$;


ALTER FUNCTION cfg.create_cfg_item(text, text, text, text, text) OWNER TO "gm-dbo";

--
-- Name: set_option(text, anyelement, text, text, text); Type: FUNCTION; Schema: cfg; Owner: gm-dbo
--

CREATE FUNCTION cfg.set_option(text, anyelement, text, text, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
	_option alias for $1;
	_value alias for $2;
	_workplace alias for $3;
	_cookie alias for $4;
	_owner alias for $5;
	val_type text;
	pk_item integer;
	rows integer;
	cmd text;
begin
	-- determine data type
	if _value is of (text, char, varchar, name) then
		val_type := 'string';
	elsif _value is of (smallint, integer, bigint, numeric, boolean) then
		val_type := 'numeric';
	elsif _value is of (bytea) then
		val_type := 'data';
	elsif _value is of (text[]) then
		val_type := 'str_array';
	else
		raise exception 'cfg.set_option(text, any, text, text, text): invalid type of value';
	end if;
	-- create template/item if need be
	select into pk_item cfg.create_cfg_item(_option, val_type, _workplace, _cookie, _owner);
	-- set item value
	cmd := 'select 1 from cfg.cfg_' || val_type || ' where fk_item=' || pk_item || ';';
	execute cmd;
	get diagnostics rows = row_count;
	found := rows <> 0;
	if FOUND then
		if val_type = 'str_array' then
			cmd := 'update cfg.cfg_str_array set value=''{"' || array_to_string(_value, '","') || '"}'' where fk_item=' || pk_item || ';';
		elsif val_type = 'data' then
			cmd := 'update cfg.cfg_data set value=''' || encode(_value, 'escape') || ''' where fk_item=' || pk_item || ';';
		else
			cmd := 'update cfg.cfg_' || val_type || ' set value=' || quote_literal(_value) || ' where fk_item=' || pk_item || ';';
		end if;
		execute cmd;
		return True;
	end if;
	if val_type = 'str_array' then
		cmd := 'insert into cfg.cfg_str_array(fk_item, value) values (' || pk_item || ', ''{"' || array_to_string(_value, '","') || '"}'');';
	elsif val_type = 'data' then
		cmd := 'insert into cfg.cfg_data(fk_item, value) values (' || pk_item || ', ''' || encode(_value, 'escape') || ''');';
	else
		cmd := 'insert into cfg.cfg_' || val_type || ' (fk_item, value) values (' || pk_item || ', ' || quote_literal(_value) || ');';
	end if;
	execute cmd;
	return True;
end;$_$;


ALTER FUNCTION cfg.set_option(text, anyelement, text, text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION set_option(text, anyelement, text, text, text); Type: COMMENT; Schema: cfg; Owner: gm-dbo
--

COMMENT ON FUNCTION cfg.set_option(text, anyelement, text, text, text) IS 'set option, owner = NULL means CURRENT_USER';


--
-- Name: _get_recommendation_for_patient_hint(text, integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin._get_recommendation_for_patient_hint(text, integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_raw_query ALIAS FOR $1;
	_pk_identity ALIAS FOR $2;
	_query text;
	_recommendation text;
BEGIN
	IF _raw_query IS NULL THEN
		RETURN NULL::text;
	END IF;
	_query := replace(_raw_query, 'ID_ACTIVE_PATIENT', _pk_identity::text);
	BEGIN
		EXECUTE _query INTO STRICT _recommendation;
	EXCEPTION
		--WHEN insufficient_privilege THEN RAISE WARNING 'auto hint recommendation query failed: %', _query;
		WHEN others THEN
			RAISE WARNING 'auto hint recommendation query failed: %', _query;
			-- only available starting with PG 9.2:
			--GET STACKED DIAGNOSTICS
			--	_exc_state = RETURNED_SQLSTATE,
			--	_exc_msg = MESSAGE_TEXT,
			--	_exc_detail = PG_EXCEPTION_DETAIL,
			--	_exc_hint = PG_EXCEPTION_HINT,
			--	_exc_context = PG_EXCEPTION_CONTEXT;
			--RAISE WARNING 'SQL STATE: %', _exc_state;
			--RAISE WARNING 'MESSAGE: %', _exc_msg;
			--RAISE WARNING 'DETAIL: %', _exc_detail;
			--RAISE WARNING 'HINT: %', _exc_hint;
			--RAISE WARNING 'CONTEXT: %', _exc_context;
			-- workaround for 9.1:
			RAISE WARNING 'SQL STATE: %', SQLSTATE;
			RAISE WARNING 'MESSAGE: %', SQLERRM;
			_recommendation := 'ERROR running hint recommendation query [' || _query || ']';
	END;
	RETURN _recommendation;
END;$_$;


ALTER FUNCTION clin._get_recommendation_for_patient_hint(text, integer) OWNER TO "gm-dbo";

--
-- Name: add_coded_phrase(text, text, text); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.add_coded_phrase(text, text, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
	_term alias for $1;
	_code alias for $2;
	_system alias for $3;
	_tmp text;
begin
	select into _tmp 1 from clin.coded_phrase where
		term = _term
		and code = _code
		and xfk_coding_system = _system;
	if found then
		return True;
	end if;
	insert into clin.coded_phrase (term, code, xfk_coding_system)
		values (_term, _code, _system);
	return True;
end;$_$;


ALTER FUNCTION clin.add_coded_phrase(text, text, text) OWNER TO "gm-dbo";

--
-- Name: f_del_booster_must_have_base_immunity(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.f_del_booster_must_have_base_immunity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	msg text;
BEGIN
	-- do not worry about booster deletes
	if OLD.is_booster then
		return null;
	end if;
	-- any non-booster rows left ?
	perform 1 from clin.vaccination_definition where fk_course = OLD.fk_course and seq_no is not null;
	if FOUND then
		return null;
	end if;
	-- *any* rows left ?
	perform 1 from clin.vaccination_definition where fk_course = OLD.fk_course;
	if not FOUND then
		-- no problem
		return null;
	end if;
	-- any remaining rows can only be booster rows - which is a problem
	msg := 'Cannot delete last non-booster vacc def [' || OLD.pk || '] from course [' || OLD.fk_course || ']. There would be only booster definitions left.';
	raise exception '%', msg;
	return null;
END;$$;


ALTER FUNCTION clin.f_del_booster_must_have_base_immunity() OWNER TO "gm-dbo";

--
-- Name: f_fk_reviewer_default(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.f_fk_reviewer_default() RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
	_pk_staff integer;
begin
	select pk into _pk_staff from dem.staff where db_user = current_user;
	return _pk_staff;
end;$$;


ALTER FUNCTION clin.f_fk_reviewer_default() OWNER TO "gm-dbo";

--
-- Name: f_ins_booster_must_have_base_immunity(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.f_ins_booster_must_have_base_immunity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- do not worry about non-booster inserts
	if NEW.is_booster is false then
		return NEW;
	end if;
	-- only insert booster def if non-booster def exists
	perform 1 from clin.vaccination_definition where fk_course = NEW.fk_course and seq_no is not null;
	if FOUND then
		return NEW;
	end if;
	raise exception 'Cannot define booster shot for course [%]. There is no base immunization definition.', NEW.fk_course;
	return null;
END;
$$;


ALTER FUNCTION clin.f_ins_booster_must_have_base_immunity() OWNER TO "gm-dbo";

--
-- Name: f_protect_clin_root_item(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.f_protect_clin_root_item() RETURNS boolean
    LANGUAGE plpgsql
    AS $$
begin
	raise exception 'INSERT/DELETE on <clin_root_item> not allowed.';
	return False;
end;
$$;


ALTER FUNCTION clin.f_protect_clin_root_item() OWNER TO "gm-dbo";

--
-- Name: FUNCTION f_protect_clin_root_item(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.f_protect_clin_root_item() IS 'protect from direct inserts/deletes which the 
	 inheritance system cannot handle properly';


--
-- Name: f_upd_booster_must_have_base_immunity(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.f_upd_booster_must_have_base_immunity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	msg text;
BEGIN
	-- do not worry about non-booster updates
	if NEW.is_booster is false then
		return null;
	end if;
	-- after update to booster still non-booster def available ?
	perform 1 from clin.vaccination_definition where fk_course = NEW.fk_course and seq_no is not null;
	if FOUND then
		return null;
	end if;
	msg := 'Cannot set vacc def [' || NEW.pk || '] to booster for course [' || NEW.fk_course || ']. There would be no base immunization definition left.';
	raise exception '%', msg;
	return null;
END;$$;


ALTER FUNCTION clin.f_upd_booster_must_have_base_immunity() OWNER TO "gm-dbo";

--
-- Name: get_dob(integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.get_dob(integer) RETURNS timestamp with time zone
    LANGUAGE sql
    AS $_$
		select date_trunc('day'::text, d_i.dob) + COALESCE(d_i.tob, d_i.dob::time without time zone)::interval AS dob
		from dem.identity d_i
		where d_i.pk = $1;
	$_$;


ALTER FUNCTION clin.get_dob(integer) OWNER TO "gm-dbo";

--
-- Name: get_dod(integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.get_dod(integer) RETURNS timestamp with time zone
    LANGUAGE sql
    AS $_$select deceased from dem.identity d_i where d_i.pk = $1;$_$;


ALTER FUNCTION clin.get_dod(integer) OWNER TO "gm-dbo";

--
-- Name: is_null_or_blank_string(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.is_null_or_blank_string(text) RETURNS boolean
    LANGUAGE sql
    AS $_$select (coalesce(trim($1), '') = '');$_$;


ALTER FUNCTION gm.is_null_or_blank_string(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION is_null_or_blank_string(text); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.is_null_or_blank_string(text) IS 'input is either NULL or empty string -> True; 
input is not NULL and not empty -> FALSE';


--
-- Name: is_null_or_non_empty_string(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.is_null_or_non_empty_string(text) RETURNS boolean
    LANGUAGE sql
    AS $_$select (coalesce(trim($1), 'NULL') != '');$_$;


ALTER FUNCTION gm.is_null_or_non_empty_string(text) OWNER TO "gm-dbo";



--
-- Name: audit_fields; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.audit_fields (
    pk_audit integer NOT NULL,
    row_version integer DEFAULT 0 NOT NULL,
    modified_when timestamp with time zone DEFAULT now() NOT NULL,
    modified_by name DEFAULT "current_user"() NOT NULL,
    CONSTRAINT audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
);


ALTER TABLE audit.audit_fields OWNER TO "gm-dbo";

--
-- Name: TABLE audit_fields; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON TABLE audit.audit_fields IS 'this table holds all the fields needed for auditing';


--
-- Name: COLUMN audit_fields.row_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_fields.row_version IS 'the version of the row; mainly just a count';


--
-- Name: COLUMN audit_fields.modified_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_fields.modified_when IS 'when has this row been committed (created/modified)';


--
-- Name: COLUMN audit_fields.modified_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_fields.modified_by IS 'by whom has this row been committed (created/modified)';


--
-- Name: auto_hint; Type: TABLE; Schema: ref; Owner: gm-dbo
--

CREATE TABLE ref.auto_hint (
    pk integer NOT NULL,
    query text,
    title text,
    hint text,
    url text,
    is_active boolean DEFAULT true NOT NULL,
    source text,
    lang text,
    recommendation_query text,
    popup_type integer DEFAULT 1 NOT NULL,
    highlight_as_priority boolean DEFAULT true NOT NULL,
    CONSTRAINT ref_auto_hint_sane_hint CHECK ((gm.is_null_or_blank_string(hint) IS FALSE)),
    CONSTRAINT ref_auto_hint_sane_lang CHECK ((gm.is_null_or_blank_string(lang) IS FALSE)),
    CONSTRAINT ref_auto_hint_sane_popup_type CHECK (((popup_type > '-1'::integer) AND (popup_type < 3))),
    CONSTRAINT ref_auto_hint_sane_query CHECK ((gm.is_null_or_blank_string(query) IS FALSE)),
    CONSTRAINT ref_auto_hint_sane_rec_query CHECK (gm.is_null_or_non_empty_string(recommendation_query)),
    CONSTRAINT ref_auto_hint_sane_source CHECK ((gm.is_null_or_blank_string(source) IS FALSE)),
    CONSTRAINT ref_auto_hint_sane_title CHECK ((gm.is_null_or_blank_string(title) IS FALSE)),
    CONSTRAINT ref_auto_hint_sane_url CHECK (gm.is_null_or_non_empty_string(url))
)
INHERITS (audit.audit_fields);


ALTER TABLE ref.auto_hint OWNER TO "gm-dbo";

--
-- Name: TABLE auto_hint; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON TABLE ref.auto_hint IS 'This table stores SQL queries and the associated hints. If the query returns TRUE the client should display the hint.';


--
-- Name: COLUMN auto_hint.query; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.query IS 'This query is run against the database.';


--
-- Name: COLUMN auto_hint.title; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.title IS 'A short title to summarize and identify the hint.';


--
-- Name: COLUMN auto_hint.hint; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.hint IS 'When the query returns true this is the hint that should be displayed.';


--
-- Name: COLUMN auto_hint.url; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.url IS 'An URL relevant to the hint.';


--
-- Name: COLUMN auto_hint.is_active; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.is_active IS 'Whether or not this query/hint is active.';


--
-- Name: COLUMN auto_hint.source; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.source IS 'Who provided query and hint.';


--
-- Name: COLUMN auto_hint.lang; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.lang IS 'The language the hint is written in.';


--
-- Name: COLUMN auto_hint.popup_type; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.popup_type IS '0: do not include in any popups (= 0 popups); 1: show an individual popup for this hint (= 1 hint per popup), 2: include in list popups only (= 2 or more hints in popup)';


--
-- Name: COLUMN auto_hint.highlight_as_priority; Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON COLUMN ref.auto_hint.highlight_as_priority IS 'Whether or not user wants this hint highlighted (possibly among a list of displayed hints), the way of highlighting is up to the application.';


--
-- Name: v_auto_hints; Type: VIEW; Schema: ref; Owner: gm-dbo
--

CREATE VIEW ref.v_auto_hints AS
 SELECT auto_hint.pk AS pk_auto_hint,
    auto_hint.query,
    auto_hint.recommendation_query,
    auto_hint.title,
    auto_hint.hint,
    auto_hint.url,
    auto_hint.is_active,
    auto_hint.source,
    auto_hint.lang,
    auto_hint.popup_type,
    auto_hint.highlight_as_priority,
    NULL::text AS rationale4suppression,
    NULL::text AS recommendation,
    md5(((((COALESCE(auto_hint.query, ''::text) || COALESCE(auto_hint.recommendation_query, ''::text)) || COALESCE(auto_hint.title, ''::text)) || COALESCE(auto_hint.hint, ''::text)) || COALESCE(auto_hint.url, ''::text))) AS md5_sum,
    auto_hint.xmin AS xmin_auto_hint
   FROM ref.auto_hint;


ALTER TABLE ref.v_auto_hints OWNER TO "gm-dbo";

--
-- Name: get_hints_for_patient(integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.get_hints_for_patient(_pk_identity integer) RETURNS SETOF ref.v_auto_hints
    LANGUAGE plpgsql
    AS $$
DECLARE
	_hint ref.v_auto_hints%rowtype;
	_query text;
	_suppression_exists boolean;		-- does not mean that the suppression applies
	_md5_at_suppression text;
	_old_rationale4suppression text;
	_hint_currently_applies boolean;	-- regardless of whether suppressed or not
	_hint_recommendation text;
	_title text;
--	_exc_state text;
--	_exc_msg text;
--	_exc_detail text;
--	_exc_hint text;
--	_exc_context text;
BEGIN
	-- loop over all defined hints
	FOR _hint IN SELECT * FROM ref.v_auto_hints WHERE is_active LOOP
		--raise NOTICE 'checking hint for patient %: %', _pk_identity, _hint.title;
		-- is the hint suppressed ?
		SELECT (clin.hint_suppression_exists(_pk_identity, _hint.pk_auto_hint)).*
			INTO STRICT _suppression_exists, _md5_at_suppression, _old_rationale4suppression;
		-- does the hint currently apply ? (regardless of whether it is suppressed)
		_query := replace(_hint.query, 'ID_ACTIVE_PATIENT', _pk_identity::text);
		_query := replace(_query, 'clin.v_emr_journal', 'clin._view_emr_journal_without_suppressed_hints');
		SELECT (clin.run_hint_query(_hint.title, _query)).*
			INTO STRICT _hint_currently_applies, _title;
		-- error ?
		IF _hint_currently_applies IS NULL THEN
			--raise NOTICE ' error -> return';
			_hint.title := _title;
			_hint.hint := _query;
			RETURN NEXT _hint;
			-- process next hint
			CONTINUE;
		END IF;
		-- hint does not apply
		IF _hint_currently_applies IS FALSE THEN
			-- does a (previously stored) suppression exist ?
			IF _suppression_exists IS FALSE THEN
				-- no, so skip this hint
				--raise NOTICE ' does not apply -> skip';
				CONTINUE;
			END IF;
			--raise NOTICE ' does not apply but suppression invalid -> return for invalidation';
			-- hint suppressed but does NOT apply:
			-- skip hint but invalidate suppression, because:
			-- * previously the hint must have applied and the user suppressed it,
			-- * then patient data (or hint definition) changed such that
			--   the hint does not apply anymore (but the suppression is
			--   still valid),
			-- * when patient data changes again, the hint might apply again
			-- * HOWEVER - since the suppression would still be valid - the
			--   hint would magically get suppressed again (which is
			--   medically unsafe) ...
			-- after invalidation, the hint will no longer be suppressed,
			-- however - since it does not currently apply - it will
			-- still not be returned and shown until it applies again ...
			--
			-- -----------------------------------------------------------------------
			-- UNFORTUNATELY, the following is currently not _possible_ because
			-- we are running inside a READONLY transaction (due to inherent
			-- security risks when running arbitrary user queries [IOW the hint
			-- SQL] against the database) and we cannot execute a
			-- sub-transaction as READWRITE :-/
			--
			--UPDATE clin.suppressed_hint
			--SET md5_sum = 'invalidated'::text		-- will not ever match any md5 sum
			--WHERE
			--	fk_encounter IN (
			--		SELECT pk FROM clin.encounter WHERE fk_patient = _pk_identity
			--	)
			--		AND
			--	fk_hint = _hint.pk_auto_hint;
			-- -----------------------------------------------------------------------
			--
			-- hence our our workaround is to, indeed, return the hint but
			-- tag it with a magic rationale, by means of which the client
			-- can detect it to be in need of invalidation:
			_hint.title := 'HINT DOES NOT APPLY BUT NEEDS INVALIDATION OF EXISTING SUPPRESSION [' || _hint.title || '].';
			_hint.rationale4suppression := 'magic_tag::does_not_apply::suppression_needs_invalidation';
			RETURN NEXT _hint;
			CONTINUE;
		END IF;
		--raise NOTICE ' applies';
		-- but is there a suppression ?
		IF _suppression_exists IS FALSE THEN
			--raise NOTICE ' return';
			-- no: retrieve recommendation
			SELECT clin._get_recommendation_for_patient_hint(_hint.recommendation_query, _pk_identity) INTO STRICT _hint_recommendation;
			_hint.recommendation := _hint_recommendation;
			-- return hint
			RETURN NEXT _hint;
			CONTINUE;
		END IF;
		-- yes, is suppressed
		--raise NOTICE ' is suppressed';
		-- is the suppression still valid ?
		-- -> yes, suppression valid
		IF _md5_at_suppression = _hint.md5_sum THEN
			--raise NOTICE '-> suppression valid, ignoring hint';
			-- hint applies, suppressed, suppression valid: skip this hint
			CONTINUE;
		END IF;
		-- -> no, suppression not valid
		-- hint definition must have changed so ignore the suppression but
		-- provide previous rationale for suppression to the user
		_hint.rationale4suppression := _old_rationale4suppression;
		-- retrieve recommendation
		SELECT clin._get_recommendation_for_patient_hint(_hint.recommendation_query, _pk_identity) INTO STRICT _hint_recommendation;
		_hint.recommendation := _hint_recommendation;
		RETURN NEXT _hint;
		CONTINUE;
	END LOOP;
	RETURN;
END;$$;


ALTER FUNCTION clin.get_hints_for_patient(_pk_identity integer) OWNER TO "gm-dbo";

--
-- Name: hint_suppression_exists(integer, integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.hint_suppression_exists(_pk_identity integer, _pk_hint integer, OUT _o_exists boolean, OUT _o_md5 text, OUT _o_rationale text) RETURNS record
    LANGUAGE plpgsql
    AS $$
--DECLARE
--	_md5_suppressed text;
--	_old_rationale4suppression text;
BEGIN
	SELECT
		md5_sum,
		rationale
			INTO
		_o_md5,
		_o_rationale
	FROM clin.suppressed_hint WHERE
		fk_hint = _pk_hint
			AND
		fk_encounter IN (
			SELECT pk FROM clin.encounter WHERE fk_patient = _pk_identity
		);
	IF FOUND THEN
		_o_exists := TRUE;
	ELSE
		_o_exists := FALSE;
	END IF;
END;$$;


ALTER FUNCTION clin.hint_suppression_exists(_pk_identity integer, _pk_hint integer, OUT _o_exists boolean, OUT _o_md5 text, OUT _o_rationale text) OWNER TO "gm-dbo";

--
-- Name: move_waiting_list_entry(integer, integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.move_waiting_list_entry(_wl_pos_src integer, _wl_pos_dest integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	_tmp_pos integer;
	_curr_max_pos integer;
BEGIN
	if _wl_pos_src = _wl_pos_dest then
		return true;
	end if;
	if _wl_pos_dest < 1 then
		raise notice 'clin.move_waiting_list_entry(): Will not move entry [%] before start of list [%].', _wl_pos_src, _wl_pos_dest;
		return False;
	end if;
	select max(list_position) into _curr_max_pos from clin.waiting_list;
	-- do not move last entry further down
	if _wl_pos_src = _curr_max_pos then
		if _wl_pos_dest > _wl_pos_src then
			raise notice 'clin.move_waiting_list_entry(): Will not move last entry [%] beyond end of list to [%].', _wl_pos_src, _wl_pos_dest;
			return False;
		end if;
	end if;
	-- does the source row exist ?
	perform 1 from clin.waiting_list where list_position = _wl_pos_src;
	if not found then
		raise notice 'clin.move_waiting_list_entry(): Cannot move entry [%] to [%]. Entry does not exist.', _wl_pos_src, _wl_pos_dest ;
		return false;
	end if;
	-- load destination row
	perform 1 from clin.waiting_list where list_position = _wl_pos_dest;
	-- does not exist
	if not found then
		-- do not move entry beyond end of list more than necessary
		if _wl_pos_dest > (_curr_max_pos + 1) then
			_tmp_pos := _curr_max_pos + 1;
		else
			_tmp_pos := _wl_pos_dest;
		end if;
		-- so update row to move and be done with it
		update clin.waiting_list
			set list_position = _tmp_pos
			where list_position = _wl_pos_src;
		return true;
	end if;
	-- move existing row out of the way
	select (max(list_position) + _wl_pos_dest + _wl_pos_src) into _tmp_pos from clin.waiting_list;
	update clin.waiting_list
		set list_position = _tmp_pos
		where list_position = _wl_pos_dest;
	-- move row to move
	update clin.waiting_list
		set list_position = _wl_pos_dest
		where list_position = _wl_pos_src;
	-- move back existing row
	update clin.waiting_list
		set list_position = _wl_pos_src
		where list_position = _tmp_pos;
	return true;
END;$$;


ALTER FUNCTION clin.move_waiting_list_entry(_wl_pos_src integer, _wl_pos_dest integer) OWNER TO "gm-dbo";

--
-- Name: FUNCTION move_waiting_list_entry(_wl_pos_src integer, _wl_pos_dest integer); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.move_waiting_list_entry(_wl_pos_src integer, _wl_pos_dest integer) IS 'Move row with logical position $1 into logical position $2. If another row
 exists with position $2 it will be moved to position $1 in the process.
 Fails if there is no row with position $1.';


--
-- Name: remove_old_empty_encounters(integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.remove_old_empty_encounters(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$select clin.remove_old_empty_encounters($1, '1 week'::interval);$_$;


ALTER FUNCTION clin.remove_old_empty_encounters(integer) OWNER TO "gm-dbo";

--
-- Name: FUNCTION remove_old_empty_encounters(integer); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.remove_old_empty_encounters(integer) IS 'Remove empty encounters older than 1 week from a patient.';


--
-- Name: remove_old_empty_encounters(integer, interval); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.remove_old_empty_encounters(integer, interval) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_pk_identity alias for $1;
	_defined_minimum_encounter_age alias for $2;
	_usable_minimum_encounter_age interval;
	_no_of_encounters integer;
BEGIN
	-- does person exist ?
	perform 1 from dem.identity where pk = _pk_identity;
	if not found then
		raise exception 'clin.remove_old_empty_encounters(person=%, min_age=%): person [%] does not exist', _pk_identity, _defined_minimum_encounter_age, _pk_identity;
		return false;
	end if;
	SELECT count(1) INTO STRICT _no_of_encounters
	FROM clin.encounter	WHERE fk_patient = _pk_identity;
	IF _no_of_encounters < 2 THEN
		raise notice 'clin.remove_old_empty_encounters(person=%, min_age=%): there are less than 2 encounters for this patient', _pk_identity, _defined_minimum_encounter_age;
		return false;
	END IF;
	if _defined_minimum_encounter_age < '3 days'::interval then
		_usable_minimum_encounter_age := '3 days'::interval;
	else
		_usable_minimum_encounter_age := _defined_minimum_encounter_age;
	end if;
	DELETE FROM clin.encounter WHERE
		clin.encounter.fk_patient = _pk_identity
			AND
		-- is this the active encounter somewhere ?
		(SELECT pg_try_advisory_lock('clin.encounter'::regclass::oid::int, clin.encounter.pk))
			AND
		age(clin.encounter.last_affirmed) > _usable_minimum_encounter_age
			AND
		NOT EXISTS (SELECT 1 FROM clin.clin_root_item WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM blobs.doc_med WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.episode WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.health_issue WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.allergy_state WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM bill.bill_item WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.external_care WHERE fk_encounter = clin.encounter.pk)
			AND
		NOT EXISTS (SELECT 1 FROM clin.suppressed_hint WHERE fk_encounter = clin.encounter.pk)
	;
	RETURN TRUE;
END;$_$;


ALTER FUNCTION clin.remove_old_empty_encounters(integer, interval) OWNER TO "gm-dbo";

--
-- Name: FUNCTION remove_old_empty_encounters(integer, interval); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.remove_old_empty_encounters(integer, interval) IS 'Remove empty encounters older than a definable minimum age from a patient.';


--
-- Name: run_hint_query(text, text); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.run_hint_query(_title text, _query text, OUT _o_applies boolean, OUT _o_title text) RETURNS record
    LANGUAGE plpgsql
    AS $$
BEGIN
	BEGIN
		EXECUTE _query INTO STRICT _o_applies;
	EXCEPTION
		--WHEN insufficient_privilege THEN RAISE WARNING 'auto hint query failed: %', _query;
		WHEN others THEN
			RAISE WARNING 'auto hint query failed: %', _query;
			-- only available starting with PG 9.2:
			--GET STACKED DIAGNOSTICS
			--	_exc_state = RETURNED_SQLSTATE,
			--	_exc_msg = MESSAGE_TEXT,
			--	_exc_detail = PG_EXCEPTION_DETAIL,
			--	_exc_hint = PG_EXCEPTION_HINT,
			--	_exc_context = PG_EXCEPTION_CONTEXT;
			--RAISE WARNING 'SQL STATE: %', _exc_state;
			--RAISE WARNING 'MESSAGE: %', _exc_msg;
			--RAISE WARNING 'DETAIL: %', _exc_detail;
			--RAISE WARNING 'HINT: %', _exc_hint;
			--RAISE WARNING 'CONTEXT: %', _exc_context;
			-- workaround for 9.1:
			RAISE WARNING 'SQL STATE: %', SQLSTATE;
			RAISE WARNING 'MESSAGE: %', SQLERRM;
			_o_applies := NULL;
			_o_title := ('ERROR checking for [' || _title || '] !')::TEXT;
			RETURN;
	END;
	_o_title := _title;
END;$$;


ALTER FUNCTION clin.run_hint_query(_title text, _query text, OUT _o_applies boolean, OUT _o_title text) OWNER TO "gm-dbo";

--
-- Name: transfer_all_encounter_data(integer, integer); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.transfer_all_encounter_data(_pk_source_encounter integer, _pk_target_encounter integer) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_fk_row record;
	_success boolean;
BEGIN
	-- same patient on both encounters ?
	SELECT (
		coalesce((select fk_patient from clin.encounter where pk = _pk_source_encounter), -1)
			=
		coalesce((select fk_patient from clin.encounter where pk = _pk_target_encounter), -2)
	) INTO strict _success;
	IF _success IS FALSE THEN
		RAISE NOTICE 'source encounter (%) belongs to a patient different from target encounter (%), aborting', _pk_source_encounter, _pk_target_encounter;
		RETURN FALSE;
	END IF;
	-- loop over foreign keys in non-clin.* tables
	FOR _fk_row IN
		SELECT
			conrelid::pg_catalog.regclass
				as referencing_table,
			(select attname from pg_attribute where attnum = pg_c.conkey[1] and attrelid = pg_c.conrelid) as referencing_column
		FROM
			pg_catalog.pg_constraint pg_c
		WHERE
			pg_c.confrelid = 'clin.encounter'::pg_catalog.regclass
				AND
			pg_c.contype = 'f'
				AND
			position('clin.'::text in pg_c.conrelid::pg_catalog.regclass::text) = 0
	LOOP
		EXECUTE format (
			'UPDATE %1$s SET %2$I = %3$L WHERE %2$I = %4$L',
				_fk_row.referencing_table,
				_fk_row.referencing_column,
				_pk_target_encounter,
				_pk_source_encounter
		);
	END LOOP;
	-- update clin.clin_root_item children in one go
	UPDATE clin.clin_root_item SET fk_encounter = _pk_target_encounter WHERE fk_encounter = _pk_source_encounter;
	return TRUE;
END;$_$;


ALTER FUNCTION clin.transfer_all_encounter_data(_pk_source_encounter integer, _pk_target_encounter integer) OWNER TO "gm-dbo";

--
-- Name: FUNCTION transfer_all_encounter_data(_pk_source_encounter integer, _pk_target_encounter integer); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.transfer_all_encounter_data(_pk_source_encounter integer, _pk_target_encounter integer) IS 'transfers all data linked to _pk_source_encounter into _pk_target_encounter';


--
-- Name: trf_activate_issue_on_opening_episode(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_activate_issue_on_opening_episode() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	if TG_OP = 'UPDATE' then
		if OLD.is_open is TRUE then
			return NEW;
		end if;
	end if;
	update clin.health_issue
	set is_active = TRUE
	where
		pk = NEW.fk_health_issue
			AND
		is_active is FALSE
	;
	return NEW;
end;$$;


ALTER FUNCTION clin.trf_activate_issue_on_opening_episode() OWNER TO "gm-dbo";

--
-- Name: trf_announce_active_substance_mod_no_pk(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_announce_active_substance_mod_no_pk() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	execute 'notify "active_substance_mod_db:"';
	return NULL;
end;
$$;


ALTER FUNCTION clin.trf_announce_active_substance_mod_no_pk() OWNER TO "gm-dbo";

--
-- Name: trf_announce_consumed_substance_mod_no_pk(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_announce_consumed_substance_mod_no_pk() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	execute 'notify "consumed_substance_mod_db:"';
	return NULL;
end;
$$;


ALTER FUNCTION clin.trf_announce_consumed_substance_mod_no_pk() OWNER TO "gm-dbo";

--
-- Name: trf_check_ext_care_uniq_issue_per_enc_and_unit_ins_upd(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_check_ext_care_uniq_issue_per_enc_and_unit_ins_upd() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_issue_count integer;
BEGIN
	SELECT COUNT(1) INTO STRICT _issue_count
	FROM clin.external_care
	WHERE
		issue = NEW.issue
			AND
		fk_org_unit = NEW.fk_org_unit
			AND
		fk_encounter IN (
			SELECT pk FROM clin.encounter WHERE fk_patient = (
				SELECT fk_patient FROM clin.encounter WHERE pk = NEW.fk_encounter
			)
		)
	;
	IF _issue_count > 1 THEN
		RAISE EXCEPTION '% into clin.external_care: Sanity check failed. Cannot insert issue [%] more than once for patient of encounter [%] at org unit [%].',
			TG_OP,
			NEW.issue,
			NEW.fk_encounter,
			NEW.fk_org_unit
			USING ERRCODE = 'check_violation';
		return NULL;
	END IF;
	RETURN NEW;
END;
$$;


ALTER FUNCTION clin.trf_check_ext_care_uniq_issue_per_enc_and_unit_ins_upd() OWNER TO "gm-dbo";

--
-- Name: trf_del_intake_document_deleted(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_del_intake_document_deleted() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_row record;
	_pk_episode integer;
BEGIN
	select
		* into _row
	from
		clin.v_substance_intake_journal
	where
		src_pk = OLD.pk;
	_pk_episode := _row.pk_episode;
	-- create episode if needed
	if _pk_episode is null then
		select pk into _pk_episode
		from clin.episode
		where
			description = _('Medication history')
				and
			fk_encounter in (
				select pk from clin.encounter where fk_patient = _row.pk_patient
			);
		if not found then
			insert into clin.episode (
				description,
				is_open,
				fk_encounter
			) values (
				_('Medication history'),
				FALSE,
				OLD.fk_encounter
			) returning pk into _pk_episode;
		end if;
	end if;
	insert into clin.clin_narrative (
		fk_encounter,
		fk_episode,
		soap_cat,
		narrative
	) values (
		_row.pk_encounter,
		_pk_episode,
		NULL,
		_('Deletion of') || ' ' || _row.narrative
	);
	return OLD;
END;$$;


ALTER FUNCTION clin.trf_del_intake_document_deleted() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_del_intake_document_deleted(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_del_intake_document_deleted() IS 'Document the deletion of a substance intake.';


--
-- Name: trf_del_intake_must_unlink_all_drug_components(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_del_intake_must_unlink_all_drug_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_drug_product integer;
	_component_count integer;
	_pk_patient integer;
	_intake_count integer;
	_msg text;
BEGIN
	-- which drug ?
	select fk_drug_product into _pk_drug_product
	from ref.lnk_dose2drug
	where pk = OLD.fk_drug_component;
	-- how many components therein ?
	select count(1) into _component_count
	from ref.lnk_dose2drug
	where fk_drug_product = _pk_drug_product;
	-- only one component anyways ?
	if _component_count = 1 then
		return NULL;
	end if;
	-- retrieve patient
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = OLD.fk_encounter;
	-- how many intakes of this drug are linked to this patient
	select count(1) into _intake_count
	from clin.substance_intake
	where
		fk_drug_component in (
			select pk from ref.lnk_dose2drug where fk_drug_product = _pk_drug_product
		)
			and
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		);
	-- intake count must be 0
	if _intake_count = 0 then
		return NULL;
	end if;
	_msg := '[clin.trf_del_intake_must_unlink_all_drug_components]: deleting a multi-component intake ['
		|| OLD.pk || '] must unlink all components of the drug product ['
		|| _pk_drug_product || '] '
		|| '(unlinked component [' || OLD.fk_drug_component || '])';
	raise exception check_violation using message = _msg;
	return NULL;
END;$$;


ALTER FUNCTION clin.trf_del_intake_must_unlink_all_drug_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_del_intake_must_unlink_all_drug_components(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_del_intake_must_unlink_all_drug_components() IS 'If a multi-component drug intake is deleted from a patient ALL components thereof must be unlinked.';


--
-- Name: trf_ensure_one_allergy_state_per_patient(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_ensure_one_allergy_state_per_patient() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_new_pk_patient integer;
	_old_pk_patient integer;
begin
	-- find patient from encounter
	select into _new_pk_patient fk_patient
		from clin.encounter
		where pk = NEW.fk_encounter;
	if not FOUND then
		raise exception 'Encounter % does not exist !?', NEW.fk_encounter;
		return NEW;
	end if;
	-- new row
	if TG_OP = 'INSERT' then
		-- patient already there ?
		perform 1 from clin.allergy_state
		where
			fk_encounter in (select pk from clin.encounter where fk_patient = _new_pk_patient);
		if FOUND then
			raise exception 'Cannot insert second allergy state for patient % via encounter %.', _new_pk_patient, NEW.fk_encounter;
			return NEW;
		end if;
		return NEW;
	end if;
	if TG_OP = 'UPDATE' then
		if NEW.fk_encounter = OLD.fk_encounter then
			return NEW;
		end if;
		select into _old_pk_patient fk_patient
			from clin.encounter
			where pk = OLD.fk_encounter;
		if _new_pk_patient = _old_pk_patient then
			return NEW;
		end if;
		raise exception 'Invalid fk_encounter update (% -> %): it would change the associated patient (% -> %).', OLD.fk_encounter, NEW.fk_encounter, _old_pk_patient, _new_pk_patient;
		return NEW;
	end if;
	return NEW;
end;$$;


ALTER FUNCTION clin.trf_ensure_one_allergy_state_per_patient() OWNER TO "gm-dbo";

--
-- Name: trf_ins_intake_prevent_duplicate_component_links(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_ins_intake_prevent_duplicate_component_links() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_patient integer;
	_pk_intake integer;
	_msg text;
BEGIN
	-- which patient ?
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = NEW.fk_encounter;
	-- already exists ?
	select pk into _pk_intake
	from clin.substance_intake
	where
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		)
			and
		fk_drug_component = NEW.fk_drug_component
	;
	if FOUND then
		_msg := '[clin.trf_ins_intake_prevent_duplicate_component_links]: drug component ref.lnk_dose2drug.pk=(' || NEW.fk_drug_component || ') '
			|| 'already linked to patient=(' || _pk_patient || ') '
			|| 'as clin.substance_intake.pk=(' || _pk_intake || ')';
		raise exception unique_violation using message = _msg;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_ins_intake_prevent_duplicate_component_links() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_ins_intake_prevent_duplicate_component_links(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_ins_intake_prevent_duplicate_component_links() IS 'Prevent patient from being put on a particular component twice.';


--
-- Name: trf_ins_lc2sth_fk_generic_code(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_ins_lc2sth_fk_generic_code() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	perform 1 from ref.coding_system_root where pk_coding_system = NEW.fk_generic_code;
	if FOUND then
		return NEW;
	end if;
	_msg := 'clin.trf_ins_lc2sth_fk_generic_code(): INSERT into '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'fk_generic_code=(' || NEW.fk_generic_code || ') '
		|| 'does not exist in ref.coding_system_root.pk_coding_system';
	raise foreign_key_violation using message = _msg;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_ins_lc2sth_fk_generic_code() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_ins_lc2sth_fk_generic_code(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_ins_lc2sth_fk_generic_code() IS 'Check foreign key integrity on insert to *.fk_generic_code -> ref.coding_system_root.pk_coding_system.';


--
-- Name: trf_ins_upd_export_item_normalize_fk_identity(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_ins_upd_export_item_normalize_fk_identity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	SELECT fk_patient INTO STRICT NEW.fk_identity
	FROM clin.encounter
	WHERE
		pk = (
			SELECT fk_encounter FROM blobs.doc_med WHERE pk = (
				SELECT fk_doc FROM blobs.doc_obj WHERE pk = NEW.fk_doc_obj
			)
		)
	;
	RETURN NEW;
END;$$;


ALTER FUNCTION clin.trf_ins_upd_export_item_normalize_fk_identity() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_ins_upd_export_item_normalize_fk_identity(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_ins_upd_export_item_normalize_fk_identity() IS 'Set .fk_identity from .fk_doc if .fk_doc is NOT NULL';


--
-- Name: trf_insert_intake_links_all_drug_components(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_insert_intake_links_all_drug_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_component_count integer;
	_pk_patient integer;
	_pk_drug_product integer;
	_pk_component integer;
BEGIN
	-- get the product we are linking to
	select fk_drug_product into _pk_drug_product
	from ref.lnk_dose2drug
	where pk = NEW.fk_drug_component;
	-- how many components therein ?
	select count(1) into _component_count
	from ref.lnk_dose2drug
	where fk_drug_product = _pk_drug_product;
	-- only one component ?
	if _component_count = 1 then
		return NEW;
	end if;
	-- which patient ?
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = NEW.fk_encounter;
	-- INSERT all components
	for _pk_component in
		select pk from ref.lnk_dose2drug where fk_drug_product = _pk_drug_product
	loop
		-- already there ?
		perform 1 from clin.substance_intake where
			fk_encounter in (
				select pk from clin.encounter where fk_patient = _pk_patient
			)
				and
			fk_drug_component = _pk_component
		;
		if FOUND then
			continue;
		end if;
		-- insert
		insert into clin.substance_intake (
			fk_drug_component,				-- differentiate
			clin_when,						-- harmonize (started)
			fk_encounter,					-- harmonize
			fk_episode,						-- required
			soap_cat,						-- harmonize
			schedule,						-- harmonize
			duration,						-- harmonize
			intake_is_approved_of,			-- harmonize
			is_long_term,					-- harmonize
			discontinued,					-- harmonize
			narrative,
			aim,
			discontinue_reason,
			comment_on_start,
			harmful_use_type
		) values (
			_pk_component,
			NEW.clin_when,
			NEW.fk_encounter,
			NEW.fk_episode,
			NEW.soap_cat,
			NEW.schedule,
			NEW.duration,
			NEW.intake_is_approved_of,
			NEW.is_long_term,
			NEW.discontinued,
			NEW.narrative,
			NEW.aim,
			NEW.discontinue_reason,
			NEW.comment_on_start,
			NEW.harmful_use_type
		);
	end loop;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_insert_intake_links_all_drug_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_insert_intake_links_all_drug_components(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_insert_intake_links_all_drug_components() IS 'If a patient is put on a multi-component drug they must be put on ALL components thereof.';


--
-- Name: trf_invalidate_review_on_result_change(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_invalidate_review_on_result_change() RETURNS trigger
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
DECLARE
	is_modified bool;
BEGIN
	is_modified := False;
	-- change of test type
	if NEW.fk_type != OLD.fk_type then
		is_modified := True;
	end if;
	-- change of numeric value
	if NEW.val_num != OLD.val_num then
		is_modified := True;
	end if;
	-- change of alpha value
	if NEW.val_alpha != OLD.val_alpha then
		is_modified := True;
	end if;
	-- change of unit
	if NEW.val_unit != OLD.val_unit then
		is_modified := True;
	end if;
	if is_modified is True then
		delete from clin.reviewed_test_results where fk_reviewed_row = OLD.pk;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_invalidate_review_on_result_change() OWNER TO "gm-dbo";

--
-- Name: trf_normalize_proc_is_ongoing(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_normalize_proc_is_ongoing() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if NEW.clin_end > clock_timestamp() then
		NEW.is_ongoing := TRUE;
	else
		NEW.is_ongoing := FALSE;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_normalize_proc_is_ongoing() OWNER TO "gm-dbo";

--
-- Name: trf_notify_reviewer_of_review_change(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_notify_reviewer_of_review_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_pk_patient integer;
	_pk_type integer;
begin
	-- disallow change of referenced row
	-- for cleanliness this really *should* be in another trigger
	if NEW.fk_reviewed_row <> OLD.fk_reviewed_row then
		raise exception 'Attaching an existing review to another test result is not allowed (fk_reviewed_row change).';
		return NEW;
	end if;
	-- change of last reviewer ?
	if NEW.fk_reviewer = OLD.fk_reviewer then
		return NEW;
	end if;
	-- review change ?
	if (NEW.is_technically_abnormal <> OLD.is_technically_abnormal) or
	   (NEW.clinically_relevant <> OLD.clinically_relevant) then
		-- find patient for test result
		select pk_patient into _pk_patient
			from clin.v_test_results
			where pk_test_result = OLD.fk_reviewed_row;
		-- find inbox item type
		select pk_type into _pk_type
			from dem.v_inbox_item_type where
			type = 'results review change';
		-- create it if necessary
		if not found then
			insert into dem.inbox_item_type (
				fk_inbox_item_category,
				description
			) values (
				(select pk from dem.inbox_item_category where description = 'clinical'),
				'results review change'
			);
			select pk_type into _pk_type
				from dem.v_inbox_item_type where
				type = 'results review change';
		end if;
		-- already notified ?
		perform 1 from dem.message_inbox where
			fk_staff = OLD.fk_reviewer
			and fk_inbox_item_type = _pk_type
			and ufk_context = ARRAY[_pk_patient];
		-- nope, so notify now
		if not found then
			insert into dem.message_inbox (
				fk_staff, fk_inbox_item_type, comment, ufk_context
			) values (
				OLD.fk_reviewer,
				_pk_type,
				(select
					_('results review changed for patient') || ' [' || vpb.lastnames || ', ' || vbp.firstnames || ']'
					from dem.v_basic_person vbp
					where vpb.pk_identity = _pk_patient
				),
				ARRAY[_pk_patient]
			);
		end if;
	end if;
	return NEW;
end;$$;


ALTER FUNCTION clin.trf_notify_reviewer_of_review_change() OWNER TO "gm-dbo";

--
-- Name: trf_sanity_check_enc_epi_ins_upd(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sanity_check_enc_epi_ins_upd() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
declare
	_fk_encounter_col text;
	_enc_pk integer;
	_fk_episode_col text;
	_epi_pk integer;
	_identity_from_encounter integer;
	_identity_from_episode integer;
	_cmd text;
begin
	_fk_encounter_col := TG_ARGV[0];
	_fk_episode_col := TG_ARGV[1];
	_cmd := 'select $1.' || _fk_encounter_col;
	EXECUTE _cmd INTO STRICT _enc_pk USING NEW;
	select fk_patient into _identity_from_encounter from clin.encounter where pk = _enc_pk;
--	raise notice '%: % -> %', _cmd, _enc_pk, _identity_from_encounter;
	_cmd := 'select $1.' || _fk_episode_col;
	EXECUTE _cmd INTO STRICT _epi_pk USING NEW;
	select fk_patient into _identity_from_episode from clin.encounter where pk = (select fk_encounter from clin.episode where pk = _epi_pk);
--	raise notice '%: % -> %', _cmd, _epi_pk, _identity_from_episode;
	if _identity_from_encounter <> _identity_from_episode then
		raise exception '% into %.%: Sanity check failed. %=% -> patient=%. %=% -> patient=%.',
			TG_OP,
			TG_TABLE_SCHEMA,
			TG_TABLE_NAME,
			_fk_encounter_col,
			_enc_pk,
			_identity_from_encounter,
			_fk_episode_col,
			_epi_pk,
			_identity_from_episode
		;
		return NULL;
	end if;
	return NEW;
end;
$_$;


ALTER FUNCTION clin.trf_sanity_check_enc_epi_ins_upd() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_sanity_check_enc_epi_ins_upd(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_sanity_check_enc_epi_ins_upd() IS 'this function is used in triggers and checks whether foreign keys to clin.episode.pk and clin.encounter.pk on a single table ultimately point to the same patient';


--
-- Name: trf_sanity_check_enc_issue_ins_upd(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sanity_check_enc_issue_ins_upd() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_enc_pk integer;
	_epi_pk integer;
	_identity_from_encounter integer;
	_identity_from_issue integer;
	_cmd text;
begin
	select fk_patient into _identity_from_encounter from clin.encounter where pk = NEW.fk_encounter;
--	raise notice '%: % -> %', _cmd, _enc_pk, _identity_from_encounter;
	select fk_patient into _identity_from_issue
	from clin.encounter where pk = (
		select fk_encounter from clin.health_issue where pk = NEW.fk_health_issue
	);
	IF _identity_from_encounter <> _identity_from_issue THEN
		RAISE EXCEPTION '% into clin.external_care: Sanity check failed. fk_encounter=% -> patient=%. fk_health_issue=% -> patient=%.',
			TG_OP,
			NEW.fk_encounter,
			_identity_from_encounter,
			NEW.fk_health_issue,
			_identity_from_issue
			USING ERRCODE = 'check_violation'
		;
		return NULL;
	END IF;
	return NEW;
end;
$$;


ALTER FUNCTION clin.trf_sanity_check_enc_issue_ins_upd() OWNER TO "gm-dbo";

--
-- Name: trf_sanity_check_enc_vs_issue_on_epi(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sanity_check_enc_vs_issue_on_epi() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_identity_from_encounter integer;
	_identity_from_issue integer;
begin
	-- if issue is NULL, do not worry about mismatch
	if NEW.fk_health_issue is NULL then
		return NEW;
	end if;
	-- .fk_episode must belong to the same patient as .fk_encounter
	select fk_patient into _identity_from_encounter from clin.encounter where pk = NEW.fk_encounter;
	select fk_patient into _identity_from_issue     from clin.encounter where pk = (
		select fk_encounter from clin.health_issue where pk = NEW.fk_health_issue
	);
	if _identity_from_encounter <> _identity_from_issue then
		raise exception 'INSERT/UPDATE into %.%: Sanity check failed. Encounter % patient = %. Issue % patient = %.',
			TG_TABLE_SCHEMA,
			TG_TABLE_NAME,
			NEW.fk_encounter,
			_identity_from_encounter,
			NEW.fk_health_issue,
			_identity_from_issue
		;
		return NULL;
	end if;
	return NEW;
end;$$;


ALTER FUNCTION clin.trf_sanity_check_enc_vs_issue_on_epi() OWNER TO "gm-dbo";

--
-- Name: trf_sanity_check_procedure_episode(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sanity_check_procedure_episode() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_hospital_stay_episode_pk integer;
BEGIN
	if NEW.fk_hospital_stay is null then
		return NEW;
	end if;
	select into _hospital_stay_episode_pk fk_episode
		from clin.hospital_stay
		where pk = NEW.fk_hospital_stay;
	if NEW.fk_episode = _hospital_stay_episode_pk then
		return NEW;
	end if;
	raise exception '[clin.procedure]: INSERT/UPDATE failed: fk_episode (%) does not match fk_episode (%) behind fk_hospital_stay (%)', NEW.fk_episode, _hospital_stay_episode_pk, NEW.fk_hospital_stay;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_sanity_check_procedure_episode() OWNER TO "gm-dbo";

--
-- Name: trf_sanity_check_uniq_hint_per_pat_ins_upd(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sanity_check_uniq_hint_per_pat_ins_upd() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_suppression_count integer;
BEGIN
	-- the count of suppressions for this hint in this patient
	SELECT COUNT(1) INTO STRICT _suppression_count
	FROM clin.suppressed_hint
	WHERE
		fk_hint = NEW.fk_hint
			AND
		fk_encounter IN (
			SELECT pk FROM clin.encounter WHERE fk_patient = (
				SELECT fk_patient FROM clin.encounter WHERE pk = NEW.fk_encounter
			)
		)
	;
	IF _suppression_count > 1 THEN
		RAISE EXCEPTION '% into clin.suppressed_hint: Sanity check failed. Hint [%] suppressed more than once for patient of encounter [%].',
			TG_OP,
			NEW.pk,
			NEW.fk_encounter
			USING ERRCODE = 'check_violation';
		return NULL;
	END IF;
	return NEW;
END;
$$;


ALTER FUNCTION clin.trf_sanity_check_uniq_hint_per_pat_ins_upd() OWNER TO "gm-dbo";

--
-- Name: trf_sync_allergic_state_on_allergies_modified(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_sync_allergic_state_on_allergies_modified() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_fk_patient integer;
	_fk_encounter integer;
	_state integer;
	_no_of_allergies integer;
BEGIN
	if TG_OP = 'INSERT' then
		select into _fk_patient fk_patient from clin.encounter where pk = NEW.fk_encounter;
		_fk_encounter := NEW.fk_encounter;
		_state := 1;
	end if;
	if TG_OP = 'DELETE' then
		-- only run this trigger if deleting last allergy
		select into _fk_patient fk_patient from clin.encounter where pk = OLD.fk_encounter;
		select into _no_of_allergies count(1) from clin.allergy where fk_encounter in (
			select pk from clin.encounter where fk_patient = _fk_patient
		);
		if _no_of_allergies > 1 then
			return OLD;		-- still allergies left
		end if;
		_fk_encounter := OLD.fk_encounter;
		_state := 0;
	end if;
	update clin.allergy_state
		set
			has_allergy = _state,
			last_confirmed = coalesce(last_confirmed, now())
		where fk_encounter in (
			select pk from clin.encounter where fk_patient = _fk_patient
		);
	if not FOUND then
		insert into clin.allergy_state
			(fk_encounter, has_allergy, last_confirmed)
		values
			(_fk_encounter, _state, now());
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_sync_allergic_state_on_allergies_modified() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_sync_allergic_state_on_allergies_modified(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_sync_allergic_state_on_allergies_modified() IS 'trigger function to sync the allergy state on insert/delete';


--
-- Name: trf_undiscontinue_unsets_reason(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_undiscontinue_unsets_reason() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_identity_from_encounter integer;
	_identity_from_issue integer;
begin
	if NEW.discontinued is NULL then
		NEW.discontinue_reason := NULL;
	end if;
	return NEW;
end;$$;


ALTER FUNCTION clin.trf_undiscontinue_unsets_reason() OWNER TO "gm-dbo";

--
-- Name: trf_unique_indication_in_schedule(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_unique_indication_in_schedule() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	-- is the indication already linked ?
	perform 1 from clin.v_vaccination_courses_in_schedule where
		pk_vaccination_schedule = NEW.fk_schedule and
		pk_indication = (select fk_indication from clin.vaccination_course where pk=NEW.fk_course);
	if FOUND then
		_msg := 'Cannot link course [' || NEW.fk_course || '] into schedule [' || NEW.fk_schedule || ']. The indication is already linked.';
		raise exception '%', _msg;
		return null;
	end if;
	return null;
END;$$;


ALTER FUNCTION clin.trf_unique_indication_in_schedule() OWNER TO "gm-dbo";

--
-- Name: trf_upd_intake_must_link_all_drug_components(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_upd_intake_must_link_all_drug_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_intake_count integer;
	_component_count integer;
	_pk_patient integer;
	_pk_drug_product integer;
	_msg text;
BEGIN
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = NEW.fk_encounter;
	-- get the drug product we were linking to
	select fk_drug_product into _pk_drug_product
	from ref.lnk_dose2drug
	where pk = OLD.fk_drug_component;
	-- How many substance intake links for this drug have we got ?
	select count(1) into _intake_count
	from clin.substance_intake
	where
		fk_drug_component in (
			select pk from ref.lnk_dose2drug where fk_drug_product = _pk_drug_product
		)
			and
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		);
	-- unlinking completely would be fine but else:
	if _intake_count != 0 then
		-- How many components *are* there in the drug in question ?
		select count(1) into _component_count
		from ref.lnk_dose2drug
		where fk_drug_product = _pk_drug_product;
		-- substance intake link count and number of components must match
		if _component_count != _intake_count then
			_msg := '[clin.trf_upd_intake_must_link_all_drug_components]: re-linking drug product must unlink all components of old drug product [' || _pk_drug_product || '] '
				|| '(component [' || OLD.fk_drug_component || ' -> ' || NEW.fk_drug_component || '])';
			raise exception check_violation using message = _msg;
		end if;
	end if;
	-- check the NEW drug product
	-- get the drug product we are linking to
	select fk_drug_product into _pk_drug_product
	from ref.lnk_dose2drug
	where fk_substance = NEW.fk_drug_component;
	-- How many substance intake links for this drug have we got ?
	select count(1) into _intake_count
	from clin.substance_intake
	where
		fk_drug_component in (
			select pk from ref.lnk_dose2drug where fk_drug_product = _pk_drug_product
		)
			and
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		);
	-- linking all is fine but else:
	if _intake_count != 0 then
		-- How many components *are* there in the drug in question ?
		select count(1) into _component_count
		from ref.lnk_dose2drug
		where fk_drug_product = _pk_drug_product;
		-- substance intake link count and number of components must match
		if _component_count != _intake_count then
			_msg := '[clin.trf_upd_intake_must_link_all_drug_components]: re-linking drug product must link all components of new drug product [' || _pk_drug_product || '] '
				|| '(component [' || OLD.fk_drug_component || ' -> ' || NEW.fk_drug_component || '])';
			raise exception check_violation using message = _msg;
		end if;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_upd_intake_must_link_all_drug_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_intake_must_link_all_drug_components(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_upd_intake_must_link_all_drug_components() IS 'If a patient is put on a different multi-component drug ALL components thereof must be updated.';


--
-- Name: trf_upd_intake_prevent_duplicate_component_links(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_upd_intake_prevent_duplicate_component_links() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_patient integer;
	_intake_count integer;
	_msg text;
BEGIN
	-- which patient ?
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = NEW.fk_encounter;
	-- already exists ?
	select count(1) into strict _intake_count
	from clin.substance_intake
	where
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		)
			and
		fk_drug_component = NEW.fk_drug_component
	;
	if _intake_count > 1 then
		_msg := '[clin.trf_upd_intake_prevent_duplicate_component_links]: drug component ref.lnk_dose2drug.pk=(' || NEW.fk_drug_component || ') '
			|| 'already linked to patient=(' || _pk_patient || ') as clin.substance_intake';
		raise exception unique_violation using message = _msg;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_upd_intake_prevent_duplicate_component_links() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_intake_prevent_duplicate_component_links(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_upd_intake_prevent_duplicate_component_links() IS 'Prevent patient from being put on a particular component twice.';


--
-- Name: trf_upd_intake_updates_all_drug_components(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_upd_intake_updates_all_drug_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_drug_product integer;
	_component_count integer;
	_pk_patient integer;
BEGIN
	-- which drug ?
	select fk_drug_product into _pk_drug_product
	from ref.lnk_dose2drug
	where pk = NEW.fk_drug_component;
	-- how many components therein ?
	select count(1) into _component_count
	from ref.lnk_dose2drug
	where fk_drug_product = _pk_drug_product;
	-- only one component ?
	if _component_count = 1 then
		return NEW;
	end if;
	-- which patient ?
	select fk_patient into _pk_patient
	from clin.encounter
	where pk = NEW.fk_encounter;
	-- update all substance instake fields shared by drug components ...
	update clin.substance_intake set
		clin_when = NEW.clin_when,				-- started
		fk_encounter = NEW.fk_encounter,
		soap_cat = NEW.soap_cat,
		schedule = NEW.schedule,
		duration = NEW.duration,
		intake_is_approved_of = NEW.intake_is_approved_of,
		is_long_term = NEW.is_long_term,
		discontinued = NEW.discontinued,
		discontinue_reason = NEW.discontinue_reason,
		comment_on_start = NEW.comment_on_start,
		harmful_use_type = NEW.harmful_use_type
	where
		-- ... which belong to this drug ...
		fk_drug_component in (
			select pk from ref.lnk_dose2drug where fk_drug_product = _pk_drug_product
		)
			AND
		-- ... but are not THIS component ...
		fk_drug_component != NEW.fk_drug_component
			AND
		-- ... this patient ...
		fk_encounter in (
			select pk from clin.encounter where fk_patient = _pk_patient
		)
			AND
		-- ... are different in value (this will stop recursion as soon as all are equal)
		(
			clin_when is distinct from NEW.clin_when
				OR
			fk_encounter is distinct from NEW.fk_encounter
				OR
			soap_cat is distinct from NEW.soap_cat
				OR
			schedule is distinct from NEW.schedule
				OR
			duration is distinct from NEW.duration
				OR
			intake_is_approved_of is distinct from NEW.intake_is_approved_of
				OR
			is_long_term is distinct from NEW.is_long_term
				OR
			discontinued is distinct from NEW.discontinued
				OR
			discontinue_reason is distinct from NEW.discontinue_reason
				OR
			comment_on_start is distinct from NEW.comment_on_start
				OR
			harmful_use_type is distinct from NEW.harmful_use_type
		)
	;
	return NEW;
END;$$;


ALTER FUNCTION clin.trf_upd_intake_updates_all_drug_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_intake_updates_all_drug_components(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_upd_intake_updates_all_drug_components() IS 'If a drug component substance intake is updated all sibling components must receive some values thereof.';


--
-- Name: trf_upd_lc2sth_fk_generic_code(); Type: FUNCTION; Schema: clin; Owner: gm-dbo
--

CREATE FUNCTION clin.trf_upd_lc2sth_fk_generic_code() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	perform 1 from ref.coding_system_root where pk_coding_system = NEW.fk_generic_code;
	if FOUND then
		return NEW;
	end if;
	_msg := 'clin.trf_upd_lc2sth_fk_generic_code(): UPDATE of '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'fk_generic_code=(' || NEW.fk_generic_code || ') '
		|| 'does not exist in ref.coding_system_root.pk_coding_system, '
		|| 'old fk_generic_code=(' || OLD.fk_generic_code || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;$$;


ALTER FUNCTION clin.trf_upd_lc2sth_fk_generic_code() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_lc2sth_fk_generic_code(); Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON FUNCTION clin.trf_upd_lc2sth_fk_generic_code() IS 'Check foreign key integrity on update of *.fk_generic_code -> ref.coding_system_root.pk_coding_system.';


--
-- Name: add_external_id_type(text, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.add_external_id_type(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
declare
	_name alias for $1;
	_issuer alias for $2;
	_pk int;
begin
	select pk into _pk from dem.enum_ext_id_types where name = _name and issuer = _issuer;
	if FOUND then
		return _pk;
	end if;
	insert into dem.enum_ext_id_types(name, issuer) values (_name, _issuer);
	select currval(pg_get_serial_sequence('dem.enum_ext_id_types', 'pk')) into _pk;
	return _pk;
end;$_$;


ALTER FUNCTION dem.add_external_id_type(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION add_external_id_type(text, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.add_external_id_type(text, text) IS 'Add an external ID type if it does not exist yet.
	 This implementation is prone to concurrency issues.';


--
-- Name: add_name(integer, text, text, boolean); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.add_name(integer, text, text, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_id_identity alias for $1;
	_first alias for $2;
	_last alias for $3;
	_active alias for $4;
	_id integer;
BEGIN
    -- deactivate all the existing names if this name is to become active
	if _active then
		update dem.names set active = false where id_identity = _id_identity;
	end if;
	-- name already there for this identity ?
	select into _id id from dem.names where id_identity = _id_identity and firstnames = _first and lastnames = _last;
	if FOUND then
		update dem.names set active = _active where id = _id;
		return _id;
	end if;
	-- no, insert new name
	insert into dem.names (id_identity, firstnames, lastnames, active) values (_id_identity, _first, _last, _active);
	if FOUND then
		return currval('dem.names_id_seq');
	end if;
	return NULL;
END;$_$;


ALTER FUNCTION dem.add_name(integer, text, text, boolean) OWNER TO "gm-dbo";

--
-- Name: address_exists(text, text, text, text, text, text, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.address_exists(text, text, text, text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_code_country alias for $1;
	_code_region alias for $2;
	_urb alias for $3;
	_postcode alias for $4;
	_street alias for $5;
	_number alias for $6;
	_subunit alias for $7;
	__subunit text;
	_pk_address integer;
	msg text;
BEGIN
	if (_code_country || _code_region || _urb || _postcode || _street || _number) is NULL then
		msg := '[dem.address_exists]: insufficient or invalid address definition: '
			|| 'country code <' || coalesce(_code_country, 'NULL') || '>, '
			|| 'region code <' || coalesce(_code_region, 'NULL') || '>, '
			|| 'urb <' || coalesce(_urb, 'NULL') || '>, '
			|| 'zip <' || coalesce(_postcode, 'NULL') || '>, '
			|| 'street <' || coalesce(_street, 'NULL') || '>, '
			|| 'number <' || coalesce(_number, 'NULL') || '>'
		;
		raise exception '%', msg;
	end if;
	__subunit := nullif(trim(_subunit), '');
	if __subunit is null then
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_region = trim(_code_region)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit is null;
	else
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_region = trim(_code_region)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit = __subunit;
	end if;
	return _pk_address;
END;$_$;


ALTER FUNCTION dem.address_exists(text, text, text, text, text, text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION address_exists(text, text, text, text, text, text, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.address_exists(text, text, text, text, text, text, text) IS 'This function checks whether a given address exists in
the database and returns the primary key if found.
It takes the following parameters:
	country code,
	region code,
	urb (location),
	postcode,
	street,
	number,
	subunit (can be NULL)
';


--
-- Name: assert_unique_named_identity(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.assert_unique_named_identity() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_identity_row record;
	_names_row record;
	_names_pks integer[];
	_other_identities integer[];
BEGIN
	-- working on dem.identity
	if TG_TABLE_NAME = 'identity' then
		_identity_row := NEW;
		select array_agg(id) into _names_pks from dem.names where id_identity = NEW.pk;
	-- working on dem.names
	else
		select * into _identity_row from dem.identity where pk = NEW.id_identity;
		select ARRAY[NEW.id] into _names_pks;
	end if;
	-- loop over names rows belonging to identity
	FOR _names_row IN
		SELECT * FROM dem.names
		WHERE id = ANY(_names_pks)
	LOOP
		-- there must not be any combination of identical
		-- (dob, firstname, lastname, identity.comment)
		-- so, look for clashing rows
		SELECT array_agg(pk_identity) INTO _other_identities FROM
			dem.v_person_names d_vpn
				join dem.identity d_i on (d_i.pk = d_vpn.pk_identity)
		WHERE
			-- same firstname
			d_vpn.firstnames = _names_row.firstnames
				AND
			-- same lastname
			d_vpn.lastnames = _names_row.lastnames
				AND
			-- same gender
			d_i.gender is not distinct from _identity_row.gender
				AND
			-- same dob (day)
			date_trunc('day', d_i.dob) is not distinct from date_trunc('day', _identity_row.dob)
				AND
			-- same discriminator
			d_i.comment is not distinct from _identity_row.comment
				AND
			-- but not the currently updated or inserted row
			d_i.pk != _identity_row.pk
		;
		if coalesce(array_length(_other_identities, 1), 0) > 0 then
			RAISE EXCEPTION
				'[dem.assert_unique_named_identity] % on %.%: More than one person with (firstnames=%), (lastnames=%), (dob=%), (comment=%): % & %',
					TG_OP,
					TG_TABLE_SCHEMA,
					TG_TABLE_NAME,
					_names_row.firstnames,
					_names_row.lastnames,
					_identity_row.dob,
					_identity_row.comment,
					_identity_row.pk,
					_other_identities
				USING ERRCODE = 'unique_violation'
			;
			RETURN NULL;
		end if;
	END LOOP;
	return NEW;
END;$$;


ALTER FUNCTION dem.assert_unique_named_identity() OWNER TO "gm-dbo";

--
-- Name: FUNCTION assert_unique_named_identity(); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.assert_unique_named_identity() IS 'Ensures unique(identity.dob, names.firstnames, names.lastnames, identity.comment)';


--
-- Name: create_address(text, text, text, text, text, text, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.create_address(text, text, text, text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_number ALIAS FOR $1;
	_street ALIAS FOR $2;
	_postcode ALIAS FOR $3;
	_urb ALIAS FOR $4;
	_region_code ALIAS FOR $5;
	_country_code ALIAS FOR $6;
	_subunit alias for $7;
	_street_id integer;
	_pk_address integer;
	__subunit text;
	msg text;
BEGIN
	select into _pk_address dem.address_exists (
		_country_code,
		_region_code,
		_urb,
		_postcode,
		_street,
		_number,
		_subunit
	);
	if _pk_address is not null then
		return _pk_address;
	end if;
	-- this either creates dem.street and possible dem.urb rows or
	-- or else it fails (because region and/or country do not exist)
	select into _street_id dem.create_street(_street, _postcode, _urb, _region_code, _country_code);
	-- create address
	__subunit := nullif(trim(_subunit), '');
	insert into dem.address (
		number,
		id_street,
		subunit
	) values (
		_number,
		_street_id,
		__subunit
	)
	returning id
	into _pk_address;
	return _pk_address;
END;$_$;


ALTER FUNCTION dem.create_address(text, text, text, text, text, text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_address(text, text, text, text, text, text, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.create_address(text, text, text, text, text, text, text) IS 'This function creates an address. It first
checks whether the address already exists.
It takes the following parameters:
	number,
	street,
	postcode,
	urb (location),
	region code,
	country code,
	subunit (can be NULL)
If the country or the region do not exist
in the database, the function fails.
';


--
-- Name: create_comm_type(text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.create_comm_type(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_description alias for $1;
	_pk_type integer;
BEGIN
	select pk into _pk_type from dem.enum_comm_types where _(description) = _description;
	if FOUND then
		return _pk_type;
	end if;
	select pk into _pk_type from dem.enum_comm_types where description = _description;
	if FOUND then
		return _pk_type;
	end if;
	insert into dem.enum_comm_types(description) values (_description);
	select currval(pg_get_serial_sequence('dem.enum_comm_types', 'pk')) into _pk_type;
	return _pk_type;
END;$_$;


ALTER FUNCTION dem.create_comm_type(text) OWNER TO "gm-dbo";

--
-- Name: create_occupation(text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.create_occupation(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_job alias for $1;
	_id integer;
BEGIN
	select into _id id from dem.occupation where name = _job;
	if FOUND then
		return _id;
	end if;
	insert into dem.occupation (name) values (_job);
	return currval('dem.occupation_id_seq');
END;$_$;


ALTER FUNCTION dem.create_occupation(text) OWNER TO "gm-dbo";

--
-- Name: create_street(text, text, text, text, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.create_street(text, text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_street ALIAS FOR $1;
	_postcode ALIAS FOR $2;
	_urb ALIAS FOR $3;
	_state_code ALIAS FOR $4;
	_country_code ALIAS FOR $5;
	_urb_id integer;
	_street_id integer;
	msg text;
BEGIN
	-- create/get urb
	SELECT INTO _urb_id dem.create_urb(_urb, _postcode, _state_code, _country_code);
	-- create/get and return street
	SELECT INTO _street_id s.id from dem.street s WHERE s.name ILIKE _street AND s.id_urb = _urb_id AND postcode ILIKE _postcode;
	IF FOUND THEN
		RETURN _street_id;
	END IF;
	INSERT INTO dem.street (name, postcode, id_urb) VALUES (_street, _postcode, _urb_id);
	RETURN currval('dem.street_id_seq');
END;$_$;


ALTER FUNCTION dem.create_street(text, text, text, text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_street(text, text, text, text, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.create_street(text, text, text, text, text) IS 'This function takes a parameters the name of the street,

	the postal code, the name of the urb,

	the postcode of the urb, the code of the state and the

	code of the country.

	If the country or the state does not exists in the tables,

	the function fails.

	At first, both the urb and street are tried to be retrieved according to the

	supplied information. If the fields do not match exactly an

	existing row, a new urb is created or a new street is created and returned.';


--
-- Name: create_urb(text, text, text, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.create_urb(text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_urb ALIAS FOR $1;
	_urb_postcode ALIAS FOR $2;	
	_region_code ALIAS FOR $3;
	_country_code ALIAS FOR $4;
 	_region_pk integer;
	_urb_id integer;
	msg text;
BEGIN
 	-- get region
 	SELECT INTO _region_pk d_r.pk from dem.region d_r WHERE d_r.code = _region_code and d_r.country = _country_code;
 	IF NOT FOUND THEN
		msg := 'combination of region + country not registered ['
			||   'country:' || coalesce(_country_code, 'NULL')
			||  ', region:' || coalesce(_region_code, 'NULL')
			||     ', urb:' || coalesce(_urb, 'NULL')
			|| ', urb_zip:' || coalesce(_urb_postcode, 'NULL')
			|| ']';
		RAISE EXCEPTION '=> %', msg;
 	END IF;
	-- get/create and return urb
	SELECT INTO _urb_id u.id from dem.urb u WHERE u.name ILIKE _urb AND u.fk_region = _region_pk;
	IF FOUND THEN
		RETURN _urb_id;
	END IF;
	INSERT INTO dem.urb (name, postcode, fk_region) VALUES (_urb, _urb_postcode, _region_pk);
	RETURN currval('dem.urb_id_seq');
END;$_$;


ALTER FUNCTION dem.create_urb(text, text, text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_urb(text, text, text, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.create_urb(text, text, text, text) IS 'This function takes a parameters the name of the urb,\n
	the postcode of the urb, the name of the region and the\n
	name of the country.\n
	If the country or the region does not exists in the tables,\n
	the function fails.\n
	At first, the urb is tried to be retrieved according to the\n
	supplied information. If the fields do not match exactly an\n
	existing row, a new urb is created and returned.';


--
-- Name: date_trunc_utc(text, timestamp with time zone); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.date_trunc_utc(text, timestamp with time zone) RETURNS timestamp without time zone
    LANGUAGE sql IMMUTABLE
    AS $_$select date_trunc($1, $2 at time zone 'UTC');$_$;


ALTER FUNCTION dem.date_trunc_utc(text, timestamp with time zone) OWNER TO "gm-dbo";

--
-- Name: FUNCTION date_trunc_utc(text, timestamp with time zone); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.date_trunc_utc(text, timestamp with time zone) IS 'date_trunc() is not immutable because it depends on the timezone
	 setting, hence need to use this in index creation, but also need
	 to use it in queries which want to use that index, so make it
	 generally available as a function';


--
-- Name: dob_is_in_range(timestamp with time zone, interval, interval); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.dob_is_in_range(timestamp with time zone, interval, interval) RETURNS boolean
    LANGUAGE sql
    AS $_$
select
	($1 - (extract(year from $1) * '1 year'::interval)) -
	(now() - (extract(year from now()) * '1 year'::interval))
	between (-1 * $2) and $3$_$;


ALTER FUNCTION dem.dob_is_in_range(timestamp with time zone, interval, interval) OWNER TO "gm-dbo";

--
-- Name: f_always_active_name(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.f_always_active_name() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if NEW.active = false then
		raise exception 'Cannot delete/disable active name. Another name must be activated first.';
		return OLD;
	end if;
	return NEW;
END;
$$;


ALTER FUNCTION dem.f_always_active_name() OWNER TO "gm-dbo";

--
-- Name: f_delete_names(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.f_delete_names() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
BEGIN
	DELETE from dem.names WHERE id_identity=OLD.id;
	RETURN OLD;
END;$$;


ALTER FUNCTION dem.f_delete_names() OWNER TO "gm-dbo";

--
-- Name: f_uniq_active_name(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.f_uniq_active_name() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
--	tmp text;
BEGIN
--	tmp := 'identity:' || NEW.id_identity || ',id:' || NEW.id || ',name:' || NEW.firstnames || ' ' || NEW.lastnames;
--	raise notice 'uniq_active_name: [%]', tmp;
	if NEW.active = true then
		update dem.names set active = false
		where
			id_identity = NEW.id_identity
				and
			active = true;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION dem.f_uniq_active_name() OWNER TO "gm-dbo";

--
-- Name: gm_upd_default_regions(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.gm_upd_default_regions() RETURNS boolean
    LANGUAGE plpgsql
    AS $$
declare
	_region_code text;
	_region_name text;
	_country_row record;
begin
	_region_code := '??';
	_region_name := 'state/territory/province/region not available';
	-- add default region to countries needing one
	for _country_row in
		select distinct code from dem.country
		where code not in (
			select country from dem.region where code = _region_code
		)
	loop
		raise notice 'adding default region for [%]', _country_row.code;
		execute 'insert into dem.region (code, country, name) values ('
				|| quote_literal(_region_code) || ', '
				|| quote_literal(_country_row.code) || ', '
				|| quote_literal(_region_name) || ');';
	end loop;
	return true;
end;
$$;


ALTER FUNCTION dem.gm_upd_default_regions() OWNER TO "gm-dbo";

--
-- Name: remove_person(integer); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.remove_person(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_pk_identity alias for $1;
BEGIN
	-- does person exist ?
	perform 1 from dem.identity where pk = _pk_identity;
	if not FOUND then
		raise notice 'dem.remove_person(): dem.identity.pk=(%) does not exist, not removing', _pk_identity;
		return false;
	end if;
	-- we cannot just get the child tables of clin.clin_root_item and
	-- delete from them since they are inter-dependent and may require
	-- a particular order of deletion, so let us do that explicitely:
	DELETE FROM clin.clin_hx_family WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.vaccination WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy_state WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_diag WHERE fk_narrative IN (
		SELECT pk FROM clin.clin_narrative WHERE fk_encounter IN (
			select pk from clin.encounter where fk_patient = _pk_identity
		)
	);
	DELETE FROM clin.test_result WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.lab_request WHERE fk_encounter in (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.substance_intake WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.procedure WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_narrative WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM blobs.doc_med WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	-- now that we have deleted all the clinical data let us
	-- delete the EMR structural items as well:
	DELETE FROM clin.episode WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.health_issue WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.encounter WHERE fk_patient = _pk_identity;
	-- delete demographics details:
	DELETE FROM dem.identity_tag where fk_identity = _pk_identity;
	DELETE FROM dem.names WHERE id_identity = _pk_identity;
	-- eventually delete the identity itself which does
	-- not go down without some twisting of arms:
	ALTER TABLE dem.identity disable rule r_del_identity;
	DELETE FROM dem.identity WHERE pk = _pk_identity;
	ALTER TABLE dem.identity enable rule r_del_identity;
	return true;
END;$_$;


ALTER FUNCTION dem.remove_person(integer) OWNER TO "gm-dbo";

--
-- Name: FUNCTION remove_person(integer); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.remove_person(integer) IS 'Fully remove a person from the system - except
 everything is still in the audit tables ;-)';


--
-- Name: set_nickname(integer, text); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.set_nickname(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_id_identity alias for $1;
	_nick alias for $2;
	_names_row record;
	msg text;
BEGIN
	-- 0.1: Just always set the nickname inside the active name
	-- post 0.1: openEHR-like (name: pk, fk_identity, name, fk_type, comment, is_legal, is_active ...)
	-- does name exist ?
	select into _names_row * from dem.names where id_identity = _id_identity and active = true;
	if not found then
		msg := 'Cannot set nickname [' || _nick || ']. No active <names> row with id_identity [' || _id_identity || '] found.';
		raise exception '%', msg;
	end if;
	update dem.names set preferred = _nick where id = _names_row.id;
	return _names_row.id;
END;$_$;


ALTER FUNCTION dem.set_nickname(integer, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION set_nickname(integer, text); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.set_nickname(integer, text) IS 'Setting the nickname only makes sense for the currently active

	 name. However, we also want to keep track of previous nicknames.

	 Hence we would set the nickname right in the active name if

	 it is NULL. It it contains a previous nickname (eg IS NOT NULL)

	 we will inactivate the currently active name and copy it into

	 a new active name but with the nickname set to the new one.

	 Unsetting works the same (IOW *setting* to NULL).';


--
-- Name: trf_normalize_time_in_dob(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.trf_normalize_time_in_dob() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if NEW.dob is NULL then
		return NEW;
	end if;
	NEW.dob = date_trunc('day', NEW.dob) + '11 hours 11 minutes 11 seconds 111 milliseconds'::interval;
	return NEW;
END;$$;


ALTER FUNCTION dem.trf_normalize_time_in_dob() OWNER TO "gm-dbo";

--
-- Name: trf_null_empty_title(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.trf_null_empty_title() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	if (NEW.title is null) then
		return NEW;
	end if;
	if trim(NEW.title) <> '' then
		return NEW;
	end if;
	NEW.title := NULL;
	return NEW;
end;$$;


ALTER FUNCTION dem.trf_null_empty_title() OWNER TO "gm-dbo";

--
-- Name: trf_prevent_deletion_of_in_use_staff(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.trf_prevent_deletion_of_in_use_staff() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	-- does any audited table contain the dem.staff.db_user we are about to delete ?
	PERFORM 1 FROM audit.audit_fields WHERE modified_by = OLD.db_user LIMIT 1;
	IF FOUND THEN
		RAISE EXCEPTION
			'DELETE from dem.staff: Sanity check failed. User <%> is referenced from <.modified_by> of at least one audited table.', OLD.db_user
			USING ERRCODE = 'foreign_key_violation'
		;
		RETURN NULL;
	END IF;
	-- does any audit table contain the dem.staff.db_user we are about to delete ?
	PERFORM 1 FROM audit.audit_trail WHERE orig_by = OLD.db_user OR audit_by = OLD.db_user LIMIT 1;
	IF FOUND THEN
		RAISE EXCEPTION
			'DELETE from dem.staff: Sanity check failed. User <%> is referenced from <.orig_by> or <.audit_by> of at least one audit table.', OLD.db_user
			USING ERRCODE = 'foreign_key_violation'
		;
		RETURN NULL;
	END IF;
	RETURN OLD;
END;
$$;


ALTER FUNCTION dem.trf_prevent_deletion_of_in_use_staff() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_prevent_deletion_of_in_use_staff(); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.trf_prevent_deletion_of_in_use_staff() IS 'this function is used to prevent DELETEs of staff members which had been used to store data';


--
-- Name: trf_prevent_multiple_praxi(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.trf_prevent_multiple_praxi() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_branch_count integer;
	_pk_org_existing integer;
	_pk_org_prospective integer;
	_msg text;
BEGIN
	select count(1) into _branch_count from dem.praxis_branch;
	if TG_OP = 'INSERT' then
		-- first branch ever
		if _branch_count = 0 then
			return NEW;
		end if;
	end if;
	if TG_OP = 'UPDATE' then
		-- only one branch (which is being updated)
		if _branch_count = 1 then
			return NEW;
		end if;
	end if;
	-- now we have either got an INSERT with at least
	-- one praxis_branch already existing or we are
	-- UPDATEing one of several existing branches
	SELECT fk_org INTO _pk_org_existing FROM dem.org_unit
	WHERE pk = (SELECT fk_org_unit FROM dem.praxis_branch LIMIT 1);
	SELECT fk_org INTO _pk_org_prospective
	FROM dem.org_unit WHERE pk = NEW.fk_org_unit;
	if _pk_org_prospective = _pk_org_existing then
		return NEW;
	end if;
	_msg := '[dem.trf_prevent_multiple_praxi] '
			|| TG_OP || ': '
			|| 'Existing praxis branches (dem.praxis_branch.fk_org->dem.org_unit.pk->dem.org_unit.fk_org) belong to an org with dem.org.pk='
			|| _pk_org_existing
			|| '. Cannot link praxis branch (dem.praxis_branch.fk_org=dem.org_unit.pk='
			|| NEW.fk_org_unit
			||') to a different org (dem.org.pk='
			|| _pk_org_prospective
			||'). There can only be one praxis (=dem.org) per database.';
	raise exception unique_violation using message = _msg;
	return NULL;
END;$$;


ALTER FUNCTION dem.trf_prevent_multiple_praxi() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_prevent_multiple_praxi(); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.trf_prevent_multiple_praxi() IS 'Prevent praxis branches to be defined for more than one dem.org.';


--
-- Name: trf_protect_active_name_of_person(); Type: FUNCTION; Schema: dem; Owner: gm-dbo
--

CREATE FUNCTION dem.trf_protect_active_name_of_person() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_name_count integer;
	_msg text;
BEGIN
	-- how many names are there for the identity at the end of the Tx ?
	select count(1) into _name_count
	from dem.names
	where
		id_identity = OLD.id_identity
			and
		active is true
	;
	-- less than one name ?
	if _name_count < 1 then
		_msg := 'person ' || OLD.id_identity || ' must have at least one, active, name entry';
		raise exception check_violation using message = _msg;
		return OLD;
	end if;
	return OLD;
END;$$;


ALTER FUNCTION dem.trf_protect_active_name_of_person() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_protect_active_name_of_person(); Type: COMMENT; Schema: dem; Owner: gm-dbo
--

COMMENT ON FUNCTION dem.trf_protect_active_name_of_person() IS 'A person must have at least one, active, name record.';


--
-- Name: account_is_dbowner_or_staff(name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.account_is_dbowner_or_staff(_account name) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	_is_owner boolean;
BEGIN
	-- is _account member of current db group ?
--	PERFORM 1 FROM pg_auth_members
--	WHERE
--		roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database())
--			AND
--		member = (SELECT oid FROM pg_roles WHERE rolname = _account)
--	;
--	IF FOUND THEN
--		-- should catch people on staff, gm-dbo, and postgres
--		RETURN TRUE;
--	END IF;
	-- postgres
	IF _account = 'postgres' THEN
		RETURN TRUE;
	END IF;
	-- on staff list
	PERFORM 1 FROM dem.staff WHERE db_user = _account;
	IF FOUND THEN
		RETURN TRUE;
	END IF;
	-- owner
	SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = current_database();
	IF _is_owner IS TRUE THEN
		RETURN TRUE;
	END IF;
	RETURN FALSE;
END;$$;


ALTER FUNCTION gm.account_is_dbowner_or_staff(_account name) OWNER TO "gm-dbo";

--
-- Name: add_missing_array_bits(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.add_missing_array_bits() RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
BEGIN
	-- array_agg
	perform 1 from pg_catalog.pg_aggregate where aggfnoid::oid = (select oid from pg_catalog.pg_proc where proname = 'array_agg'::name limit 1);
	if FOUND then
		raise NOTICE '[gm.add_missing_array_bits]: aggregate <array_agg> already exists';
	else
		raise NOTICE '[gm.add_missing_array_bits]: aggregate <array_agg> does not exist (probably PostgreSQL <8.4), creating';
		CREATE AGGREGATE array_agg(anyelement) (
			SFUNC = array_append,
			STYPE = anyarray,
			INITCOND = '{}'
		);
		comment on aggregate array_agg(anyelement) is
			'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
	end if;
	-- unnest()
	perform 1 from pg_catalog.pg_proc where
		proname = 'array_unnest'::name
			and
		pronamespace = (select oid from pg_namespace where nspname = 'gm'::name)
	;
	if FOUND then
		raise NOTICE '[gm.add_missing_array_bits]: function "gm.array_unnest()" already exists';
	else
		raise NOTICE '[gm.add_missing_array_bits]: function "gm.array_unnest()" does not exist, creating';
		CREATE OR REPLACE FUNCTION gm.array_unnest(anyarray)
			RETURNS SETOF anyelement
			AS '
		SELECT $1[i] FROM
			generate_series (
				array_lower($1,1),
				array_upper($1,1)
			) i
		;'
			LANGUAGE 'sql'
			IMMUTABLE
		;
		comment on function gm.array_unnest(anyarray) is
			'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
	end if;
	perform 1 from pg_catalog.pg_proc where
		proname = 'unnest'::name
			and
		pronamespace = (select oid from pg_namespace where nspname = 'pg_catalog'::name)
	;
	if FOUND then
		raise NOTICE '[gm.add_missing_array_bits]: function "pg_catalog.unnest()" exists';
		-- also exists in public ?
		perform 1 from pg_catalog.pg_proc where
			proname = 'unnest'::name
				and
			pronamespace = (select oid from pg_namespace where nspname = 'public'::name)
		;
		if FOUND then
			raise NOTICE '[gm.add_missing_array_bits]: function "unnest()" also exists in schema "public", removing';
			drop function public.unnest(anyarray) cascade;
		end if;
	else
		raise NOTICE '[gm.add_missing_array_bits]: function "pg_catalog.unnest()" does not exist (probably PostgreSQL <8.4)';
		-- exists in public ?
		perform 1 from pg_catalog.pg_proc where
			proname = 'unnest'::name
				and
			pronamespace = (select oid from pg_namespace where nspname = 'public'::name)
		;
		if FOUND then
			raise NOTICE '[gm.add_missing_array_bits]: function "public.unnest()" already exists';
		else
			raise NOTICE '[gm.add_missing_array_bits]: function "public.unnest()" does not exist either, creating';
			CREATE OR REPLACE FUNCTION public.unnest(anyarray)
				RETURNS SETOF anyelement
				AS 'SELECT gm.array_unnest($1);'
				LANGUAGE 'sql'
				IMMUTABLE
			;
			comment on function public.unnest(anyarray) is
				'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
		end if;
	end if;
	return TRUE;
END;$_$;


ALTER FUNCTION gm.add_missing_array_bits() OWNER TO "gm-dbo";

--
-- Name: FUNCTION add_missing_array_bits(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.add_missing_array_bits() IS 'Add array aggregate and array unnesting to PostgreSQL versions lacking this functionality (IOW < 8.4).';


--
-- Name: add_table_for_notifies(name, name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.add_table_for_notifies(name, name) RETURNS boolean
    LANGUAGE sql
    AS $_$select gm.register_notifying_table($1, $2);$_$;


ALTER FUNCTION gm.add_table_for_notifies(name, name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION add_table_for_notifies(name, name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.add_table_for_notifies(name, name) IS 'Mark given table for notification trigger generator.
	 Parameters are: (schema, table).
	 Defaults signal to table name.';


--
-- Name: add_user_to_permission_group(name, name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.add_user_to_permission_group(name, name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_username alias for $1;
	_target_group alias for $2;
	_valid_groups name[];
	_group name;
	_query text;
BEGIN
	-- verify user
	perform 1 from pg_user where usename = _username;
	if not FOUND then
		raise warning '[gm.add_user_to_permission_group]: user [%] does not exist', _username;
		return False;
	end if;
	-- verify logical group validity
	-- no "gm-nurse", "gm-admin" just yet
	_valid_groups := ARRAY[quote_ident('gm-public'), quote_ident('gm-staff'), quote_ident('gm-doctors')];
	if quote_ident(_target_group) <> all(_valid_groups) then
		raise warning '[gm.add_user_to_permission_group]: invalid group [%]', _target_group;
		return False;
	end if;
	-- verify group existance
	perform 1 from pg_group where groname = _target_group;
	if not FOUND then
		raise warning '[gm.add_user_to_permission_group]: group [%] does not exist', _target_group;
		return False;
	end if;
	-- drop user from all groups
	--FOREACH _group IN ARRAY _valid_groups LOOP
	FOR _group IN SELECT unnest(_valid_groups) LOOP
		_query := 'alter group ' || _group || ' drop user ' || quote_ident(_username) || ';';
		execute _query;
	END LOOP;
	-- add user to desired group
	_query := 'alter group ' || quote_ident(_target_group) || ' add user ' || quote_ident(_username) || ';';
	execute _query;
	return True;
END;$_$;


ALTER FUNCTION gm.add_user_to_permission_group(name, name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION add_user_to_permission_group(name, name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.add_user_to_permission_group(name, name) IS 'Only gm-dbo is GRANTed EXECUTE on this function. This way users need
 to know the gm-dbo (GNUmed admin) password to execute it.';


--
-- Name: array_unnest(anyarray); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.array_unnest(anyarray) RETURNS SETOF anyelement
    LANGUAGE sql IMMUTABLE
    AS $_$
		SELECT $1[i] FROM
			generate_series (
				array_lower($1,1),
				array_upper($1,1)
			) i
		;$_$;


ALTER FUNCTION gm.array_unnest(anyarray) OWNER TO "gm-dbo";

--
-- Name: FUNCTION array_unnest(anyarray); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.array_unnest(anyarray) IS 'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';


--
-- Name: concat_table_structure(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_struct text;
begin
	select into _struct gm.concat_table_structure_v19_and_up();
	return _struct;
end;
$$;


ALTER FUNCTION gm.concat_table_structure() OWNER TO "gm-dbo";

--
-- Name: concat_table_structure(integer); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure(integer) RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
declare
	_db_ver alias for $1;
	_struct text;
begin
	if _db_ver < 6 then
		select into _struct gm.concat_table_structure_v1();
		return _struct;
	end if;
	if _db_ver < 8 then
		select into _struct gm.concat_table_structure_v2();
		return _struct;
	end if;
	if _db_ver < 16 then
		select into _struct gm.concat_table_structure_v3();
		return _struct;
	end if;
	if _db_ver < 17 then
		select into _struct gm.concat_table_structure_v16_and_up();
		return _struct;
	end if;
	if _db_ver < 18 then
		select into _struct gm.concat_table_structure_v17_and_up();
		return _struct;
	end if;
	if _db_ver < 19 then
		select into _struct gm.concat_table_structure_v18_and_up();
		return _struct;
	end if;
	select into _struct gm.concat_table_structure_v19_and_up();
	return _struct;
end;
$_$;


ALTER FUNCTION gm.concat_table_structure(integer) OWNER TO "gm-dbo";

--
-- Name: concat_table_structure_v1(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v1() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_row record;
	_total text;
begin
	_total := '';
	-- schema.table.column.data_type
	for _row in
		select * from information_schema.columns cols
			where cols.table_name in (
				select tabs.table_name from information_schema.tables tabs where
					-- those which hold clinical data
					tabs.table_schema in ('public', 'dem', 'clin', 'blobs') and
					tabs.table_type = 'BASE TABLE'
				)
			order by
				md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
	loop
		_total := _total
			|| _row.table_schema || '.'
			|| _row.table_name || '.'
			|| _row.column_name || '::'
			|| _row.udt_name || '
';
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v1() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v1(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v1() IS 'copy of gm_concat_table_structure() until gnumed_v5, works on public, dem, clin, blobs';


--
-- Name: concat_table_structure_v16_and_up(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v16_and_up() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_row record;
	_total text;
begin
	_total := '';
	-- schema.table.column.data_type
	for _row in
		select *
		from information_schema.columns cols
		where
			cols.table_name in (
				select tabs.table_name from information_schema.tables tabs where
					-- those which hold clinical data
					tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n')
						and
					tabs.table_type = 'BASE TABLE'
			) and
			cols.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n')
		order by
			decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
	loop
		_total := _total
			|| _row.table_schema || '.'
			|| _row.table_name || '.'
			|| _row.column_name || '::'
			|| _row.udt_name || E'
';
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v16_and_up() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v16_and_up(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v16_and_up() IS 'new concat_table_structure() starting with gnumed_v16,
	 works on dem, clin, blobs, cfg, ref, i18n,
	 sorts properly by bytea';


--
-- Name: concat_table_structure_v17_and_up(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v17_and_up() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_row record;
	_total text;
begin
	_total := '';
	-- schema.table.column.data_type
	for _row in
		select *
		from information_schema.columns cols
		where
			cols.table_name in (
				select tabs.table_name from information_schema.tables tabs where
					tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
						and
					tabs.table_type = 'BASE TABLE'
			) and
			cols.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
		order by
			decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
	loop
		_total := _total
			|| _row.table_schema || '.'
			|| _row.table_name || '.'
			|| _row.column_name || '::'
			|| _row.udt_name || E'
';
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v17_and_up() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v17_and_up(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v17_and_up() IS 'new concat_table_structure() starting with gnumed_v17,
	 works on dem, clin, blobs, cfg, ref, i18n, bill,
	 sorts properly by bytea';


--
-- Name: concat_table_structure_v18_and_up(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v18_and_up() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_table_desc record;
	_pk_desc record;
	_column_desc record;
	_total text;
begin
	_total := '';
	-- find relevant tables
	for _table_desc in
		select * from information_schema.tables tabs where
			tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
				and
			tabs.table_type = 'BASE TABLE'
		order by
			decode(md5(tabs.table_schema || tabs.table_name), 'hex')
	-- loop over tables
	loop
		-- where are we at ?
		_total := _total || 'TABLE:' || _table_desc.table_schema || '.' || _table_desc.table_name || E'
';
		-- find PKs of that table
		for _pk_desc in
			select * from (
				select
					pg_class.oid::regclass || '.' || pg_attribute.attname || '::' || format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS primary_key_column
				from
					pg_index, pg_class, pg_attribute
				where
					--pg_class.oid = 'TABLENAME'::regclass
					pg_class.oid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
						AND 
					indrelid = pg_class.oid
						AND
					pg_attribute.attrelid = pg_class.oid
						AND
					pg_attribute.attnum = any(pg_index.indkey)
						AND
					indisprimary
				) AS PKs
			order by
				decode(md5(PKs.primary_key_column), 'hex')
		-- and loop over those PK columns
		loop
			_total := _total || 'PK:' || _pk_desc.primary_key_column	|| E'
';
		end loop;
		-- find columns of that table
		for _column_desc in
			select *
			from information_schema.columns cols
			where
				cols.table_name = _table_desc.table_name
					and
				cols.table_schema = _table_desc.table_schema
			order by
				decode(md5(cols.column_name || cols.data_type), 'hex')
		-- and loop over those columns
		loop
			-- add columns in the format "schema.table.column::data_type"
			_total := _total || 'COL:'
				|| _column_desc.table_schema || '.'
				|| _column_desc.table_name || '.'
				|| _column_desc.column_name || '::'
				|| _column_desc.udt_name || E'
';
		end loop;
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v18_and_up() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v18_and_up(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v18_and_up() IS 'new concat_table_structure() starting with gnumed_v18,
	 works on dem, clin, blobs, cfg, ref, i18n, bill,
	 includes primary keys,
	 sorts properly by bytea';


--
-- Name: concat_table_structure_v19_and_up(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v19_and_up() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_table_desc record;
	_pk_desc record;
	_column_desc record;
	_constraint_def record;
	_total text;
begin
	_total := '';
	-- find relevant tables
	for _table_desc in
		select * from information_schema.tables tabs where
			tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
				and
			tabs.table_type = 'BASE TABLE'
		order by
			decode(md5(tabs.table_schema || tabs.table_name), 'hex')
	-- loop over tables
	loop
		-- where are we at ?
		_total := _total || 'TABLE:' || _table_desc.table_schema || '.' || _table_desc.table_name || E'\n';
		-- find PKs of that table
		for _pk_desc in
			select * from (
				select
					pg_class.oid::regclass || '.' || pg_attribute.attname || '::' || format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS primary_key_column
				from
					pg_index, pg_class, pg_attribute
				where
					--pg_class.oid = 'TABLENAME'::regclass
					pg_class.oid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
						AND 
					indrelid = pg_class.oid
						AND
					pg_attribute.attrelid = pg_class.oid
						AND
					pg_attribute.attnum = any(pg_index.indkey)
						AND
					indisprimary
				) AS PKs
			order by
				decode(md5(PKs.primary_key_column), 'hex')
		-- and loop over those PK columns
		loop
			_total := _total || 'PK:' || _pk_desc.primary_key_column	|| E'\n';
		end loop;
		-- find columns of that table
		for _column_desc in
			select *
			from information_schema.columns cols
			where
				cols.table_name = _table_desc.table_name
					and
				cols.table_schema = _table_desc.table_schema
			order by
				decode(md5(cols.column_name || cols.data_type), 'hex')
		-- and loop over those columns
		loop
			-- add columns in the format "schema.table.column::data_type"
			_total := _total || 'COL:'
				|| _column_desc.table_schema || '.'
				|| _column_desc.table_name || '.'
				|| _column_desc.column_name || '::'
				|| _column_desc.udt_name || E'\n';
		end loop;
		-- find and loop over CONSTRAINTs of that table
		for _constraint_def in
			select * from
				(select
					tbl.contype,
					'CONSTRAINT:type='
						|| tbl.contype::TEXT || ':'
						|| replace(pg_catalog.pg_get_constraintdef(tbl.oid, true), ' ', '_')
						|| '::active='
						|| tbl.convalidated::TEXT
					 as condef
				from pg_catalog.pg_constraint tbl
				where
					tbl.conrelid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
					-- include FKs only because we may have to add/remove
					-- other (say, check) constraints in a minor release
					-- for valid reasons which we do not want to affect
					-- the hash, if however we need to modify a foreign
					-- key that would, indeed, warrant a hash change
						AND
					tbl.contype = 'f'
				) as CONSTRAINTs
			order by
				CONSTRAINTs.contype,
				decode(md5(CONSTRAINTs.condef), 'hex')
		loop
			_total := _total || _constraint_def.condef || E'\n';
		end loop;
	end loop;		-- over tables
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v19_and_up() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v19_and_up(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v19_and_up() IS 'new concat_table_structure() starting with gnumed_v19,
	 works on dem, clin, blobs, cfg, ref, i18n, bill,
	 includes primary keys and constraints,
	 sorts properly by bytea';


--
-- Name: concat_table_structure_v2(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v2() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_row record;
	_total text;
begin
	_total := '';
	-- schema.table.column.data_type
	for _row in
		select * from information_schema.columns cols
			where cols.table_name in (
				select tabs.table_name from information_schema.tables tabs where
					-- those which hold clinical data
					tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n') and
					tabs.table_type = 'BASE TABLE'
				)
			order by
				md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
	loop
		_total := _total
			|| _row.table_schema || '.'
			|| _row.table_name || '.'
			|| _row.column_name || '::'
			|| _row.udt_name || '
';
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v2() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v2(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v2() IS 'new concat_table_structure() starting with gnumed_v6, works on dem, clin, blobs, cfg, ref, i18n';


--
-- Name: concat_table_structure_v3(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.concat_table_structure_v3() RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
declare
	_row record;
	_total text;
begin
	_total := '';
	-- schema.table.column.data_type
	for _row in
		select * from information_schema.columns cols
			where cols.table_name in (
				select tabs.table_name from information_schema.tables tabs where
					-- those which hold clinical data
					tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n') and
					tabs.table_type = 'BASE TABLE'
				)
			order by
				decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
	loop
		_total := _total
			|| _row.table_schema || '.'
			|| _row.table_name || '.'
			|| _row.column_name || '::'
			|| _row.udt_name || '
';
	end loop;
	return _total;
end;
$$;


ALTER FUNCTION gm.concat_table_structure_v3() OWNER TO "gm-dbo";

--
-- Name: FUNCTION concat_table_structure_v3(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.concat_table_structure_v3() IS 'new concat_table_structure() starting with gnumed_v8,
	 works on dem, clin, blobs, cfg, ref, i18n,
	 sorts properly by bytea';


--
-- Name: create_all_enc_epi_sanity_check_triggers(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_all_enc_epi_sanity_check_triggers() RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	_schema name;
	_table name;
	_qualified_table2check name;
	_fk_encounter_col name;
	_fk_episode_col name;
	_total_success boolean;
BEGIN
	raise notice 'gm.create_all_enc_epi_sanity_check_triggers()';
	_total_success := True;
	-- loop over tables with encounter AND episode FKs
	-- (assumes there is only one of each)
	for _schema, _table in
		select
			(select pg_n.nspname from pg_catalog.pg_namespace pg_n where pg_n.oid = pg_c.relnamespace),
			pg_c.relname
		from pg_class pg_c
		where pg_c.oid in (
			select distinct fk_tbl.conrelid
			from pg_catalog.pg_constraint fk_tbl
			where
				exists (
					select 1 from pg_catalog.pg_constraint fk_tbl1
					where
						fk_tbl1.contype = 'f'
							and
						fk_tbl1.conrelid = fk_tbl.conrelid
							and
						fk_tbl1.confrelid = 'clin.encounter'::regclass
							and
						fk_tbl1.confkey[1] = (
							select attnum from pg_catalog.pg_attribute col_tbl
							where
								col_tbl.attname = 'pk'
									AND
								col_tbl.attrelid = 'clin.encounter'::regclass
						)
				)
					and
				exists (
					select 1 from pg_catalog.pg_constraint fk_tbl2
					where
						fk_tbl2.contype = 'f'
							and
						fk_tbl2.conrelid = fk_tbl.conrelid
							and
						fk_tbl2.confrelid = 'clin.episode'::regclass
							and
						fk_tbl2.confkey[1] = (
							select attnum from pg_catalog.pg_attribute col_tbl
							where
								col_tbl.attname = 'pk'
									AND
								col_tbl.attrelid = 'clin.episode'::regclass
						)
				)
		)
	loop
		_qualified_table2check := _schema || '.' || _table;
		raise notice 'gm.create_all_enc_epi_sanity_check_triggers(): processing %', _qualified_table2check;
		-- find encounter FK column name
		select col_tbl.attname into _fk_encounter_col
		from pg_catalog.pg_attribute col_tbl
		where
			col_tbl.attrelid = _qualified_table2check::regclass
				and
			col_tbl.attnum = (
				select fk_tbl.conkey[1]
				from pg_catalog.pg_constraint fk_tbl
				where
					fk_tbl.contype = 'f'
						and
					fk_tbl.conrelid = _qualified_table2check::regclass
						and
					fk_tbl.confrelid = 'clin.encounter'::regclass
						and
					fk_tbl.confkey[1] = (
						select col_tbl1.attnum
						from pg_catalog.pg_attribute col_tbl1
						where
							col_tbl1.attname = 'pk'
								AND
							col_tbl1.attrelid = 'clin.encounter'::regclass
					)
				)
			;
		-- find episode FK column name
		select col_tbl.attname into _fk_episode_col
		from pg_catalog.pg_attribute col_tbl
		where
			col_tbl.attrelid = _qualified_table2check::regclass
				and
			col_tbl.attnum = (
				select fk_tbl.conkey[1]
				from pg_catalog.pg_constraint fk_tbl
				where
					fk_tbl.contype = 'f'
						and
					fk_tbl.conrelid = _qualified_table2check::regclass
						and
					fk_tbl.confrelid = 'clin.episode'::regclass
						and
					fk_tbl.confkey[1] = (
						select col_tbl1.attnum
						from pg_catalog.pg_attribute col_tbl1
						where
							col_tbl1.attname = 'pk'
								AND
							col_tbl1.attrelid = 'clin.episode'::regclass
					)
				)
			;
		-- now create the trigger
		BEGIN
			PERFORM gm.create_enc_epi_sanity_check_trigger(_schema, _table, _fk_encounter_col, _fk_episode_col);
		EXCEPTION
			WHEN undefined_table OR invalid_foreign_key THEN
				raise warning 'gm.create_all_enc_epi_sanity_check_triggers(): error processing <%.%>, skipping', _schema, _table;
				_total_success := False;
		END;
	end loop;
	return _total_success;
END;
$$;


ALTER FUNCTION gm.create_all_enc_epi_sanity_check_triggers() OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_all_enc_epi_sanity_check_triggers(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.create_all_enc_epi_sanity_check_triggers() IS '(Re)create sanity check triggers for all tables which have both fk_encounter and fk_episode.';


--
-- Name: create_all_table_mod_triggers(boolean); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_all_table_mod_triggers(_drop_old_triggers boolean) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	_notify_table record;
	_cmd text;
	_total_success boolean;
BEGIN
	_total_success := True;
	-- loop over registered tables
	for _notify_table in select * from gm.notifying_tables loop
		BEGIN
			PERFORM gm.create_table_mod_triggers(_notify_table.schema_name, _notify_table.table_name);
		EXCEPTION
			WHEN undefined_table OR undefined_column THEN
				raise warning 'gm.create_all_table_mod_triggers(): error processing <%.%>, skipping', _notify_table.schema_name, _notify_table.table_name;
				_total_success := False;
		END;
	end loop;
	return _total_success;
END;
$$;


ALTER FUNCTION gm.create_all_table_mod_triggers(_drop_old_triggers boolean) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_all_table_mod_triggers(_drop_old_triggers boolean); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.create_all_table_mod_triggers(_drop_old_triggers boolean) IS '(Re)create all table mod triggers for all registered tables.';


--
-- Name: create_enc_epi_sanity_check_trigger(name, name, name, name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_enc_epi_sanity_check_trigger(_schema_name name, _table_name name, _fk_encounter_col name, _fk_episode_col name) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
	_qualified_table2check text;
	_msg text;
	_cmd text;
BEGIN
	_qualified_table2check := _schema_name || '.' || _table_name;
	raise notice 'gm.create_enc_epi_sanity_check_trigger(): % (.% vs .%)', _qualified_table2check, _fk_encounter_col, _fk_episode_col;
	-- verify table exists
	if not exists(select 1 from information_schema.tables where table_schema = _schema_name and table_name = _table_name) then
		raise warning 'gm.create_enc_epi_sanity_check_trigger(): table <%> does not exist', _qualified_table2check;
		raise exception undefined_table;
		return false;
	end if;
	-- verify that it points to clin.encounter.pk
	if not exists (
		select 1
		from
			pg_catalog.pg_constraint fk_tbl
		where
			fk_tbl.contype = 'f'
				AND
			fk_tbl.confrelid = 'clin.encounter'::regclass
				AND
			fk_tbl.conrelid = _qualified_table2check::regclass
				AND
			fk_tbl.confkey[1] = (
				select attnum from pg_catalog.pg_attribute col_tbl
				where
					col_tbl.attname = 'pk'
						AND
					col_tbl.attrelid = 'clin.encounter'::regclass
			)
				AND
			fk_tbl.conkey[1] = (
				select attnum from pg_catalog.pg_attribute col_tbl
				where
					col_tbl.attname = _fk_encounter_col
						AND
					col_tbl.attrelid = _qualified_table2check::regclass
			)
	) then
		raise exception
			'gm.create_enc_epi_sanity_check_trigger(): <%.%> does not point to clin.encounter.pk', _qualified_table2check, _fk_encounter_col
			USING ERRCODE = 'invalid_foreign_key'
		;
		return false;
	end if;
	-- verify that it points to clin.episode.pk
	if not exists (
		select 1
		from
			pg_catalog.pg_constraint fk_tbl
		where
			fk_tbl.contype = 'f'
				AND
			fk_tbl.confrelid = 'clin.episode'::regclass
				AND
			fk_tbl.conrelid = _qualified_table2check::regclass
				AND
			fk_tbl.confkey[1] = (
				select attnum from pg_catalog.pg_attribute col_tbl
				where
					col_tbl.attname = 'pk'
						AND
					col_tbl.attrelid = 'clin.episode'::regclass
			)
				AND
			fk_tbl.conkey[1] = (
				select attnum from pg_catalog.pg_attribute col_tbl
				where
					col_tbl.attname = _fk_episode_col
						AND
					col_tbl.attrelid = _qualified_table2check::regclass
			)
	) then
		raise exception
			'gm.create_enc_epi_sanity_check_trigger(): <%.%> does not point to clin.episode.pk', _qualified_table2check, _fk_episode_col
			USING ERRCODE = 'invalid_foreign_key'
		;
		return false;
	end if;
	-- re-create trigger
	_cmd := 'drop trigger if exists tr_sanity_check_enc_epi_ins_upd on ' || _qualified_table2check || ' cascade';
	execute _cmd;
	_cmd := 'create trigger tr_sanity_check_enc_epi_ins_upd ';
	_cmd := _cmd || 'before insert or update ';
	_cmd := _cmd || 'on ' || _qualified_table2check || ' ';
	_cmd := _cmd || 'for each row when (NEW.fk_episode is not null) ';
	_cmd := _cmd || 'execute procedure clin.trf_sanity_check_enc_epi_ins_upd(''' || _fk_encounter_col || ''', ''' || _fk_episode_col || ''')';
	execute _cmd;
	return True;
END;
$$;


ALTER FUNCTION gm.create_enc_epi_sanity_check_trigger(_schema_name name, _table_name name, _fk_encounter_col name, _fk_episode_col name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_enc_epi_sanity_check_trigger(_schema_name name, _table_name name, _fk_encounter_col name, _fk_episode_col name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.create_enc_epi_sanity_check_trigger(_schema_name name, _table_name name, _fk_encounter_col name, _fk_episode_col name) IS 'This function can be run on any table in order to add enccounter <-> episode sanity check triggers to that table.';


--
-- Name: create_generic_combi_vaccine(text, text[], text, boolean); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_generic_combi_vaccine(text, text[], text, boolean) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_name alias for $1;
	_indications alias for $2;
	_atc alias for $3;
	_is_live alias for $4;
	_generic_name text;
	_pk_brand integer;
	_pk_vaccine integer;
	_indication text;
BEGIN
	_generic_name := _name || ' - generic vaccine';
	raise notice 're-creating [%] (%)', _generic_name, array_to_string(_indications, '-');
	-- retrieve or create ref.branded_drug entry for indication
	select pk into _pk_brand from ref.branded_drug
	where
		is_fake is true
			and
		description = _generic_name;
	if FOUND is false then
		insert into ref.branded_drug (
			description,
			preparation,
			is_fake,
			atc_code
		) values (
			_generic_name,
			'vaccine',		-- this is rather arbitrary
			True,
			coalesce(_atc, 'J07')
		)
		returning pk
		into _pk_brand;
	end if;
	-- retrieve or create clin.vaccine entry for generic brand
	select pk into _pk_vaccine from clin.vaccine
	where fk_brand = _pk_brand;
	if FOUND is false then
		insert into clin.vaccine (
			is_live,
			fk_brand
		) values (
			_is_live,
			_pk_brand
		)
		returning pk
		into _pk_vaccine;
	end if;
	-- link indications to vaccine
	delete from clin.lnk_vaccine2inds
	where
		fk_vaccine = _pk_vaccine;
	for _indication in select unnest(_indications) loop
		insert into clin.lnk_vaccine2inds (
			fk_vaccine,
			fk_indication
		) values (
			_pk_vaccine,
			(select id from clin.vacc_indication where description = _indication)
		);
	end loop;
	return true;
END;$_$;


ALTER FUNCTION gm.create_generic_combi_vaccine(text, text[], text, boolean) OWNER TO "gm-dbo";

--
-- Name: create_table_mod_triggers(name, name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_table_mod_triggers(_schema_name name, _table_name name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_qualified_table text;
	_msg text;
	_payload text;
	_PK_col_name text;
	_pk_accessor_SQL text;
	_accessor_col text;
	_col_candidate text;
	_identity_accessor_SQL text;
	_cmd text;
BEGIN
	_qualified_table := _schema_name || '.' || _table_name;
	raise notice 'gm.create_table_mod_triggers(): %', _qualified_table;
	-- verify table exists
	if not exists(select 1 from information_schema.tables where table_schema = _schema_name and table_name = _table_name) then
		raise warning 'gm.create_table_mod_triggers(): table <%> does not exist', _qualified_table;
		raise exception undefined_table;
		return false;
	end if;
	-- find PK column
	select
		pg_attribute.attname into _PK_col_name
	from
		pg_index, pg_class, pg_attribute
	where
		pg_class.oid = _qualified_table::regclass
			AND
		indrelid = pg_class.oid
			AND
		pg_attribute.attrelid = pg_class.oid
			AND
		pg_attribute.attnum = any(pg_index.indkey)
			AND
		indisprimary;
	if _PK_col_name is NULL then
		raise warning 'gm.create_table_mod_triggers(): table <%> lacks a primary key', _qualified_table;
		raise exception undefined_column;
		return false;
	end if;
	_pk_accessor_SQL := 'select $1.' || _PK_col_name;
	-- find identity accessor
	-- special case
	if _qualified_table = 'dem.identity' then
		_identity_accessor_SQL := 'select $1.pk';
	else
		-- look for columns by which to retrieve affected person
		_accessor_col := NULL;
		foreach _col_candidate in array array['fk_identity', 'fk_patient', 'id_identity', 'fk_encounter'] loop
			if exists (
				select 1 from pg_class, pg_attribute where
				pg_class.oid = _qualified_table::regclass
					AND
				pg_attribute.attname = _col_candidate
					AND
				pg_attribute.attrelid = pg_class.oid
			) then
				_accessor_col := _col_candidate;
				exit;
			end if;
		end loop;
		if _accessor_col = 'fk_encounter' then					-- retrieve identity PK via fk_encounter
			_identity_accessor_SQL := 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1';
		elsif _accessor_col = 'fk_identity' then					-- retrieve identity PK via fk_identity
			_identity_accessor_SQL := 'select $1.fk_identity';
		elsif _accessor_col = 'fk_patient' then					-- retrieve identity PK via fk_patient
			_identity_accessor_SQL := 'select $1.fk_patient';
		elsif _accessor_col = 'id_identity' then					-- retrieve identity PK via id_identity
			_identity_accessor_SQL := 'select $1.id_identity';
		else
			_identity_accessor_SQL := '<NULL>';
		end if;
	end if;
	-- drop triggers should they exist
	-- new-name announcement triggers
	_cmd := 'drop trigger if exists zzz_tr_announce_' || _schema_name || '_' || _table_name || '_ins_upd on ' || _qualified_table || ' cascade;';
	execute _cmd;
	_cmd := 'drop trigger if exists zzz_tr_announce_' || _schema_name || '_' || _table_name || '_del on ' || _qualified_table || ' cascade;';
	execute _cmd;
	-- re-create triggers
	-- 1) INSERT/UPDATE
	_payload := 'table=' || _qualified_table || '::PK name=' || _PK_col_name;
	_cmd := 'create constraint trigger zzz_tr_announce_' || _schema_name || '_' || _table_name || '_ins_upd';
	_cmd := _cmd || ' after insert or update';
	_cmd := _cmd || ' on ' || _qualified_table;
	-- needed so a SELECT inside, say, _identity_accessor_SQL running
	-- concurrently to a "lengthy" TX does not create a serialization
	-- failure by being a rw-dependancy pivot
	_cmd := _cmd || ' deferrable initially deferred';
	_cmd := _cmd || ' for each row';
	_cmd := _cmd || ' execute procedure gm.trf_announce_table_ins_upd(''' || _payload || ''', ''' || _pk_accessor_SQL || ''', ''' || _identity_accessor_SQL || ''');';
	execute _cmd;
	-- 2) DELETE
	_payload := 'operation=DELETE::' || _payload;
	_cmd := 'create constraint trigger zzz_tr_announce_' || _schema_name || '_' || _table_name || '_del';
	_cmd := _cmd || ' after delete';
	_cmd := _cmd || ' on ' || _qualified_table;
	-- needed so a SELECT inside, say, _identity_accessor_SQL running
	-- concurrently to a "lengthy" TX does not create a serialization
	-- failure by being a rw-dependancy pivot
	_cmd := _cmd || ' deferrable initially deferred';
	_cmd := _cmd || ' for each row';
	_cmd := _cmd || ' execute procedure gm.trf_announce_table_del(''' || _payload || ''', ''' || _pk_accessor_SQL || ''', ''' || _identity_accessor_SQL || ''');';
	execute _cmd;
	return True;
END;
$_$;


ALTER FUNCTION gm.create_table_mod_triggers(_schema_name name, _table_name name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_table_mod_triggers(_schema_name name, _table_name name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.create_table_mod_triggers(_schema_name name, _table_name name) IS 'This function can be run on any table in order to add notification triggers to that table.';


--
-- Name: create_user(name, text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.create_user(name, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_username alias for $1;
	_password alias for $2;
	_database text;
	_query text;
BEGIN
	perform 1 from pg_user where usename = _username;
	if not FOUND then
		_query := 'create user ' || quote_ident(_username)
					|| ' with password ' || quote_literal(_password)
					|| ';';
		execute _query;
		perform 1 from pg_user where usename = _username;
		if not FOUND then
			raise exception 'cannot create user [%]', _username;
			return false;
		end if;
	end if;
	_query := 'alter group "gm-logins" add user ' || quote_ident(_username) || ';';
	execute _query;
	_query := 'alter group "gm-public" add user ' || quote_ident(_username) || ';';
	execute _query;
	-- satisfy "database = samerole" in pg_hba.conf
	select into _database current_database();
	_query := 'alter group ' || quote_ident(_database) || ' add user ' || quote_ident(_username) || ';';
	execute _query;
	return true;
END;$_$;


ALTER FUNCTION gm.create_user(name, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION create_user(name, text); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.create_user(name, text) IS 'To create users one needs to have CREATEROLE rights. Only gm-dbo is
 GRANTed EXECUTE. This way users need to know the gm-dbo (GNUmed admin)
 password to execute the function.
 Newly created users belong to group "gm-public" by default.';


--
-- Name: disable_user(name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.disable_user(name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_username alias for $1;
	_query text;
BEGIN
	perform 1 from pg_user where usename = _username;
	if not FOUND then
		return true;
	end if;
	_query := 'alter group "gm-logins" drop user ' || quote_ident(_username) || ';';
	execute _query;
	return true;
END;$_$;


ALTER FUNCTION gm.disable_user(name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION disable_user(name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.disable_user(name) IS 'To disable users one needs to have CREATEROLE rights.
 Only gm-dbo is GRANTed EXECUTE.
 This way users need to know the gm-dbo (GNUmed admin) password
 to execute the function.';


--
-- Name: drop_user(name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.drop_user(name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_username alias for $1;
	_query text;
BEGIN
	perform 1 from pg_user where usename = _username;
	if not FOUND then
		return true;
	end if;
	_query := 'drop user ' || quote_ident(_username) || ';';
	execute _query;
	perform 1 from pg_user where usename = _username;
	if FOUND then
		return false;
	end if;
	return true;
END;$_$;


ALTER FUNCTION gm.drop_user(name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION drop_user(name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.drop_user(name) IS 'To drop users one needs to have CREATEROLE rights.
 Only gm-dbo is GRANTed EXECUTE.
 This way users need to know the gm-dbo (GNUmed admin) password
 to execute the function.';


--
-- Name: get_users(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.get_users() RETURNS text[]
    LANGUAGE sql
    AS $$select gm.get_users(current_database());$$;


ALTER FUNCTION gm.get_users() OWNER TO "gm-dbo";

--
-- Name: get_users(name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.get_users(name) RETURNS text[]
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_db alias for $1;
	_gm_users text[];
	_user text;
BEGIN
	-- GNUmed group roles
	_gm_users := ARRAY['gm-logins', 'gm-public', 'gm-doctors', 'gm-staff', _db];
	-- add roles being *members* of groups gm-logins, gm-public, _db
	FOR _user in
		select distinct rolname from pg_roles where oid in (
			select member from pg_auth_members where roleid in (
				select oid from pg_roles where rolname in ('gm-logins', 'gm-public', _db)
			)
		)
	LOOP
		continue when _user = 'postgres';
		continue when _user = any(_gm_users);
		_gm_users := _gm_users || _user;
	END LOOP;
	-- add roles mentioned in any *.modified_by fields
	FOR _user in select distinct modified_by from audit.audit_fields LOOP
		continue when _user = 'postgres';
		continue when _user = any(_gm_users);
		_gm_users := _gm_users || _user;
	END LOOP;
	-- add roles mentioned in dem.staff.db_user
	FOR _user in select distinct db_user from dem.staff LOOP
		continue when _user = 'postgres';
		continue when _user = any(_gm_users);
		_gm_users := _gm_users || _user;
	END LOOP;
	return _gm_users;
END;$_$;


ALTER FUNCTION gm.get_users(name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION get_users(name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.get_users(name) IS 'Convenience function listing all PostgreSQL accounts (roles) needed for a consistent dump of the database.';


--
-- Name: lo_chunked_md5(oid, integer); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.lo_chunked_md5(oid, integer) RETURNS text
    LANGUAGE plpgsql STABLE STRICT
    AS $_$
DECLARE
	lo_id alias for $1;
	chunk_size alias for $2;
	_lo_fd integer;
	_lo_size integer;
	_chunk_count integer;
	_remainder integer;
	_md5_concat text;
	INV_READ constant integer := x'40000'::integer;
	SEEK_SET constant integer := 0;
	SEEK_END constant integer := 2;
BEGIN
	-- check for existence of lo_id ?
	_lo_fd := lo_open(lo_id, INV_READ);
	-- get size
	_lo_size := lo_lseek(_lo_fd, 0, SEEK_END);
	PERFORM lo_close(_lo_fd);						-- move further down if loread() proves faster
	-- calculate chunks and remainder
	_chunk_count := _lo_size / chunk_size;
	_remainder := _lo_size % chunk_size;
	-- loop over chunks
	_md5_concat := '';
	FOR _chunk_id in 1.._chunk_count LOOP
		_md5_concat := _md5_concat || md5(lo_get(lo_id, (_chunk_id - 1) * chunk_size, chunk_size));
		-- using loread() may be faster (as it directly accesses the
		-- existing lo_fd and thusly does not need to re-open the LO
		-- each round)
	END LOOP;
	-- add remainder
	_md5_concat := _md5_concat || md5(lo_get(lo_id, _chunk_count * chunk_size, _remainder));
	return md5(_md5_concat);
END;$_$;


ALTER FUNCTION gm.lo_chunked_md5(oid, integer) OWNER TO "gm-dbo";

--
-- Name: FUNCTION lo_chunked_md5(oid, integer); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.lo_chunked_md5(oid, integer) IS 'Function to create a chunked md5 sum on arbitrarily large LARGE OBJECTs.';


--
-- Name: load_auto_explain(integer); Type: FUNCTION; Schema: gm; Owner: postgres
--

CREATE FUNCTION gm.load_auto_explain(_min_duration integer) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $$
BEGIN
	IF _min_duration < 1500 THEN
		_min_duration := 1500;
	END IF;
	LOAD 'auto_explain';
	PERFORM set_config('auto_explain.log_min_duration'::TEXT, _min_duration::TEXT, false);
	PERFORM set_config('auto_explain.log_analyze'::TEXT, 'ON'::TEXT, false);
	PERFORM set_config('auto_explain.log_verbose'::TEXT, 'ON'::TEXT, false);
	PERFORM set_config('auto_explain.log_timing'::TEXT, 'ON'::TEXT, false);
	PERFORM set_config('auto_explain.log_nested_statements'::TEXT, 'ON'::TEXT, false);
	RETURN TRUE;
END;$$;


ALTER FUNCTION gm.load_auto_explain(_min_duration integer) OWNER TO postgres;

--
-- Name: FUNCTION load_auto_explain(_min_duration integer); Type: COMMENT; Schema: gm; Owner: postgres
--

COMMENT ON FUNCTION gm.load_auto_explain(_min_duration integer) IS 'Load and configure auto_explain.
.
Integer argument = auto_explain.log_min_duration in milliseconds with a lower bound of 1500.';


--
-- Name: log_access2emr(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.log_access2emr(text) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_action alias for $1;
BEGIN
	if gm.is_null_or_blank_string(_action) then
		raise exception 'gm.log_access2emr(): action detail cannot be NULL or empty';
	end if;
	insert into gm.access_log (user_action) values ('EMR access: ' || _action);
	return;
END;$_$;


ALTER FUNCTION gm.log_access2emr(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION log_access2emr(text); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.log_access2emr(text) IS 'This logs access to a patient EMR.';


--
-- Name: log_other_access(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.log_other_access(text) RETURNS void
    LANGUAGE sql SECURITY DEFINER
    AS $_$insert into gm.access_log (user_action) values ($1);$_$;


ALTER FUNCTION gm.log_other_access(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION log_other_access(text); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.log_other_access(text) IS 'This logs access to the database.';


--
-- Name: log_script_insertion(text, text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.log_script_insertion(text, text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
declare
	_filename alias for $1;
	_version alias for $2;
	_hash text;
begin
	delete from gm.schema_revision where filename = _filename;
	insert into gm.schema_revision (filename, version) values (
		_filename,
		_version
	);
	perform gm.log_other_access (
		'database change script inserted: ' || _filename || ' (' || _version || ')'
	);
	select into _hash md5(gm.concat_table_structure());
	return _hash;
end;$_$;


ALTER FUNCTION gm.log_script_insertion(text, text) OWNER TO "gm-dbo";

--
-- Name: nullify_empty_string(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.nullify_empty_string(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_input alias for $1;
BEGIN
	if _input is null then
		return null;
	end if;
	if trim(_input) = '' then
		return null;
	end if;
	return _input;
END;$_$;


ALTER FUNCTION gm.nullify_empty_string(text) OWNER TO "gm-dbo";

--
-- Name: register_notifying_table(name, name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.register_notifying_table(name, name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_namespace alias for $1;
	_table alias for $2;
	dummy RECORD;
BEGIN
	-- does table exist ?
	select relname into dummy from pg_class where
		relname = _table and
		relnamespace = (select oid from pg_namespace where nspname = _namespace)
	;
	if not found then
		raise exception 'register_notifying_table(): Table [%.%] does not exist.', _namespace, _table;
	end if;
	-- make sure we can insert
	delete from gm.notifying_tables where
		table_name = _table
		and schema_name = _namespace;
	insert into gm.notifying_tables (
		schema_name,
		table_name
	) values (
		_namespace,
		_table
	);
	return true;
END;$_$;


ALTER FUNCTION gm.register_notifying_table(name, name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION register_notifying_table(name, name); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.register_notifying_table(name, name) IS 'Register given table for notification trigger generator. Parameters are: (schema, table)';


--
-- Name: strip_allzeros_fraction(numeric); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.strip_allzeros_fraction(numeric) RETURNS numeric
    LANGUAGE plpgsql STRICT
    AS $_$
DECLARE
	_numeric_value alias for $1;
	_fraction numeric;
	_msg text;
BEGIN
	_fraction := _numeric_value - trunc(_numeric_value);
	if _fraction <> 0 then
		return _numeric_value;
	end if;
	BEGIN
		return _numeric_value::bigint::numeric;
	EXCEPTION
		WHEN numeric_value_out_of_range THEN
			RAISE NOTICE '[gm.strip_allzeros_fraction]: cannot strip from %', _numeric_value;
			RETURN _numeric_value;
	END;
END;$_$;


ALTER FUNCTION gm.strip_allzeros_fraction(numeric) OWNER TO "gm-dbo";

--
-- Name: FUNCTION strip_allzeros_fraction(numeric); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.strip_allzeros_fraction(numeric) IS 'Remove fractions containing only zeros (n.000...) from NUMERICs/DECIMALs.';


--
-- Name: transfer_users(text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.transfer_users(text) RETURNS boolean
    LANGUAGE sql
    AS $_$select gm.transfer_users($1, current_database());$_$;


ALTER FUNCTION gm.transfer_users(text) OWNER TO "gm-dbo";

--
-- Name: transfer_users(text, text); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.transfer_users(text, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_source_group alias for $1;
	_target_group alias for $2;
	member_ids int[];
	member_id int;
	member_name text;
	tmp text;
BEGIN
	-- source group exists ?
	perform 1 from pg_group where groname = _source_group;
	if not FOUND then
		raise exception 'gm_transfer_users(): source group [%] does not exist', _source_group;
		return false;
	end if;
	-- target group exists ?
	perform 1 from pg_group where groname = _target_group;
	if not FOUND then
		raise exception 'gm_transfer_users(): target group [%] does not exist', _target_group;
		return false;
	end if;
	-- loop over group member IDs
	select into member_ids grolist from pg_group where groname = _source_group;
	FOR idx IN coalesce(array_lower(member_ids, 1), 0) .. coalesce(array_upper(member_ids, 1), -1) LOOP
		member_id := member_ids[idx];
		select into member_name usename from pg_user where usesysid = member_id;
		tmp := 'gm_transfer_users(text): transferring "'
				|| member_name || '" ('
				|| member_id || ') from group "'
				|| _source_group || '" to group "'
				|| _target_group || '"';
		raise notice '%', tmp;
		-- satisfy "database = samegroup" in pg_hba.conf
		tmp := 'alter group ' || quote_ident(_target_group) || ' add user ' || quote_ident(member_name) || ';';
		execute tmp;
	end LOOP;
	return true;
END;$_$;


ALTER FUNCTION gm.transfer_users(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION transfer_users(text, text); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.transfer_users(text, text) IS 'This function transfers adds users from the group role given in the
 argument to the group role corresponding to the current database
 name. This enables group membership based authentication as used
 in GNUmed. This operation is typically only run on database upgrade
 and is only available to gm-dbo.';


--
-- Name: trf_announce_table_del(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.trf_announce_table_del() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_payload text;
	_pk_accessor_SQL text;
	_pk_col_val integer;
	_identity_accessor_SQL text;
	_pk_identity integer;
begin
	_pk_accessor_SQL := TG_ARGV[1];
	EXECUTE _pk_accessor_SQL INTO STRICT _pk_col_val USING OLD;
	_payload := TG_ARGV[0] || '::row PK=' || coalesce(_pk_col_val::text, 'NULL');
	_identity_accessor_SQL := TG_ARGV[2];
	if _identity_accessor_SQL <> '<NULL>' then
		--raise notice '%.%: %', TG_TABLE_SCHEMA, TG_TABLE_NAME, _identity_accessor_SQL;
		EXECUTE _identity_accessor_SQL INTO STRICT _pk_identity USING OLD;
		_payload := _payload || '::person PK=' || coalesce(_pk_identity::text, 'NULL');
	end if;
	perform pg_notify('gm_table_mod', _payload);
	return NULL;
end;
$$;


ALTER FUNCTION gm.trf_announce_table_del() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_announce_table_del(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.trf_announce_table_del() IS 'Trigger function announcing a DELETE on a table.
sends signal: gm_table_mod
payload:
	operation=DELETE,
	table=the table that is updated,
	PK name=the name of the PK column of the table (requires single column PKs),
	row PK=the PK of the affected row,
	person PK=the PK of the affected person,
';


--
-- Name: trf_announce_table_ins_upd(); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.trf_announce_table_ins_upd() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	_payload text;
	_pk_accessor_SQL text;
	_pk_col_val integer;
	_identity_accessor_SQL text;
	_pk_identity integer;
begin
	_pk_accessor_SQL := TG_ARGV[1];
	EXECUTE _pk_accessor_SQL INTO STRICT _pk_col_val USING NEW;
	_payload := 'operation=' || TG_OP || '::' || TG_ARGV[0] || '::row PK=' || coalesce(_pk_col_val::text, 'NULL');
	_identity_accessor_SQL := TG_ARGV[2];
	if _identity_accessor_SQL <> '<NULL>' then
		EXECUTE _identity_accessor_SQL INTO STRICT _pk_identity USING NEW;
		_payload := _payload || '::person PK=' || coalesce(_pk_identity::text, 'NULL');
	end if;
	perform pg_notify('gm_table_mod', _payload);
	return NULL;
end;
$$;


ALTER FUNCTION gm.trf_announce_table_ins_upd() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_announce_table_ins_upd(); Type: COMMENT; Schema: gm; Owner: gm-dbo
--

COMMENT ON FUNCTION gm.trf_announce_table_ins_upd() IS 'Trigger function announcing an INSERT or UPDATE to a table.
sends signal: gm_table_mod
payload:
	operation=INSERT/UPDATE,
	table=the table that is updated,
	PK name=the name of the PK column of the table (requires single column PKs),
	row PK=the PK of the affected row,
	person PK=the PK of the affected person,
';


--
-- Name: user_exists(name); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.user_exists(name) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
BEGIN
	perform 1 from pg_user where usename = $1;
	if not FOUND then
		raise notice 'Cannot set database language. User % does not exist.', $1;
		return false;
	end if;
	return true;
END;$_$;


ALTER FUNCTION gm.user_exists(name) OWNER TO "gm-dbo";

--
-- Name: xid2int(xid); Type: FUNCTION; Schema: gm; Owner: gm-dbo
--

CREATE FUNCTION gm.xid2int(xid) RETURNS integer
    LANGUAGE sql
    AS $_$select $1::text::integer;$_$;


ALTER FUNCTION gm.xid2int(xid) OWNER TO "gm-dbo";

--
-- Name: _(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n._(text) RETURNS text
    LANGUAGE sql
    AS $_$select i18n._($1, i18n.get_curr_lang())$_$;


ALTER FUNCTION i18n._(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION _(text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n._(text) IS 'will return either the translation into
	 i18n.curr_lang.lang for the current user
	 or the input,
	 created in public schema for easy access';


--
-- Name: _(text, text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n._(text, text) RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_orig alias for $1;
	_lang alias for $2;
	trans_str text;
BEGIN
	select into trans_str trans from i18n.translations where lang = _lang and orig = _orig;
	if not found then
		-- reduce xx_XX@YY to xx
		select into trans_str trans from i18n.translations where lang = regexp_replace(_lang, '_.*$', '') and orig = _orig;
		if not found then
			return _orig;
		end if;
--		if not found then
--			-- check "generic" dummy translation used to work around accentuation problems
--			select into trans_str trans from i18n.translations where lang = 'generic' and orig = _orig;
--
--			if not found then
--				return _orig;
--			end if;
--		end if;
	end if;
	return trans_str;
END;
$_$;


ALTER FUNCTION i18n._(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION _(text, text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n._(text, text) IS 'will return either the translation into <lang>::text
 (2nd argument) for the current user or the input,
 created in public schema for easy access,
 will fallback to "xx" if xx_XX does not exist';


--
-- Name: force_curr_lang(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.force_curr_lang(text) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
    _lang ALIAS FOR $1;
BEGIN
    raise notice 'Forcing current language to [%] without checking for translations..', _lang;
    delete from i18n.curr_lang where db_user = SESSION_USER;
	insert into i18n.curr_lang (lang, db_user) values (_lang, SESSION_USER);
    return true;
END;
$_$;


ALTER FUNCTION i18n.force_curr_lang(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION force_curr_lang(text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.force_curr_lang(text) IS 'force preferred language to some language:
	 - for "current user"';


--
-- Name: get_curr_lang(); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.get_curr_lang() RETURNS text
    LANGUAGE sql
    AS $$select i18n.get_curr_lang(CURRENT_USER)$$;


ALTER FUNCTION i18n.get_curr_lang() OWNER TO "gm-dbo";

--
-- Name: get_curr_lang(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.get_curr_lang(text) RETURNS text
    LANGUAGE sql
    AS $_$select lang from i18n.curr_lang where db_user = $1$_$;


ALTER FUNCTION i18n.get_curr_lang(text) OWNER TO "gm-dbo";

--
-- Name: i18n(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.i18n(text) RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	original ALIAS FOR $1;
BEGIN
	if not exists(select pk from i18n.keys where orig = original) then
		insert into i18n.keys (orig) values (original);
	end if;
	return original;
END;
$_$;


ALTER FUNCTION i18n.i18n(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION i18n(text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.i18n(text) IS 'insert original strings into i18n.keys for later translation';


--
-- Name: set_curr_lang(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.set_curr_lang(text) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_lang ALIAS FOR $1;
BEGIN
	if exists(select pk from i18n.translations where lang = _lang) then
		delete from i18n.curr_lang where db_user = SESSION_USER;
		insert into i18n.curr_lang (lang, db_user) values (_lang, SESSION_USER);
		return true;
	end if;
	raise notice 'Cannot set current language to [%]. No translations available.', _lang;
	return false;
END;
$_$;


ALTER FUNCTION i18n.set_curr_lang(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION set_curr_lang(text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.set_curr_lang(text) IS 'set preferred language:
	 - for "current (session) user"
	 - only if translations for this language are available';


--
-- Name: set_curr_lang(text, name); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.set_curr_lang(text, name) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_lang ALIAS FOR $1;
	_db_user ALIAS FOR $2;
	lang_has_tx boolean;
BEGIN
	select into lang_has_tx exists(select pk from i18n.translations where lang = _lang);
	if lang_has_tx is False then
		raise notice 'Cannot set current language to [%]. No translations available.', _lang;
		return False;
	end if;
	delete from i18n.curr_lang where db_user = _db_user;
	insert into i18n.curr_lang (db_user, lang) values (_db_user, _lang);
	return true;
END;
$_$;


ALTER FUNCTION i18n.set_curr_lang(text, name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION set_curr_lang(text, name); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.set_curr_lang(text, name) IS 'set language to first argument for the user named in
	 the second argument if translations are available';


--
-- Name: tx_or_null(text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.tx_or_null(text) RETURNS text
    LANGUAGE sql
    AS $_$select i18n.tx_or_null($1, i18n.get_curr_lang())$_$;


ALTER FUNCTION i18n.tx_or_null(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION tx_or_null(text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.tx_or_null(text) IS 'will return either the translation into i18n.curr_lang.lang for the current user or null';


--
-- Name: tx_or_null(text, text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.tx_or_null(text, text) RETURNS text
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
DECLARE
	_orig alias for $1;
	_lang alias for $2;
	trans_str text;
BEGIN
	select into trans_str trans from i18n.translations where lang = _lang and orig = _orig;
	if not found then
		return null;
	end if;
	return trans_str;
END;
$_$;


ALTER FUNCTION i18n.tx_or_null(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION tx_or_null(text, text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.tx_or_null(text, text) IS 'will return either the translation into language <text> (2nd argument) or null';


--
-- Name: unset_curr_lang(); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.unset_curr_lang() RETURNS void
    LANGUAGE sql
    AS $$select i18n.unset_curr_lang(CURRENT_USER);$$;


ALTER FUNCTION i18n.unset_curr_lang() OWNER TO "gm-dbo";

--
-- Name: FUNCTION unset_curr_lang(); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.unset_curr_lang() IS 'unset the db language for the current user';


--
-- Name: unset_curr_lang(name); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.unset_curr_lang(name) RETURNS void
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
BEGIN
	delete from i18n.curr_lang where db_user = $1;
	return;
END;$_$;


ALTER FUNCTION i18n.unset_curr_lang(name) OWNER TO "gm-dbo";

--
-- Name: FUNCTION unset_curr_lang(name); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.unset_curr_lang(name) IS 'unset the db language for a user (thereby reverting to the default English)';


--
-- Name: untranslate(text, text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.untranslate(text, text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
	_trans alias for $1;
	_lang alias for $2;
	_orig text;
BEGIN
	select orig into _orig
	from i18n.translations
	where
		trans = _trans
			and
		lang = _lang;
	return _orig;
END;$_$;


ALTER FUNCTION i18n.untranslate(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION untranslate(text, text); Type: COMMENT; Schema: i18n; Owner: gm-dbo
--

COMMENT ON FUNCTION i18n.untranslate(text, text) IS 'Return "original" from a "translated" string (1st argument) as per language (2nd argument).';


--
-- Name: upd_tx(text, text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.upd_tx(text, text) RETURNS boolean
    LANGUAGE sql
    AS $_$select i18n.upd_tx((select i18n.get_curr_lang()), $1, $2)$_$;


ALTER FUNCTION i18n.upd_tx(text, text) OWNER TO "gm-dbo";

--
-- Name: upd_tx(text, text, text); Type: FUNCTION; Schema: i18n; Owner: gm-dbo
--

CREATE FUNCTION i18n.upd_tx(text, text, text) RETURNS boolean
    LANGUAGE plpgsql SECURITY DEFINER
    AS $_$
declare
	_lang alias for $1;
	_orig alias for $2;
	_trans alias for $3;
	_tmp text;
begin
	if _lang is null then
		raise notice 'i18n.upd_tx(text, text, text): Cannot create translation for language <NULL>.';
		return False;
	end if;
	if _trans = _orig then
		raise notice 'i18n.upd_tx(text, text, text): Original = translation. Skipping.';
		return True;
	end if;
	select into _tmp '1' from i18n.keys where orig = _orig;
	if not found then
		raise notice 'i18n.upd_tx(text, text, text): [%] not found in i18n.keys. Creating entry.', _orig;
		insert into i18n.keys (orig) values (_orig);
	end if;
	delete from i18n.translations where lang = _lang and orig = _orig;
	insert into i18n.translations (lang, orig, trans) values (_lang, _orig, _trans);
	raise notice 'i18n.upd_tx(%: [%] ==> [%])', _lang, _orig, _trans;
	return True;
end;$_$;


ALTER FUNCTION i18n.upd_tx(text, text, text) OWNER TO "gm-dbo";

--
-- Name: _(text); Type: FUNCTION; Schema: public; Owner: gm-dbo
--

CREATE FUNCTION public._(text) RETURNS text
    LANGUAGE sql
    AS $_$select i18n._($1)$_$;


ALTER FUNCTION public._(text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION _(text); Type: COMMENT; Schema: public; Owner: gm-dbo
--

COMMENT ON FUNCTION public._(text) IS 'will return either the translation into
	 i18n.curr_lang.lang for the current user
	 or the input,
	 created in public schema for easy access';


--
-- Name: _(text, text); Type: FUNCTION; Schema: public; Owner: gm-dbo
--

CREATE FUNCTION public._(text, text) RETURNS text
    LANGUAGE sql
    AS $_$select i18n._($1, $2)$_$;


ALTER FUNCTION public._(text, text) OWNER TO "gm-dbo";

--
-- Name: FUNCTION _(text, text); Type: COMMENT; Schema: public; Owner: gm-dbo
--

COMMENT ON FUNCTION public._(text, text) IS 'will return either the translation into <text>
	 (2nd argument) for the current user or the input,
	 created in public schema for easy access';


--
-- Name: f_rfi_type2item(); Type: FUNCTION; Schema: public; Owner: gm-dbo
--

CREATE FUNCTION public.f_rfi_type2item() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
	dummy integer;
	msg text;
begin
	-- does fk_item change at all ?
	if TG_OP = 'UPDATE' then
		if NEW.fk_item = OLD.fk_item then
			return NEW;
		end if;
	end if;
	-- check referential integrity
	select into dummy 1 from clin.clin_root_item where pk_item=NEW.fk_item;
	if not found then
		msg := 'referential integrity violation: clin.lnk_type2item.fk_item [' || NEW.fk_item || '] not in <clin_root_item.pk_item>';
		raise exception '%', msg;
		return NULL;
	end if;
	return NEW;
end;
$$;


ALTER FUNCTION public.f_rfi_type2item() OWNER TO "gm-dbo";

--
-- Name: FUNCTION f_rfi_type2item(); Type: COMMENT; Schema: public; Owner: gm-dbo
--

COMMENT ON FUNCTION public.f_rfi_type2item() IS 'function used to check referential integrity from
	 clin.lnk_type2item to clin.clin_root_item with a custom trigger';


--
-- Name: f_set_encounter_timezone(); Type: FUNCTION; Schema: public; Owner: gm-dbo
--

CREATE FUNCTION public.f_set_encounter_timezone() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	if TG_OP = 'INSERT' then
		NEW.source_time_zone := (select (extract(timezone from (select now()))::text || 'seconds')::interval);
	else
		NEW.source_time_zone := OLD.source_time_zone;
	end if;
	return NEW;
end;
$$;


ALTER FUNCTION public.f_set_encounter_timezone() OWNER TO "gm-dbo";

--
-- Name: trf_announce_substance_in_brand_mod_no_pk(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_announce_substance_in_brand_mod_no_pk() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
	execute 'notify "substance_in_brand_mod_db:"';
	return NULL;
end;
$$;


ALTER FUNCTION ref.trf_announce_substance_in_brand_mod_no_pk() OWNER TO "gm-dbo";

--
-- Name: trf_assert_product_has_components(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_assert_product_has_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	PERFORM 1 FROM ref.lnk_dose2drug WHERE fk_drug_product = NEW.pk LIMIT 1;
	IF FOUND THEN
		RETURN NEW;
	END IF;
	_msg := '[ref.trf_assert_product_has_components()]: '
		|| TG_OP
		|| ' failed: no components (doses) linked to drug product ['
		|| NEW.pk
		|| '].'
	;
	RAISE EXCEPTION integrity_constraint_violation using message = _msg;
	RETURN NEW;
END;$$;


ALTER FUNCTION ref.trf_assert_product_has_components() OWNER TO "gm-dbo";

--
-- Name: trf_assert_product_keeps_components(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_assert_product_keeps_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	PERFORM 1 FROM ref.lnk_dose2drug WHERE fk_drug_product = OLD.pk LIMIT 1;
	-- any components left ?
	IF FOUND THEN
		-- does not matter
		RETURN OLD;
	END IF;
	PERFORM 1 FROM ref.drug_product WHERE pk = OLD.fk_drug_product LIMIT 1;
	-- drug AND components deleted ?
	IF NOT FOUND THEN
		-- does not matter
		RETURN OLD;
	END IF;
	_msg := '[ref.trf_assert_product_keeps_components()]: '
		|| TG_OP
		|| ' failed: no components (doses) linked to drug product ['
		|| OLD.pk
		|| '] anymore.'
	;
	RAISE EXCEPTION integrity_constraint_violation using message = _msg;
	-- does not matter
	RETURN OLD;
END;$$;


ALTER FUNCTION ref.trf_assert_product_keeps_components() OWNER TO "gm-dbo";

--
-- Name: trf_consumable_subst_normalize_amount(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_consumable_subst_normalize_amount() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	NEW.amount := gm.strip_allzeros_fraction(NEW.amount);
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_consumable_subst_normalize_amount() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_consumable_subst_normalize_amount(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_consumable_subst_normalize_amount() IS 'On INSERT/UPDATE drop .000 all-zero fractions from amounts.';


--
-- Name: trf_del_ref_code_tbl_check_backlink(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_del_ref_code_tbl_check_backlink() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return OLD;
	end if;
	_msg := 'ref.trf_del_ref_code_tbl_check_backlink(): DELETE from '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;$$;


ALTER FUNCTION ref.trf_del_ref_code_tbl_check_backlink() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_del_ref_code_tbl_check_backlink(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_del_ref_code_tbl_check_backlink() IS 'When deleting from any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.';


--
-- Name: trf_do_not_update_component_if_taken_by_patient(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_do_not_update_component_if_taken_by_patient() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if OLD.fk_brand = NEW.fk_brand then
		if OLD.fk_substance = NEW.fk_substance then
			return NEW;
		end if;
	end if;
	perform 1 from clin.substance_intake c_si
	where c_si.fk_drug_component = OLD.pk
	limit 1;
	if NOT FOUND then
		return NEW;
	end if;
	raise exception '[ref.trf_do_not_update_component_if_taken_by_patient]: as long as drug component <%> is taken by a patient you cannot modify it', OLD.pk;
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_do_not_update_component_if_taken_by_patient() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_do_not_update_component_if_taken_by_patient(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_do_not_update_component_if_taken_by_patient() IS 'If this drug component is taken by any patient do not modify it (
amount, unit, substance, brand).';


--
-- Name: trf_do_not_update_substance_if_taken_by_patient(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_do_not_update_substance_if_taken_by_patient() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	-- allow for case insensitive non-changes
	if upper(OLD.description) = upper(NEW.description) then
		if OLD.amount = NEW.amount then
			if upper(OLD.unit) = upper(NEW.unit) then
				return NEW;
			end if;
		end if;
	end if;
	_msg := '[ref.trf_do_not_update_substance_if_taken_by_patient]: as long as substance <' || OLD.description || '> is taken by a patient you cannot modify it';
	perform 1 from clin.substance_intake c_si
	where c_si.fk_substance = OLD.pk
	limit 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	PERFORM 1
	FROM clin.substance_intake c_si
	WHERE c_si.fk_drug_component IN (
		-- get all PKs in component link table which
		-- represent the substance we want to modify
		SELECT
			r_ls2b.pk
		FROM
			ref.lnk_substance2brand r_ls2b
		WHERE
			r_ls2b.fk_substance = OLD.pk
	)
	LIMIT 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_do_not_update_substance_if_taken_by_patient() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_do_not_update_substance_if_taken_by_patient(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_do_not_update_substance_if_taken_by_patient() IS 'If this substance is taken by any patient do not modify description, amount, or unit (case changes allowed).';


--
-- Name: trf_ins_upd_assert_dose_unit_across_drug_components(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_ins_upd_assert_dose_unit_across_drug_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_dose_unit_count integer;
	_msg text;
BEGIN
	SELECT count(1) into strict _dose_unit_count
	FROM (
		SELECT dose_unit
		FROM ref.v_drug_components
		WHERE pk_drug_product = NEW.fk_drug_product
		GROUP BY dose_unit
	) AS dose_unit_count;
	if _dose_unit_count = 1 then
		return NEW;
	end if;
	_msg := '[ref.trf_ins_upd_assert_dose_unit_across_drug_components()]: cannot link substance dose [' || NEW.fk_dose || '] '
		|| 'to drug product [' || NEW.fk_drug_product || '] '
		|| 'because all components must have the same <dose_unit>';
	raise exception check_violation using message = _msg;
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_ins_upd_assert_dose_unit_across_drug_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_ins_upd_assert_dose_unit_across_drug_components(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_ins_upd_assert_dose_unit_across_drug_components() IS 'Assert that all substance doses linked into a multi-component drug carry the same <dose_unit>';


--
-- Name: trf_protect_template_data(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_protect_template_data() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
	if NEW.data != OLD.data then
		-- look for references in clin.form_instances
		-- if there are any we fail this update no matter what
		perform 1 from clin.form_instances where fk_form_def = NEW.pk;
		if FOUND then
			raise exception 'Updating ref.paperwork_templates.data not allowed because it is referenced from existing forms.';
		end if;
	end if;
	-- otherwise let it happen
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_protect_template_data() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_protect_template_data(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_protect_template_data() IS 'Do not allow updates to the template data if
	 any forms already use this template.';


--
-- Name: trf_true_brands_must_have_components(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_true_brands_must_have_components() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_brand_is_deleted boolean;
	_is_fake_brand boolean;
	_has_other_components boolean;
BEGIN
	-- if an UPDATE does NOT move the component to another drug
	-- there WILL be at least one component left
	if TG_OP = 'UPDATE' then
		if NEW.fk_brand = OLD.fk_brand then
			return NEW;
		end if;
	end if;
	-- fake drugs may become devoid of components
	select
		is_fake into _is_fake_brand
	from
		ref.branded_drug
	where
		pk = OLD.fk_brand
	;
	if _is_fake_brand is TRUE then
		return OLD;
	end if;
	-- DELETEs may proceed if the drug has been deleted, too
	if TG_OP = 'DELETE' then
		select not exists (
			select 1 from ref.branded_drug
			where pk = OLD.fk_brand
		) into _brand_is_deleted;
		if _brand_is_deleted is TRUE then
			return OLD;
		end if;
	end if;
	-- if there are other components left after the
	-- UPDATE or DELETE everything is fine
	select exists (
		select 1 from ref.lnk_substance2brand
		where
			fk_brand = OLD.fk_brand
				and
			fk_substance != OLD.fk_substance
		limit 1
	) into _has_other_components;
	if _has_other_components is TRUE then
		return OLD;
	end if;
	raise exception '[ref.trf_true_brands_must_have_components::%] brand must have components (brand <%> component <%>)', TG_OP, OLD.fk_brand, OLD.fk_substance;
	return OLD;
END;$$;


ALTER FUNCTION ref.trf_true_brands_must_have_components() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_true_brands_must_have_components(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_true_brands_must_have_components() IS 'There must always be at least one component for any existing non-fake branded drug.';


--
-- Name: trf_upd_assert_dose_unit_in_multi_component_drugs(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_upd_assert_dose_unit_in_multi_component_drugs() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_pk_drug_product integer;
	_component_count integer;
	_dose_unit_count integer;
	_msg text;
BEGIN
	SELECT fk_drug_product into strict _pk_drug_product
	FROM ref.lnk_dose2drug
	WHERE fk_dose = NEW.pk;
	if FOUND IS FALSE then
		return NEW;
	end if;
	SELECT count(1) into strict _component_count
	FROM ref.lnk_dose2drug
	WHERE fk_drug_product = _pk_drug_product;
	if _component_count = 1 then
		return NEW;
	end if;
	SELECT count(1) into strict _dose_unit_count
	FROM (
		SELECT dose_unit
		FROM ref.v_drug_components
		WHERE pk_drug_product = _pk_drug_product
		GROUP BY dose_unit
	) AS dose_unit_count;
	if _dose_unit_count = 1 then
		return NEW;
	end if;
	_msg := '[ref.trf_upd_assert_dose_unit_in_multi_component_drugs()]: cannot change <dose_unit> on dose [' || NEW.pk || '] '
		|| 'from [' || coalesce(OLD.dose_unit, '<NULL>') || '] '
		|| 'to [' || coalesce(NEW.dose_unit, '<NULL>') || '] '
		|| 'because all doses linked to drug product [' || _pk_drug_product || '] '
		|| 'must have the same <dose_unit>';
	raise exception check_violation using message = _msg;
	return NEW;
END;$$;


ALTER FUNCTION ref.trf_upd_assert_dose_unit_in_multi_component_drugs() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_assert_dose_unit_in_multi_component_drugs(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_upd_assert_dose_unit_in_multi_component_drugs() IS 'Assert that after updates to ref.dose.dose_unit all substance doses linked into a multi-component drug still carry the same <dose_unit>.';


--
-- Name: trf_upd_ref_code_tbl_check_backlink(); Type: FUNCTION; Schema: ref; Owner: gm-dbo
--

CREATE FUNCTION ref.trf_upd_ref_code_tbl_check_backlink() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
	_msg text;
BEGIN
	if NEW.pk_coding_system = OLD.pk_coding_system then
		return NEW;
	end if;
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return NEW;
	end if;
	_msg := 'ref.trf_upd_ref_code_tbl_check_backlink(): UPDATE of '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;$$;


ALTER FUNCTION ref.trf_upd_ref_code_tbl_check_backlink() OWNER TO "gm-dbo";

--
-- Name: FUNCTION trf_upd_ref_code_tbl_check_backlink(); Type: COMMENT; Schema: ref; Owner: gm-dbo
--

COMMENT ON FUNCTION ref.trf_upd_ref_code_tbl_check_backlink() IS 'When updating any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.';


--
-- Name: clin_root_item; Type: TABLE; Schema: clin; Owner: gm-dbo
--

CREATE TABLE clin.clin_root_item (
    pk_item integer NOT NULL,
    clin_when timestamp with time zone DEFAULT now() NOT NULL,
    fk_encounter integer NOT NULL,
    fk_episode integer NOT NULL,
    narrative text,
    soap_cat text,
    CONSTRAINT clin_root_item_sane_soap_cat CHECK (((soap_cat IS NULL) OR (soap_cat = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text]))))
)
INHERITS (audit.audit_fields);


ALTER TABLE clin.clin_root_item OWNER TO "gm-dbo";

--
-- Name: TABLE clin_root_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON TABLE clin.clin_root_item IS 'ancestor table for clinical items of any kind, basic
	 unit of clinical information, do *not* store data in
	 here directly, use child tables,
	 contains all the clinical narrative aggregated for full
	 text search, ancestor for all tables that want to store
	 clinical free text';


--
-- Name: COLUMN clin_root_item.pk_item; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.pk_item IS 'the primary key, not named "id" or "pk" as usual since child
	 tables will have "id"/"pk"-named primary keys already and
	 we would get duplicate columns while inheriting from this
	 table';


--
-- Name: COLUMN clin_root_item.clin_when; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.clin_when IS 'when this clinical item became known, can be different from
	 when it was entered into the system (= audit.audit_fields.modified_when)';


--
-- Name: COLUMN clin_root_item.fk_encounter; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.fk_encounter IS 'the encounter this item belongs to';


--
-- Name: COLUMN clin_root_item.fk_episode; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.fk_episode IS 'the episode this item belongs to';


--
-- Name: COLUMN clin_root_item.narrative; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.narrative IS 'each clinical item by default inherits a free text field for clinical narrative';


--
-- Name: COLUMN clin_root_item.soap_cat; Type: COMMENT; Schema: clin; Owner: gm-dbo
--

COMMENT ON COLUMN clin.clin_root_item.soap_cat IS 'each clinical item must be either one of the S, O, A, P, U
	 categories or NULL to indicate a non-clinical item, U meaning Unspecified-but-clinical';


--
-- Name: referral; Type: TABLE; Schema: au; Owner: gm-dbo
--

CREATE TABLE au.referral (
    id integer NOT NULL,
    fk_referee integer NOT NULL,
    fk_form integer NOT NULL
)
INHERITS (clin.clin_root_item);


ALTER TABLE au.referral OWNER TO "gm-dbo";

--
-- Name: TABLE referral; Type: COMMENT; Schema: au; Owner: gm-dbo
--

COMMENT ON TABLE au.referral IS 'table for referrals to defined individuals';


--
-- Name: COLUMN referral.narrative; Type: COMMENT; Schema: au; Owner: gm-dbo
--

COMMENT ON COLUMN au.referral.narrative IS 'inherited from clin.clin_root_item;
	 stores text of referral letter';


--
-- Name: COLUMN referral.fk_referee; Type: COMMENT; Schema: au; Owner: gm-dbo
--

COMMENT ON COLUMN au.referral.fk_referee IS 'person to whom the referral is directed';


--
-- Name: COLUMN referral.fk_form; Type: COMMENT; Schema: au; Owner: gm-dbo
--

COMMENT ON COLUMN au.referral.fk_form IS 'foreign key to the form instance of
this referral.';


--
-- Name: referral_id_seq; Type: SEQUENCE; Schema: au; Owner: gm-dbo
--

CREATE SEQUENCE au.referral_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE au.referral_id_seq OWNER TO "gm-dbo";

--
-- Name: referral_id_seq; Type: SEQUENCE OWNED BY; Schema: au; Owner: gm-dbo
--

ALTER SEQUENCE au.referral_id_seq OWNED BY au.referral.id;


--
-- Name: audit_fields_pk_audit_seq; Type: SEQUENCE; Schema: audit; Owner: gm-dbo
--

CREATE SEQUENCE audit.audit_fields_pk_audit_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE audit.audit_fields_pk_audit_seq OWNER TO "gm-dbo";

--
-- Name: audit_fields_pk_audit_seq; Type: SEQUENCE OWNED BY; Schema: audit; Owner: gm-dbo
--

ALTER SEQUENCE audit.audit_fields_pk_audit_seq OWNED BY audit.audit_fields.pk_audit;


--
-- Name: audit_trail; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.audit_trail (
    pk_audit integer NOT NULL,
    orig_version integer DEFAULT 0 NOT NULL,
    orig_when timestamp with time zone NOT NULL,
    orig_by name NOT NULL,
    orig_tableoid oid NOT NULL,
    audit_action text NOT NULL,
    audit_when timestamp with time zone DEFAULT now() NOT NULL,
    audit_by name DEFAULT "current_user"() NOT NULL,
    CONSTRAINT audit_audit_trail_orig_before_audit_when CHECK (((orig_when <= audit_when) IS TRUE)),
    CONSTRAINT audit_audit_trail_sane_audit_when CHECK (((audit_when <= clock_timestamp()) IS TRUE)),
    CONSTRAINT audit_audit_trail_sane_orig_when CHECK (((orig_when <= clock_timestamp()) IS TRUE)),
    CONSTRAINT audit_trail_audit_action_check CHECK ((audit_action = ANY (ARRAY['UPDATE'::text, 'DELETE'::text])))
);


ALTER TABLE audit.audit_trail OWNER TO "gm-dbo";

--
-- Name: TABLE audit_trail; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON TABLE audit.audit_trail IS 'Each table that needs standard auditing must have a log table inheriting
	 from this table. Log tables have the same name with a prepended "log_".
	 However, log_* tables shall not have constraints.';


--
-- Name: COLUMN audit_trail.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.orig_version IS 'the version of this row in the original table previous to the modification';


--
-- Name: COLUMN audit_trail.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.orig_when IS 'previous modification date in the original table';


--
-- Name: COLUMN audit_trail.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.orig_by IS 'who committed the row to the original table';


--
-- Name: COLUMN audit_trail.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.orig_tableoid IS 'the table oid of the original table, use this to identify the source table';


--
-- Name: COLUMN audit_trail.audit_action; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.audit_action IS 'either "update" or "delete"';


--
-- Name: COLUMN audit_trail.audit_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.audit_when IS 'when committed to this table for auditing';


--
-- Name: COLUMN audit_trail.audit_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.audit_trail.audit_by IS 'committed to this table for auditing by whom';


--
-- Name: audit_trail_pk_audit_seq; Type: SEQUENCE; Schema: audit; Owner: gm-dbo
--

CREATE SEQUENCE audit.audit_trail_pk_audit_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE audit.audit_trail_pk_audit_seq OWNER TO "gm-dbo";

--
-- Name: audit_trail_pk_audit_seq; Type: SEQUENCE OWNED BY; Schema: audit; Owner: gm-dbo
--

ALTER SEQUENCE audit.audit_trail_pk_audit_seq OWNED BY audit.audit_trail.pk_audit;


--
-- Name: audited_tables; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.audited_tables (
    id integer NOT NULL,
    schema name DEFAULT 'public'::name NOT NULL,
    table_name name NOT NULL
);


ALTER TABLE audit.audited_tables OWNER TO "gm-dbo";

--
-- Name: TABLE audited_tables; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON TABLE audit.audited_tables IS 'All tables that need standard auditing must be
	 recorded in this table. Audit triggers will be
	 generated automatically for all tables recorded
	 here.';


--
-- Name: audited_tables_id_seq; Type: SEQUENCE; Schema: audit; Owner: gm-dbo
--

CREATE SEQUENCE audit.audited_tables_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE audit.audited_tables_id_seq OWNER TO "gm-dbo";

--
-- Name: audited_tables_id_seq; Type: SEQUENCE OWNED BY; Schema: audit; Owner: gm-dbo
--

ALTER SEQUENCE audit.audited_tables_id_seq OWNED BY audit.audited_tables.id;


--
-- Name: log_access_log; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_access_log (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    user_action text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_access_log OWNER TO "gm-dbo";

--
-- Name: COLUMN log_access_log.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_access_log.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_access_log.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_access_log.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_access_log.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_access_log.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_access_log.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_access_log.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_address; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_address (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    id integer,
    id_street integer,
    aux_street text,
    number text,
    subunit text,
    addendum text,
    lat_lon point
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_address OWNER TO "gm-dbo";

--
-- Name: COLUMN log_address.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_address.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_address.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_address.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_address.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_address.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_address.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_address.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_allergy; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_allergy (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk_item integer,
    clin_when timestamp with time zone,
    fk_encounter integer,
    fk_episode integer,
    narrative text,
    soap_cat text,
    pk integer,
    substance text,
    substance_code text,
    generics text,
    allergene text,
    atc_code text,
    fk_type integer,
    generic_specific boolean,
    definite boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_allergy OWNER TO "gm-dbo";

--
-- Name: COLUMN log_allergy.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_allergy.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_allergy.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_allergy.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_allergy_state; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_allergy_state (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    has_allergy integer,
    comment text,
    last_confirmed timestamp with time zone,
    fk_encounter integer
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_allergy_state OWNER TO "gm-dbo";

--
-- Name: COLUMN log_allergy_state.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy_state.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_allergy_state.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy_state.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_allergy_state.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy_state.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_allergy_state.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_allergy_state.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_auto_hint; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_auto_hint (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    query text,
    title text,
    hint text,
    url text,
    is_active boolean,
    source text,
    lang text,
    recommendation_query text,
    popup_type integer,
    highlight_as_priority boolean
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_auto_hint OWNER TO "gm-dbo";

--
-- Name: COLUMN log_auto_hint.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_auto_hint.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_auto_hint.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_auto_hint.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
-- Name: COLUMN log_auto_hint.orig_by; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_auto_hint.orig_by IS 'the .modified_by in the original row before the audited action took place, should be equal to .modified_by';


--
-- Name: COLUMN log_auto_hint.orig_tableoid; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_auto_hint.orig_tableoid IS 'the TG_RELID when the audit trigger was run';


--
-- Name: log_beh_fall_typ; Type: TABLE; Schema: audit; Owner: gm-dbo
--

CREATE TABLE audit.log_beh_fall_typ (
    pk_audit integer DEFAULT nextval('audit.audit_trail_pk_audit_seq'::regclass),
    row_version integer,
    modified_when timestamp with time zone,
    modified_by name,
    pk integer,
    code text,
    kurzform text,
    name text
)
INHERITS (audit.audit_trail);


ALTER TABLE audit.log_beh_fall_typ OWNER TO "gm-dbo";

--
-- Name: COLUMN log_beh_fall_typ.orig_version; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_beh_fall_typ.orig_version IS 'the .row_version in the original row before the audited action took place, should be equal to .row_version';


--
-- Name: COLUMN log_beh_fall_typ.orig_when; Type: COMMENT; Schema: audit; Owner: gm-dbo
--

COMMENT ON COLUMN audit.log_beh_fall_typ.orig_when IS 'the .modified_when in the original row before the audited action took place, should be equal to .modified_when';


--
