Goal 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
License MIT (opens in new tab)
Data Source system catalog only
SQL Query 
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:

Triggers and rules

Queries of this category provide information about triggers and rules in a database.

NameDescription
Triggers and rulesQueries of this category provide information about triggers and rules in a database.