1

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

2 Answers 2

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.

0

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%'
1
  • That query assumes that I already know the function name round_amount but in my case I don't. I'm trying to find every constraint that relies on user-defined function.
    – eyadof
    Dec 4, 2018 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.