How to check triggers with Ecto changeset?

Phoenix Forum Questions / Help
1

I am using the following trigger to check that the referenced table has the type that I want.

I can use check_constraint(changeset, field, opts \\ []), but it is not possible to put a constraint with a foreign table,

CREATE OR REPLACE FUNCTION type_check() RETURNS trigger AS $$
DECLARE
	required_type TEXT;
	inserted_type TEXT;
	inserted_id bigint;
BEGIN
	EXECUTE 'SELECT $1.' || tg_argv[0] INTO inserted_id USING NEW;
	required_type = tg_argv[1];
	inserted_type = (SELECT TYPE FROM items WHERE ID = inserted_id);
	IF inserted_type IS NULL OR required_type != inserted_type THEN
	   RAISE EXCEPTION 'Type mismatch: required: % and got %', required_type, inserted_type;
	ELSE
	   RETURN NEW;
	END IF;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER dummy_check BEFORE INSERT OR UPDATE ON dummy FOR EACH ROW EXECUTE FUNCTION type_check(item_id, cat);
2

To the best of my knowledge, there is no special handling for exceptions generated from triggers available in Ecto.Changeset. check_constraint/3 responds to violations of database defined CHECK constraints (PostgreSQL: Documentation: 14: 5.4. Constraints) which are, naturally, able to be created through Ecto migrations (see Ecto docs for more).

I also use triggers for data validation purposes and simply have the expectation that if I raise an exception from the trigger, that a call to something like Repo.insert/2 will result in in {:error, changeset}, but that I have to handle the included Postgrex.Error to know any details beyond that something blew up. I ensure that the RAISE EXCEPTION call in the trigger sets enough of the exception details to increase the granularity of information returned from the database.

1 Like