Re: Add psql command to list constraints

Hi,

On Mon, Nov 15, 2021 at 10:38:55AM +0900, Tatsuro Yamada wrote:
> postgres=# \dco
>                                          List of constsraints
>  Schema |          Name           |                       Definition                        |  Table
> --------+-------------------------+---------------------------------------------------------+----------
>  public | t01_chk_price_check     | CHECK ((price > (0)::numeric))                          | t01_chk
>  public | t02_uniq_product_no_key | UNIQUE (product_no)                                     | t02_uniq
>  public | t03_pk1_pkey            | PRIMARY KEY (product_no)                                | t03_pk1
>  public | t03_pk2_product_no_key  | UNIQUE (product_no)                                     | t03_pk2
>  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
>  public | t05_ex_c_excl           | EXCLUDE USING gist (c WITH &&)                          | t05_ex
> (7 rows)
> ====================================================================

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...

> +    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).

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

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

-- 
Justin