Search code examples
postgresqltriggersrules

PostgreSQL: how to see which table RULEs exists

We are using RULEs on tables instead of TRIGGERs for auditing purposes.

Is there a way to see which rules exist (information_schema perhaps)?

Also, can I see the source of the rules? Something similar to pg_proc maybe.

Solution

  • This is stored in pg_rewrite

    You can use a query like this to rules for all tables in the public schema:

    select r.rulename, 
       c.relname as rule_table,
       case r.ev_type  
         when '1' then 'SELECT' 
         when '2' then 'UPDATE' 
         when '3' then 'INSERT' 
         when '4' then 'DELETE' 
       end as rule_event, 
       pg_catalog.pg_get_ruledef(r.oid, true) as definition
from pg_catalog.pg_rewrite r  
  join pg_catalog.pg_class c on r.ev_class = c.oid 
where c.relnamespace = 'public'::regnamespace
  and r.rulename <> '_RETURN';

    The r.rulename <> '_RETURN' excludes rules created for views.