Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Documenting a Database

Using COMMENT

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.

Retrieving comments

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire