Re: Add psql command to list constraints - Mailing list pgsql-hackers

Hi Justin,

Thanks for your comments and review!

> Maybe it ought to be possible to choose the type of constraints to show.
> Similar to how \dt shows tables and \di shows indexes and \dti shows
> tables+inds, you could run \dcoc for check constraints and \dcof for foreign
> keys.  But I think "\dco" is too long of a prefix...


Yeah, agreed.
I added a filter based on the type of constraints:
   - c for check
   - f for foreign key
   - p for primary key
   - t for trigger
   - u for unique
   - x for exclude c, f, p, u, t, and x.

The following is examples of \dcop, \dcof, and \dcopf.

========================================================================
postgres=# \dcop
                     List of constraints
  Schema |     Name     |        Definition        |  Table
--------+--------------+--------------------------+---------
  public | t03_pk1_pkey | PRIMARY KEY (product_no) | t03_pk1
  public | t04_fk_pkey  | PRIMARY KEY (order_id)   | t04_fk
(2 rows)

postgres=# \dcof
                                         List of constraints
  Schema |          Name          |                       Definition                        | Table
--------+------------------------+---------------------------------------------------------+--------
  public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
(1 row)

postgres=# \dcopf
                                          List of constraints
  Schema |          Name          |                       Definition                        |  Table
--------+------------------------+---------------------------------------------------------+---------
  public | t03_pk1_pkey           | PRIMARY KEY (product_no)                                | t03_pk1
  public | t04_fk_pkey            | PRIMARY KEY (order_id)                                  | t04_fk
  public | t04_fk_product_no_fkey | FOREIGN KEY (product_no) REFERENCES t03_pk1(product_no) | t04_fk
(3 rows)
========================================================================


I too think \dco is a long name. So,  I'd like to get suggestions to be more shortened. :)


  
>> +    initPQExpBuffer(&buf);
>> +    printfPQExpBuffer(&buf,
>> +                      "SELECT \n"
>> +                      "n.nspname AS \"%s\", \n"
>> +                      "cst.conname AS \"%s\", \n"
>> +                      "pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
>> +                      "c.relname AS \"%s\" \n"
>> +                      "FROM pg_constraint cst \n"
>> +                      "JOIN pg_namespace n ON n.oid = cst.connamespace \n"
>> +                      "JOIN pg_class c ON c.oid = cst.conrelid \n",
> 
> You should write "pg_catalog." prefix for the tables (in addition to the
> function).


Oops, You are right. Fixed.

  
> Rather than join to pg_class, you can write conrelid::pg_catalog.regclass,
> since regclass is supported since at least v7.3 (but ::regnamespace was
> introduced in v9.5, so the join against pg_namespace is still necessary).
> https://www.postgresql.org/docs/9.5/datatype-oid.html
>> +    myopt.title = _("List of constsraints");
> 
> spelling: constraints


Thanks! Fixed.

  
> I'm not confident that if I would use this, so let's wait to see if someone
> else wants to give a +1.


Okay, but you agree that there are DBAs and users who want to see the
list of constraints, I think.

Currently, DBAs need the table name to see the constraint information.
However, with this feature, you can see its definition and table name
from the constraint name.
For example, it will be easier to understand how many foreign key
constraints are in the DB. The \d command also displays the constraints
but does not list them, so this feature is more beneficial for those who
want to check them.


Attached new patch includes:

   -  Add a filter by contype
   - Add pg_catalog prefix
   - Fix typo
   - Fix help message to add \dco

Not implemented yet:
   - NOT NULL constraint, and so on (based on pg_attribute)
   - Tab completion
   - Regression test
   - Document

Any comments welcome! :-D


Thanks,
Tatsuro Yamada