内容

pg_rules

列出查询重写规则的系统视图

pg_rules 是一个列出查询重写规则的系统目录视图。pg_rules 扩展了底层系统目录表 pg_rewrite 以提供重写规则的人类可读表示形式。

pg_rules 添加于 PostgreSQL 6.4。

按 PostgreSQL 版本定义

pg_rules (PostgreSQL 17)

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

文档: pg_rules

pg_rules (PostgreSQL 16)

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

文档: pg_rules

pg_rules (PostgreSQL 15)

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

文档: pg_rules

pg_rules (PostgreSQL 14)

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

文档: pg_rules

pg_rules (PostgreSQL 13)

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

文档: pg_rules

pg_rules (PostgreSQL 12)

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

文档: pg_rules

pg_rules (PostgreSQL 11)

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

文档: pg_rules

pg_rules (PostgreSQL 10)

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

文档: pg_rules

pg_rules (PostgreSQL 9.6)

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

文档: pg_rules

pg_rules (PostgreSQL 9.5)

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

文档: pg_rules

pg_rules (PostgreSQL 9.4)

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

文档: pg_rules

pg_rules (PostgreSQL 9.3)

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

文档: pg_rules

pg_rules (PostgreSQL 9.2)

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

文档: pg_rules

pg_rules (PostgreSQL 9.1)

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

文档: pg_rules

pg_rules (PostgreSQL 9.0)

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

文档: 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;

文档: 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;

文档: 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;

文档: pg_rules

更改历史记录

示例

创建以下规则后

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

以下记录存在于 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;

默认情况下，pg_settings 视图始终存在两条规则

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;

分类

DDL，规则，系统目录

另请参阅

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

反馈

提交任何关于“pg_rules”的评论、建议或更正 此处。