sketch_of_concept_trigger.sql

CREATE TABLE patient (
    name text,
    administrative_gender uuid references concept.concept_cid,
    ethnicity uuid references concept.concept_cid,
    phenotypic_sex_cid uuid references concept.concept_cid,

);

-- Parametric trigger for validating concept FK against sets of codesystems
-- concept_ind_codesystem(concept_field_name, array_of_codesystems)
CREATE FUNCTION concept_in_codesystem()
RETURNS trigger AS $concept_in_codesystem$
DECLARE
  concept_field varchar := TG_ARGV[0];
  new_concept_cid uuid := to_json(NEW) ->> concept_field;
  valid_codesystem_uris varchar[] := TG_ARGV[1];
BEGIN
    SELECT * FROM concept
    WHERE concept.concept_uid = new_concept_cid
      AND codesystem_uri = ANY(valid_codesystem_uris)
    IF FOUND
      THEN RETURN NEW
    ELSE
      RAISE EXCEPTION 'concept_in_codesystem: The value NEW.% == % is not a valid concept given restricted codesystems',
       concept_field, new_concept_cid;

END;
$concept_in_codesystem$
LANGUAGE plpgsql
PARALLEL SAFE;

CREATE TRIGGER patient_ethnicity_trg BEFORE INSERT OR UPDATE ON patient
    FOR EACH ROW EXECUTE PROCEDURE concept_in_codesystem('ethnicity', ['ethnicity']);
CREATE TRIGGER patient_gender_trg BEFORE INSERT OR UPDATE ON patient
    FOR EACH ROW EXECUTE PROCEDURE concept_in_codesystem('administrative_gender', ['gender']);