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