Postgres trigger function for check constraint with if statement-CodePudding

I have a table in Postgres that looks like the one below. I am trying to add a constraint that will not allow for sires to be added if they have "sex" as "f" and dams if they have "sex" as "m". I read up on trigger functions and function creation in general but still can't figure out how exactly to write a function that would work for this constraint. My latest attempt is below, but it doesn't work.

CREATE FUNCTION check_sex() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF dam NOT IN (SELECT sheep_id
       FROM sheep
       WHERE sex='f'
      )
   THEN
      RAISE EXCEPTION 'only females can be dams';
   END IF;
 
   RETURN dam;
END;$$;

CREATE TRIGGER sex_trigger_f BEFORE INSERT OR UPDATE ON sheep FOR EACH ROW EXECUTE FUNCTION check_sex();

    Column     |         Type          | Collation | Nullable |                 Default
--------------- ----------------------- ----------- ---------- -----------------------------------------
 sheep_id      | integer               |           | not null | nextval('sheep_sheep_id_seq'::regclass)
 tag_id        | character varying(10) |           | not null |
 sex           | character varying(1)  |           | not null |
 dob           | date                  |           | not null |
 purchase_date | date                  |           |          |
 breed_id      | bigint                |           | not null |
 sire          | bigint                |           |          |
 dam           | bigint                |           |          |
Indexes:
    "sheep_pkey" PRIMARY KEY, btree (sheep_id)
    "sheep_tag_id_key" UNIQUE CONSTRAINT, btree (tag_id)
Check constraints:
    "sex_vals" CHECK (sex::text = ANY (ARRAY['f'::character varying, 'm'::character varying]::text[]))
Foreign-key constraints:
    "sheep_breed_id_fkey" FOREIGN KEY (breed_id) REFERENCES breeds(id)
    "sheep_self_fk" FOREIGN KEY (sire) REFERENCES sheep(sheep_id)
    "sheep_self_fk_dam" FOREIGN KEY (dam) REFERENCES sheep(sheep_id)
Referenced by:
    TABLE "sheep" CONSTRAINT "sheep_self_fk" FOREIGN KEY (sire) REFERENCES sheep(sheep_id)
    TABLE "sheep" CONSTRAINT "sheep_self_fk_dam" FOREIGN KEY (dam) REFERENCES sheep(sheep_id)```

CodePudding user response：

I would try:

CREATE FUNCTION check_sex() RETURNS trigger
   LANGUAGE plpgsql AS
DECLARE
    sex_type_dam varchar;
    sex_type_sire varchar;
$$
BEGIN
  
    SELECT INTO sex_type_dam sex FROM sheep WHERE sheep_id = NEW.dam;

    IF FOUND THEN
        IF sex_type_dam != 'f' THEN 
            RAISE EXCEPTION 'only females can be dams';
        END IF;
    END IF;

    SELECT INTO sex_type_sire sex FROM sheep WHERE sheep_id = NEW.sire;

    IF FOUND THEN
        IF sex_type_sire != 'm' THEN 
            RAISE EXCEPTION 'only males can be sires';
        END IF;
    END IF;
 
    RETURN NEW;
END;
$$;

FOUND is a special variable from here Result status that will be true if there is a value selected into the sex_type_* variable. This means the case where dam or sire has no value will be ignored. If they do have a value and a sex value is returned then it will tested to see if it is the correct sex. If it is not then an exception will be raised otherwise the entire NEW record will be returned.