Postgresql 中文操作指南

COMMENT

COMMENT——定义或更改对象的注释

Synopsis

COMMENT ON
{
  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  COLUMN relation_name.column_name |
  CONSTRAINT constraint_name ON table_name |
  CONSTRAINT constraint_name ON DOMAIN domain_name |
  CONVERSION object_name |
  DATABASE object_name |
  DOMAIN object_name |
  EXTENSION object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  INDEX object_name |
  LARGE OBJECT large_object_oid |
  MATERIALIZED VIEW object_name |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  POLICY policy_name ON table_name |
  [ PROCEDURAL ] LANGUAGE object_name |
  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  PUBLICATION object_name |
  ROLE object_name |
  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  RULE rule_name ON table_name |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  STATISTICS object_name |
  SUBSCRIPTION object_name |
  TABLE object_name |
  TABLESPACE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TRANSFORM FOR type_name LANGUAGE lang_name |
  TRIGGER trigger_name ON table_name |
  TYPE object_name |
  VIEW object_name
} IS { string_literal | NULL }

where aggregate_signature is:

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

Description

COMMENT 存储有关数据库对象的注释。

每个对象仅存储一个注释字符串,因此要修改注释,请对同一对象发出新的 COMMENT 命令。要移除注释,请在文本字符串中写 NULL 。当对象被删除时,注释将被自动删除。

在要进行注释的对象上获取一个 SHARE UPDATE EXCLUSIVE 锁。

对于大多数类型的对象,只有对象的拥有者可以设置注释。角色没有所有者,因此 COMMENT ON ROLE 的规则是:必须成为超级用户才能对超级用户角色进行注释,或者具有 CREATEROLE 权限并且被授予目标角色上的 ADMIN OPTION 。同样,访问方法也没有所有者;必须成为超级用户才能对访问方法进行注释。当然,超级用户可以对任何事物进行注释。

可以使用 psql 的 \d 命令族查看注释。其他用于检索注释的用户界面可以构建在 psql 所使用的相同内置函数之上,即 obj_descriptioncol_descriptionshobj_description (见 Table 9.78 )。

Parameters

  • object_namerelation_name.column_nameaggregate_nameconstraint_namefunction_nameoperator_namepolicy_nameprocedure_nameroutine_namerule_nametrigger_name

    • 要注释的对象的名称。驻留在模式中的对象(表、函数等)的名称可以具备模式限定。在对列进行注释时, relation_name 必须引用一张表、视图、复合类型或远程表。

  • table_name__domain_name

    • 在对约束、触发器、规则或策略创建注释时,这些参数会指定该对象定义到的表或域的名称。

  • source_type

    • 转换的源数据类型的名称。

  • target_type

    • 转换的目标数据类型的名称。

  • argmode

    • 函数、过程或聚合参数的模式: INOUTINOUTVARIADIC 。如果省略,则默认为 IN 。请注意, COMMENT 实际上并不会注意 OUT 参数,因为只需要输入参数来确定该函数的标识即可。因此,列出 ININOUTVARIADIC 参数就已足够。

  • argname

    • 函数、过程或聚合参数的名称。请注意, COMMENT 实际上并不会注意参数名称,因为只需要参数数据类型来确定该函数的标识即可。

  • argtype

    • 函数、过程或聚合参数的数据类型。

  • large_object_oid

    • 大对象的 OID。

  • left_type__right_type

    • 运算符参数的数据类型(可选模式限定)。为前缀运算符的缺失参数编写 NONE

  • PROCEDURAL

    • 这是一个干扰词。

  • type_name

    • 转换的数据类型的名称。

  • lang_name

    • 转换的语言名称。

  • string_literal

    • 新的注释内容,以字符串字面量形式编写。

  • NULL

    • 编写 NULL 以删除注释。

Notes

