Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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'
      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)```
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.
                See updated answer. It is explained here plpgsql trigger functions in section starting, 'Row-level triggers fired BEFORE ...'
– Adrian Klaver
                Apr 7, 2022 at 19:27
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.