Wednesday, November 9, 2011

Postgresql - Documenting your database system

Documentation is very important in every system. Database systems are not an exception. You can attach comments to virtually any database object.

COMMENT



Name


COMMENT -- define or change the comment of an object



Synopsis


COMMENT ON
{
AGGREGATE agg_name (agg_type [, ...] ) |
CAST (source_type AS target_type) |
COLLATION object_name |
COLUMN relation_name.column_name |
CONSTRAINT constraint_name ON table_name |
CONVERSION object_name |
DATABASE object_name |
DOMAIN object_name |
EXTENSION 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 |
OPERATOR operator_name (left_type, right_type) |
OPERATOR CLASS object_name USING index_method |
OPERATOR FAMILY object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
ROLE object_name |
RULE rule_name ON table_name |
SCHEMA object_name |
SEQUENCE object_name |
SERVER 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 |
TRIGGER trigger_name ON table_name |
TYPE object_name |
VIEW object_name
} IS 'text'




Description


COMMENT stores a comment about a database object.

Only one comment string is stored for each object, so to modify a comment, issue a new COMMENT command for the same object. To remove a comment, write NULL in place of the text string. Comments are automatically dropped when their object is dropped.


In order to see the comments attached to a database object, you can get them through psql with the \dd [object] option, for instance:
musicbrainz_db=# \dd artistas_mexicanos
Descripciones de objetos
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------
Esquema     | musicbrainz
Nombre      | artistas_mexicanos
Objeto      | vista
Descripción | Vista con artistas que hacen referencia al país con código 138 - Mexico (el problema es que muchos artistas tienen ese dato en null)

To get the comments of a particular set of database objects, you append a plus sign to the psql option, like this:
musicbrainz_db=# \dv+
Listado de relaciones
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------
Esquema     | musicbrainz
Nombre      | artistas_mexicanos
Tipo        | vista
Dueño       | postgres
Tamaño      | 0 bytes
Descripción | Vista con artistas que hacen referencia al país con código 138 - Mexico (el problema es que muchos artistas tienen ese dato en null)
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------
Esquema     | musicbrainz
Nombre      | canciones_mexicanas
Tipo        | vista
Dueño       | postgres
Tamaño      | 0 bytes
Descripción |

Another reference:

http://stackoverflow.com/questions/5664094/getting-list-of-table-comments-in-postgresql

No comments:

Post a Comment