Comment Definition

Comments on SQL objects are effective means to convey design information, such as metric scales and one-liner documentation.
Comments are only allowed on persistent objects. It is not allowed to add comments for temporary objects (tables/views/columns/indices) in schema "tmp".

comment-on-statement:
   COMMENT ON  catalog_object  IS { 'comment-text' | NULL }

catalog_object:
   SCHEMA   schema_name
 | TABLE  [ schema_name '.' ] table_name
 | VIEW   [ schema_name '.' ] view_name
 | COLUMN [ schema_name '.' ] table_or_view_name '.' column_name
 | INDEX  [ schema_name '.' ] index_name
 | SEQUENCE  [ schema_name '.' ] sequence_name
 | FUNCTION  [ schema_name '.' ] function_name [ argument_type_list ]
 | PROCEDURE [ schema_name '.' ] procedure_name [ argument_type_list ]
 | AGGREGATE [ FUNCTION ] [ schema_name '.' ] aggregate_name [ argument_type_list ]
 | FILTER    [ FUNCTION ] [ schema_name '.' ] filter_func_name [ argument_type_list ]
 | LOADER    [ FUNCTION ] [ schema_name '.' ] loader_name [ argument_type_list ]
 | WINDOW    [ FUNCTION ] [ schema_name '.' ] window_name [ argument_type_list ]

argument_type_list
   '(' [ data_type [',' ...] ] ')'

By specifying IS NULL or IS '' you remove the comment for the SQL object.

For functions, procedures, aggregates, filter functions, loader functions and window functions it may be necessary to add the argument(s) type list to uniquely identify an overloaded function. See examples below.

When an SQL object is removed (e.g. via DROP statement), the associated comment(s) are also removed implicitly.

All comments are stored in system table sys.comments.

Note: The information_schema views: information_schema.schemata, information_schema.tables, information_schema.views, information_schema.columns, information_schema.sequences and information_schema.routines have been extended with a column named comments such that you do not have to join table sys.comments yourself for these views.

Examples

COMMENT ON SCHEMA prj4 IS 'schema of new project 4';

COMMENT ON TABLE sys.comments IS 'contains comments on all db objects';

COMMENT ON COLUMN sys.tables."name" IS 'name of the table or view. It must be unique within the schema.';

create sequence sys.article_id_seq;
COMMENT ON SEQUENCE sys.article_id_seq IS 'sequence for article id column';

create table article (id int primary key default next value for sys.article_id_seq, title varchar(500) NOT NULL);
COMMENT ON INDEX sys.article_id_pkey IS 'index on primary key column: id of table: article';

COMMENT ON FUNCTION sys.upper IS 'converts text into UPPERcase text';

COMMENT ON AGGREGATE sys.stddev_pop(int) IS 'computes the standard deviation of a group of integer numbers';

COMMENT ON FILTER FUNCTION sys."endswith"(clob, clob) IS 'filter to match strings ending with some string value';
COMMENT ON FILTER FUNCTION sys."endswith"(clob, clob, boolean)
  IS 'filter to match strings ending with some string value with match case sensitivity control';

COMMENT ON WINDOW FUNCTION sys.row_number IS 'returns the row number of the row within the window group, starting from 1';

-- removing comment for prj0 and prjX
COMMENT ON SCHEMA prj0 IS '';
COMMENT ON SCHEMA prjX IS NULL;


-- querying comments
SELECT id, remark FROM sys.comments
 WHERE id IN (SELECT id FROM sys.ids WHERE table_name = 'mytable');

SELECT schema_name, comments
  FROM information_schema.schemata
 WHERE comments IS NOT NULL;

SELECT table_schema, table_name, table_type, comments
  FROM information_schema.tables
 WHERE comments IS NOT NULL;

SELECT table_schema, table_name, comments
  FROM information_schema.views
 WHERE comments IS NOT NULL;

SELECT table_schema, table_name, column_name, comments
  FROM information_schema.columns
 WHERE comments IS NOT NULL;

SELECT sequence_schema, sequence_name, current_value, comments
  FROM information_schema.sequences
 WHERE comments IS NOT NULL;

SELECT routine_type, routine_schema, routine_name, data_type as return_data_type, comments
  FROM information_schema.routines
 WHERE comments IS NOT NULL;

SELECT ft.function_type_keyword as function_type, s.name as schema, f.name, c.remark as comments
  FROM sys.functions f
  INNER JOIN sys.function_types ft ON ft.function_type_id = f.type
  INNER JOIN sys.schemas s ON s.id = f.schema_id
  LEFT OUTER JOIN sys.comments c ON f.id = c.id
 WHERE c.remark IS NOT NULL;