I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions.
something like:
CREATE TABLE payment (
amount bigint,
CONSTRAINT amount_gt_0 CHECK (round_amount(amount) > 0)
);
where round_amount is user-defined function.
Is it possible to get a list of all constraint or at least tables that use such constraints?
postgresql version is 9.6
It looks like that there is already a view called information_schema.check_constraint_routine_usage that provide this information.
The view originally only shows routines owned by the current role but you can find the original query by typing \d+ information_schema.check_constraint_routine_usage and tweak it.
Do you mean this?
SELECT
cls.relname,
const.conname,
const.consrc
FROM
pg_catalog.pg_constraint const
INNER JOIN pg_catalog.pg_class cls ON cls.oid = const.conrelid
INNER JOIN pg_catalog.pg_namespace ns ON ns.oid = const.connamespace
WHERE
ns.nspname = '<your_schema>'
AND const.contype = 'c'
AND const.consrc like '%round_amount%'
round_amount but in my case I don't. I'm trying to find every constraint that relies on user-defined function.