Postgres trigger function for check constraint with if statement

Question

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)```

Adrian Klaver · Accepted Answer · 2022-04-07 20:09:41Z

1

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.

edited Apr 7, 2022 at 20:09

answered Apr 7, 2022 at 18:37

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nika Bo Over a year ago

This makes sense. However, what should I return in this function?

Adrian Klaver Over a year ago

See updated answer. It is explained here plpgsql trigger functions in section starting, 'Row-level triggers fired BEFORE ...'

Adrian Klaver Over a year ago

Alright a complete function that combines your solution and mine.

Collectives™ on Stack Overflow

Postgres trigger function for check constraint with if statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related