Thread: psql feature request (\dd+)

psql feature request (\dd+)

From
Richard Broersma
Date:
It'd be nice if there was a \dd+ command to return all of the comments
of dependent objects in addition to the specified object;  i.e. all
dependent objects related to a table for example.

Notice the difference between difference between a well documented DDL
script versus the results returned by psql's \dd.  The command \d+
shows a bit more but not everything.

/* ISATags is a set of tag numbers that are used to identify
   process instrumentation. */

CREATE TABLE ISATags (
   PRIMARY KEY( area_code, process_code, loop_nbr, tag_seq ),

/* area_code identifies the area or process unit that
   a tagged instrument supports.  Valid area_codes can
   be numbered any value between 0 and 9999.
*/
      area_code INTEGER DEFAULT 0 NOT NULL
                CONSTRAINT valid_area_codes
                CHECK( area_code BETWEEN 0 and 9999 ),


/* process_code is a coded defined by the ISA in
   the normative reference XXXX-XXX.  This code
   defines the type by procces and the function
   of the instrument defined by the ISAtag. A
   process code is a 2 to 4 character sequence.
*/
   process_code VARCHAR( 6 ) NOT NULL
                CONSTRAINT valid_process_codes
                CHECK( process_code ~ E'^[A-Z]{2,4}$' ),
/* all tags in an area with the same loop_nbr are
   related and work together in support of a proccess
   function.  Loop numbers must be greater than 0,
   however and arbitary upper limit of 9999 is
   applied since some legacy control system cannot
   support loop number greater than this value.
*/
       loop_nbr INTEGER DEFAULT 0 NOT NULL
                CONSTRAINT valid_loop_nbrs
                CHECK( loop_nbr BETWEEN 0 AND 9999 ),

/* tag_seq is a sequence letter that distinguses
   instruments of identical function that work together
   in support of a proccess function. This field is
   optional but must be an alphbetic character when
   used.
*/
        tag_seq VARCHAR( 2 ) DEFAULT '' NOT NULL
                CONSTRAINT valid_tag_seqs
                CHECK( tag_seq ~ E'^(?:[A-Z])?$' )
);

     COMMENT ON TABLE ISATags
                   IS 'ISATags is a set of tag numbers that are used
to identify process instrumentation.';

    COMMENT ON COLUMN ISATags.area_code
                   IS 'area_code identifies the area or process unit
that a tagged instrument supports.';

COMMENT ON CONSTRAINT valid_area_codes
                   ON ISATags
                   IS 'Valid area_codes can be numbered any value
between 0 and 9999.';

    COMMENT ON COLUMN ISATags.process_code
                   IS 'process_code is a coded defined by the ISA in
the normative reference XXXX-XXX.  This code defines the type by
procces and the function of the instrument defined by the ISAtag.';

COMMENT ON CONSTRAINT valid_process_codes
                   ON ISATags
                   IS 'A process code is a 2 to 4 character sequence.';

    COMMENT ON COLUMN ISATags.loop_nbr
                   IS 'all tags in an area with the same loop_nbr are
related and work together in support of a proccess function.  ';

COMMENT ON CONSTRAINT valid_loop_nbrs
                   ON ISATags
                   IS 'Loop numbers must be greater than 0, however
and arbitary upper limit of 9999 is applied since some legacy control
system cannot support loop number greater than this value.';

    COMMENT ON COLUMN ISATags.tag_seq
                   IS 'tag_seq is a sequence letter that distinguses
instruments of identical function that work together in support of a
proccess function.';

COMMENT ON CONSTRAINT valid_tag_seqs
                   ON ISATags
                   IS 'This field is optional but must be an alphbetic
character when used.';

\dd ISAtags

                                              Object descriptions
 Schema |  Name   | Object |                                    Description
--------+---------+--------+------------------------------------------------------------------------------------
 public | isatags | table  | ISATags is a set of tag numbers that are
used to identify process instrumentation.
(1 row)

\dd+ ISATags
                                              Object descriptions
 Schema |  Name   | Object |                                    Description
--------+---------+--------+------------------------------------------------------------------------------------
 public | isatags | table  | ISATags is a set of tag numbers that are
used to identify process instrumentation.
(1 row)

\d+ isatags

                                                    Table
"public.isatags"
    Column    |         Type         |               Modifiers
       | Storage  |
                               Descript

--------------+----------------------+----------------------------------------+----------+-------------------------------------------------------------------------------------------
 area_code    | integer              | not null default 0
       | plain    | area_code identifies the area or process unit that
a tagged instrument supports.
 process_code | character varying(6) | not null
       | extended | process_code is a coded defined by the ISA in the
normative reference XXXX-XXX.  This code
 loop_nbr     | integer              | not null default 0
       | plain    | all tags in an area with the same loop_nbr are
related and work together in support of a p
 tag_seq      | character varying(2) | not null default ''::character
varying | extended | tag_seq is a sequence letter that distinguses
instruments of identical function that work
Indexes:
    "isatags_pkey" PRIMARY KEY, btree (area_code, process_code,
loop_nbr, tag_seq)
Check constraints:
    "valid_area_codes" CHECK (area_code >= 0 AND area_code <= 9999)
    "valid_loop_nbrs" CHECK (loop_nbr >= 0 AND loop_nbr <= 9999)
    "valid_process_codes" CHECK (process_code::text ~ '^[A-Z]{2,4}$'::text)
    "valid_tag_seqs" CHECK (tag_seq::text ~ '^(?:[A-Z])?$'::text)
Has OIDs: no

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Re: psql feature request (\dd+)

From
Craig Ringer
Date:
On 14/05/2010 11:35 PM, Richard Broersma wrote:
> It'd be nice if there was a \dd+ command to return all of the comments
> of dependent objects in addition to the specified object;  i.e. all
> dependent objects related to a table for example.

What gets me with Pg's COMMENT ON is the way the comments have to be
separate from, and after, the objects they refer to. IMO it'd be
significantly preferable to have something like:

CREATE TABLE X (
    somepk integer primary key,
    cost numeric(10,2) COMMENT 'blah blah',
);

.. with a similar clause for CONSTRAINT.

Is there any particular objection to doing things this way? Or is it
just that nobody's been inclined to implement it (and fair enough, too) ?

--
Craig Ringer

Re: psql feature request (\dd+)

From
Tom Lane
Date:
Craig Ringer <craig@postnewspapers.com.au> writes:
> What gets me with Pg's COMMENT ON is the way the comments have to be
> separate from, and after, the objects they refer to. IMO it'd be
> significantly preferable to have something like:

> CREATE TABLE X (
>     somepk integer primary key,
>     cost numeric(10,2) COMMENT 'blah blah',
> );

> .. with a similar clause for CONSTRAINT.

> Is there any particular objection to doing things this way?

You're infringing on SQL-standard syntax space if you do that.
Now maybe they'll never define some conflicting extension to
the CREATE TABLE syntax, but it seems to me to be taking a risk
for not a whole lot of gain.

Now, if you could persuade the SQL committee to standardize
syntax like the above, that'd be great.

            regards, tom lane