Postgres list constraints that use a stored procedure

Question

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

Answer 1

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.

Answer 2

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%'