Find the number of rules in a database, excluding the rules that are associated with system catalog tables and rules that are created to support views. Rules can be used to maintain data integrity in a database by causing rejection of incorrect insertions and updates. Therefore, the number of rules in a database gives some indications about the state of enforcing constraints at the database level.
Notes
Rules are specific to PostgreSQL and thus it is not possible to get information about these from the INFORMATION_SCHEMA views.
Type
Sofware measure (Numeric values (software measures) about the database)
WITH rules AS (SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename
FROM pg_rewrite r
JOIN pg_class c ON c.oid = r.ev_class
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_authid u ON n.nspowner = u.oid
WHERE r.rulename <> '_RETURN' AND
(n.nspname = 'public' OR u.rolname <> 'postgres'))
SELECT Count(*) AS number_of_rules
FROM rules;
Categories
This query is classified under the following categories:
Name
Description
Triggers and rules
Queries of this category provide information about triggers and rules in a database.