Thread: item descriptions in psql

Hi,

I just found a reference to descriptions to functions/tables/...etc.
and am now wondering how to add them myself?

Joost Roeleveld

ps. as an example of what I'm referring to:

mydb=> \dd currval
description
----------------------
sequence current value
(1 row)

mydb=> \dd mytable    --- I want to enter a description for this......
description
--------------
no description
(1 row)



************

"J. Roeleveld" wrote:
>
> Hi,
>
> I just found a reference to descriptions to functions/tables/...etc.
> and am now wondering how to add them myself?
>
> Joost Roeleveld
>
> ps. as an example of what I'm referring to:
>
> mydb=> \dd currval
> description
> ----------------------
> sequence current value
> (1 row)
>
> mydb=> \dd mytable    --- I want to enter a description for this......
> description
> --------------
> no description
> (1 row)
>

In the next release, PostgreSQL will have the equivalent of
Oracle's COMMENT ON statement to allow for user comments of
various database schema objects. Until then, you have to
manually insert rows into pg_description with the oid of the
target object and the relevant comments:

emptoris=> select oid from pg_class where relname =
'webusers';
   oid
------
155868
(1 row)

emptoris=> insert into pg_description values (155868,
'Webuser Information');
INSERT 182688 1

emptoris=> \dd webusers;
description
-------------------
Webuser Information
(1 row)

Hope that helps,

Mike Mascari

> Hi,
>
> I just found a reference to descriptions to functions/tables/...etc.
> and am now wondering how to add them myself?
>
> Joost Roeleveld

not sure if there is a shortcut to this (it's short enough already):

INSERT INTO pg_description (objoid, description)
  SELECT oid, 'your description' FROM pg_class WHERE relname = 'your_table_name';

INSERT INTO pg_description (objoid, description)
  SELECT oid, 'your description' FROM pg_proc WHERE proname = 'your_procedure_name';

INSERT INTO pg_description (objoid, description)
  SELECT oid, 'your description' FROM pg_type WHERE typname = 'your_type_name';

INSERT INTO pg_description (objoid, description)
  SELECT oid, 'your description' FROM pg_operator WHERE oprname = 'your_operator_name';

(in case of operators, oprname is '=', '<=', '>>~', etc.)

in older versions (pre-6.3), one had to typecast the names and descriptions:

INSERT INTO pg_description (objoid, description)
  SELECT oid, 'your description'::text FROM pg_type WHERE typname = 'your_type_name'::name;

--Gene

"Gene Selkov, Jr." wrote:
>
> > Hi,
> >
> > I just found a reference to descriptions to functions/tables/...etc.
> > and am now wondering how to add them myself?
> >
> > Joost Roeleveld
>
> not sure if there is a shortcut to this (it's short enough already):
>
> INSERT INTO pg_description (objoid, description)
>   SELECT oid, 'your description' FROM pg_class WHERE relname = 'your_table_name';
>
> INSERT INTO pg_description (objoid, description)
>   SELECT oid, 'your description' FROM pg_proc WHERE proname = 'your_procedure_name';
>
> INSERT INTO pg_description (objoid, description)
>   SELECT oid, 'your description' FROM pg_type WHERE typname = 'your_type_name';
>
> INSERT INTO pg_description (objoid, description)
>   SELECT oid, 'your description' FROM pg_operator WHERE oprname = 'your_operator_name';
>
> (in case of operators, oprname is '=', '<=', '>>~', etc.)
>
> in older versions (pre-6.3), one had to typecast the names and descriptions:
>
> INSERT INTO pg_description (objoid, description)
>   SELECT oid, 'your description'::text FROM pg_type WHERE typname = 'your_type_name'::name;
>
> --Gene

And also note that pg_dump does not yet dump descriptions.
So, until the next release, if your going to document your
database schema, be sure to dump pg_description before
performing any dump..blow-away..reload sequence.

Mike Mascari