目前没有用于查看注释的安全机制:连接到数据库的任何用户都可以查看该数据库中所有对象的注释。对于诸如数据库、角色和表空间等共享对象,注释会全局存储,因此连接到集群中任何数据库的任何用户都可以查看所有共享对象的注释。因此,请不要在注释中放入安全关键信息。

Examples

附加一个注释到表 mytable

COMMENT ON TABLE mytable IS 'This is my table.';

再次将其移除:

COMMENT ON TABLE mytable IS NULL;

更多一些示例:

COMMENT ON ACCESS METHOD gin IS 'GIN index access method';
COMMENT ON AGGREGATE my_aggregate (double precision) IS 'Computes sample variance';
COMMENT ON CAST (text AS int4) IS 'Allow casts from text to int4';
COMMENT ON COLLATION "fr_CA" IS 'Canadian French';
COMMENT ON COLUMN my_table.my_column IS 'Employee ID number';
COMMENT ON CONVERSION my_conv IS 'Conversion to UTF8';
COMMENT ON CONSTRAINT bar_col_cons ON bar IS 'Constrains column col';
COMMENT ON CONSTRAINT dom_col_constr ON DOMAIN dom IS 'Constrains col of domain';
COMMENT ON DATABASE my_database IS 'Development Database';
COMMENT ON DOMAIN my_domain IS 'Email Address Domain';
COMMENT ON EVENT TRIGGER abort_ddl IS 'Aborts all DDL commands';
COMMENT ON EXTENSION hstore IS 'implements the hstore data type';
COMMENT ON FOREIGN DATA WRAPPER mywrapper IS 'my foreign data wrapper';
COMMENT ON FOREIGN TABLE my_foreign_table IS 'Employee Information in other database';
COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral';
COMMENT ON INDEX my_index IS 'Enforces uniqueness on employee ID';
COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures';
COMMENT ON LARGE OBJECT 346344 IS 'Planning document';
COMMENT ON MATERIALIZED VIEW my_matview IS 'Summary of order history';
COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts';
COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';
COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees';
COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees';
COMMENT ON POLICY my_policy ON mytable IS 'Filter rows by users';
COMMENT ON PROCEDURE my_proc (integer, integer) IS 'Runs a report';
COMMENT ON PUBLICATION alltables IS 'Publishes all operations on all tables';
COMMENT ON ROLE my_role IS 'Administration group for finance tables';
COMMENT ON ROUTINE my_routine (integer, integer) IS 'Runs a routine (which is a function or procedure)';
COMMENT ON RULE my_rule ON my_table IS 'Logs updates of employee records';
COMMENT ON SCHEMA my_schema IS 'Departmental data';
COMMENT ON SEQUENCE my_sequence IS 'Used to generate primary keys';
COMMENT ON SERVER myserver IS 'my foreign server';
COMMENT ON STATISTICS my_statistics IS 'Improves planner row estimations';
COMMENT ON SUBSCRIPTION alltables IS 'Subscription for all operations on all tables';
COMMENT ON TABLE my_schema.my_table IS 'Employee Information';
COMMENT ON TABLESPACE my_tablespace IS 'Tablespace for indexes';
COMMENT ON TEXT SEARCH CONFIGURATION my_config IS 'Special word filtering';
COMMENT ON TEXT SEARCH DICTIONARY swedish IS 'Snowball stemmer for Swedish language';
COMMENT ON TEXT SEARCH PARSER my_parser IS 'Splits text into words';
COMMENT ON TEXT SEARCH TEMPLATE snowball IS 'Snowball stemmer';
COMMENT ON TRANSFORM FOR hstore LANGUAGE plpython3u IS 'Transform between hstore and Python dict';
COMMENT ON TRIGGER my_trigger ON my_table IS 'Used for RI';
COMMENT ON TYPE complex IS 'Complex number data type';
COMMENT ON VIEW my_view IS 'View of departmental costs';

Compatibility

SQL 标准中没有 COMMENT 命令。