Contents

pg_rules

A system view listing query rewrite rules

pg_rules is a system catalogue view listing query rewrite rules. pg_rules extends the underlying system catalogue table pg_rewrite to provide a human-readable representation of rewrite rules.

pg_rules was added in PostgreSQL 6.4.

Definition by PostgreSQL version

pg_rules (PostgreSQL 15)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 14)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 13)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 12)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 11)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 10)

             View "pg_catalog.pg_rules"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 rulename   | name |           |          | 
 definition | text |           |          |

Documentation: pg_rules

pg_rules (PostgreSQL 9.6)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.5)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.4)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.3)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.2)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.1)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 9.0)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text |

Documentation: pg_rules

pg_rules (PostgreSQL 8.4)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition
   FROM pg_rewrite r
   JOIN pg_class c ON c.oid = r.ev_class
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE r.rulename <> '_RETURN'::name;

Documentation: pg_rules

pg_rules (PostgreSQL 8.3)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition
   FROM pg_rewrite r
   JOIN pg_class c ON c.oid = r.ev_class
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE r.rulename <> '_RETURN'::name;

Documentation: pg_rules

pg_rules (PostgreSQL 8.2)

  View "pg_catalog.pg_rules"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 rulename   | name | 
 definition | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition
   FROM pg_rewrite r
   JOIN pg_class c ON c.oid = r.ev_class
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE r.rulename <> '_RETURN'::name;

Documentation: pg_rules

Change history

Examples

After creating the following rule:

postgres=# CREATE RULE foobar AS
             ON INSERT TO foo
               WHERE NEW.id % 2 = 1
             DO INSTEAD NOTHING;
CREATE RULE

the following record is present in pg_rules:

postgres=# SELECT * FROM pg_rules WHERE rulename='foobar'\gx
-[ RECORD 1 ]-----------------------------------------------
schemaname | public
tablename  | foo
rulename   | foobar
definition | CREATE RULE foobar AS                          +
           |     ON INSERT TO public.foo                    +
           |    WHERE ((new.id % 2) = 1) DO INSTEAD NOTHING;

By default, two rules are always present for the pg_settings view:

postgres=# SELECT * FROM pg_rules\gx
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------
schemaname | pg_catalog
tablename  | pg_settings
rulename   | pg_settings_u
definition | CREATE RULE pg_settings_u AS                                                                     +
           |     ON UPDATE TO pg_catalog.pg_settings                                                          +
           |    WHERE (new.name = old.name) DO  SELECT set_config(old.name, new.setting, false) AS set_config;
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------
schemaname | pg_catalog
tablename  | pg_settings
rulename   | pg_settings_n
definition | CREATE RULE pg_settings_n AS                                                                     +
           |     ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;

Categories

DDL, Rules, System catalogue

See also

CREATE RULE, ALTER RULE, DROP RULE, pg_rewrite, pg_get_ruledef()