PostgreSQL offers a non-standard SQL command called COMMENT, which allows for
documentation of any database object. By using COMMENT on a table, function, operator, or
other database object, you can provide description that is stored in the pg_description system
table. Descriptions can be easily retrieved through a set of extended
psql
slash commands.
Most standard objects in the database have a default description, which can be perused (along with any user-added
descriptions) with the \dd slash command within
psql
.
Here is the syntax for COMMENT:
COMMENT ON [ [ DATABASE | INDEX | RULE | SEQUENCE | TABLE | TYPE | VIEW ]
{
object_name
|
COLUMN
table_name.column_name
|
AGGREGATE
aggregate_name aggregate_type
|
FUNCTION
function_name
(
argument_type
[, ...] ) |
OPERATOR
operator_name
(
leftoperand_type
,
rightoperand_type
) |
TRIGGER
trigger_name
ON
table_name
}
] IS '
description
'
In this syntax,
object_name
is the name of the database object that you wish to add a comment of
description
to. The keywords for the major database objects are optional, but if you intend to place a
comment on a column, function, aggregate function, operator, or trigger, you must specify the preceding keyword so that
PostgreSQL knows what kind of syntax to expect, and where to look for the object name specified.
Note: Note that any comment added to a database is tied to both the database
in which it is added, and the
user
who added it. You can only see those comments that you create.
The
description
string constant following the IS keyword is the
literal comment to be placed in the database. Example 9-18 demonstrates placing a simple
description on the id column of the booktown database.
Example 9-18. Commenting the books table
booktown=#
COMMENT ON COLUMN books.id
booktown-#
IS 'An Internal Book Town Identifier';
COMMENT
The COMMENT server message returned in Example 9-18
indicates that the comment was successfully placed on the column.
You may retrieve comments from the database easily by using the
psql
slash-plus commands.
These are as follows:
-
\d+
-
Displays the same information as the standard \d command (displaying all tables,
views, sequences, and indices in the currently connected database), but adds a column for the comments as well.
-
\l+
-
Displays comments on all databases.
-
\df+ [
pattern
]
-
Displays descriptions for each function in the currently connected database (as well as the language and source
of the function). You may wish to view this slash command in expanded mode for readability by first initiating
the \x slash command (see Chapter 6 for more about this).
You can optionally supply a regular expression
pattern
to compare against existing function
names, allowing you to limit the number of functions displayed.
-
\dt+
-
Displays comments on all tables in the currently connected database.
-
\di+
-
Displays comments on all indices in the currently connected database.
-
\ds+
-
Displays comments on all sequences in the currently connected database.
-
\dv+
-
Displays comments on all views in the currently connected database.
-
\dS+
-
Displays comments on system tables. Note that comments placed on system tables are still tied to a particular database,
even though they are accessible from all databases, and will therefore not be visible when performing a
\dS+ slash command from another database connection.
-
\dd
-
Displays all descriptions for all database objects.
Example 9-19 demonstrates the retrieval of the comment that was placed on the
books table in Example 9-18 with the use of the
psql
\d+ command.
Example 9-19. Retrieving a comment
booktown=#
\d+ books
Table "books"
Attribute | Type | Modifier | Description
------------+---------+----------+----------------------------------
id | integer | not null | An Internal Book Town Identifier
title | text | not null |
author_id | integer | |
subject_id | integer | |
Index: books_id_pkey
The COMMENT SQL command provides a very simple way of internally
documenting your objects, from tables to functions. This can be of great help when working with large or complicated
database schema. Even the best of naming conventions do not always result in database objects whose applications are self-evident;
this is especially the case when working with multiple developers.
