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
From ce46a3fa7252109348876ab9efff8bafcb119730 Mon Sep 17 00:00:00 2001
From: Tatsuro Yamada <yamatat...@gmail.com>
Date: Mon, 15 Nov 2021 17:58:31 +0900
Subject: [PATCH] Add psql command to list constraints POC2
- Add a filter by contype
- Add pg_catalog prefix
- Fix typo
- Fix help message to add \dco
---
src/bin/psql/command.c | 19 +++++++++-
src/bin/psql/describe.c | 98 +++++++++++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 ++
src/bin/psql/help.c | 1 +
4 files changed, 120 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3de9d09..92c61bc 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -769,7 +769,24 @@ exec_command_d(PsqlScanState scan_state, bool
active_branch, const char *cmd)
success = describeTablespaces(pattern,
show_verbose);
break;
case 'c':
- success = listConversions(pattern,
show_verbose, show_system);
+ if (strncmp(cmd, "dco", 3) == 0) /* Constraint
*/
+ switch (cmd[3])
+ {
+ case '\0':
+ case '+':
+ case 'S':
+ case 'c':
+ case 'f':
+ case 'p':
+ case 'u':
+ case 't':
+ case 'x':
+ success =
listConstraints(&cmd[2], pattern, show_system);
+ break;
+ default:
+ status =
PSQL_CMD_UNKNOWN;
+ break;
+ }
break;
case 'C':
success = listCasts(pattern, show_verbose);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ea721d9..8837626 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -18,6 +18,7 @@
#include "catalog/pg_attribute_d.h"
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
+#include "catalog/pg_constraint_d.h"
#include "catalog/pg_default_acl_d.h"
#include "common.h"
#include "common/logging.h"
@@ -4816,6 +4817,103 @@ listExtendedStats(const char *pattern)
}
/*
+ * \dco
+ *
+ * Describes constraints
+ *
+ * As with \d, you can specify the kinds of constraints you want:
+ *
+ * c for check
+ * f for foreign key
+ * p for primary key
+ * t for trigger
+ * u for unique
+ * x for exclude
+ *
+ * and you can mix and match these in any order.
+ */
+bool
+listConstraints(const char *contypes, const char *pattern, bool showSystem)
+{
+ bool showCheck = strchr(contypes, 'c') != NULL;
+ bool showForeign = strchr(contypes, 'f') != NULL;
+ bool showPrimary = strchr(contypes, 'p') != NULL;
+ bool showTrigger = strchr(contypes, 't') != NULL;
+ bool showUnique = strchr(contypes, 'u') != NULL;
+ bool showExclude = strchr(contypes, 'x') != NULL;
+ bool showAllkinds = false;
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ /* If contype was not selected, show them all */
+ if (!(showCheck || showForeign || showPrimary || showTrigger ||
showUnique || showExclude))
+ showAllkinds = true;
+
+ 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"
+ " conrelid::pg_catalog.regclass AS
\"%s\" \n"
+ "FROM pg_catalog.pg_constraint cst \n"
+ " JOIN pg_catalog.pg_namespace n
ON n.oid = cst.connamespace \n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Definition"),
+ gettext_noop("Table")
+ );
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ "WHERE n.nspname <>
'pg_catalog' \n"
+ " AND n.nspname <>
'information_schema' \n");
+
+ processSQLNamePattern(pset.db, &buf, pattern,
+ !showSystem && !pattern,
false,
+ "n.nspname", "cst.conname",
+ NULL,
"pg_catalog.pg_table_is_visible(cst.conrelid)");
+
+ if (!showAllkinds)
+ {
+ appendPQExpBufferStr(&buf, " AND cst.contype in (");
+
+ if (showCheck)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_CHECK) ",");
+ if (showForeign)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_FOREIGN) ",");
+ if (showPrimary)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_PRIMARY) ",");
+ if (showTrigger)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_TRIGGER) ",");
+ if (showUnique)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_UNIQUE) ",");
+ if (showExclude)
+ appendPQExpBufferStr(&buf,
CppAsString2(CONSTRAINT_EXCLUSION) ",");
+
+ appendPQExpBufferStr(&buf, "''"); /* dummy */
+ appendPQExpBufferStr(&buf, ")\n");
+ }
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of constraints");
+ myopt.translate_header = true;
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
* \dC
*
* Describes casts.
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 71b320f..083ca6c 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -76,6 +76,9 @@ extern bool listDomains(const char *pattern, bool verbose,
bool showSystem);
/* \dc */
extern bool listConversions(const char *pattern, bool verbose, bool
showSystem);
+/* \dco */
+extern bool listConstraints(const char *contypes, const char *pattern, bool
showSystem);
+
/* \dC */
extern bool listCasts(const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index db12a8b..62c11c5 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -231,6 +231,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dAp[+] [AMPTRN [OPFPTRN]] list support
functions of operator families\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n"));
+ fprintf(output, _(" \\dco[S] [PATTERN] list constraint\n"));
fprintf(output, _(" \\dC[+] [PATTERN] list casts\n"));
fprintf(output, _(" \\dd[S] [PATTERN] show object descriptions
not displayed elsewhere\n"));
fprintf(output, _(" \\dD[S+] [PATTERN] list domains\n"));
--
1.8.3.1
-- check
CREATE TABLE t01_chk (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
-- unique
CREATE TABLE t02_uniq (
product_no integer UNIQUE,
name text,
price numeric
);
-- primary key
CREATE TABLE t03_pk1 (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE t03_pk2 (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
-- foreign key
CREATE TABLE t04_fk (
order_id integer PRIMARY KEY,
product_no integer REFERENCES t03_pk1 (product_no),
quantity integer
);
-- exclude
CREATE TABLE t05_ex (
c circle,
EXCLUDE USING gist (c WITH &&)
);
-- trigger
CREATE OR REPLACE FUNCTION trigger_notice_ab() RETURNS trigger as $$
begin
raise notice 'trigger % on % % % for %: (a,b)=(%,%)',
TG_NAME, TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL,
NEW.a, NEW.b;
if TG_LEVEL = 'ROW' then
return NEW;
end if;
return null;
end;
$$ language plpgsql;
CREATE TABLE t06_trg (a int, b int);
CREATE CONSTRAINT TRIGGER t06_con_trig AFTER insert ON t06_trg DEFERRABLE
FOR each row EXECUTE PROCEDURE trigger_notice_ab();
-- domain
CREATE DOMAIN t07_posint AS integer CHECK (VALUE > 0);
CREATE TABLE t07_dm (id t07_posint);
-- not null
CREATE TABLE t08_notnull (
a varchar NOT NULL
);
-- default
CREATE TABLE t09_default (
a varchar DEFAULT 'a'
);
-- Tests
\dco
\dco t01_chk_price_check
\dco t02*
\dcoS pg_stat*
-- Visibility tests
---- visible
create schema con_s1;
create TABLE con_s1.t10_test (a int PRIMARY KEY);
set search_path to public, con_s1;
\dco
---- not visible
create role regress_constraint nosuperuser;
set role regress_constraint;
\dco
reset role;
-- clean-up
DROP TABLE t01_chk,
t02_uniq,
t03_pk1,
t03_pk2,
t04_fk,
t05_ex,
t06_trg,
t07_dm,
t08_notnull,
t09_default,
con_s1.t10_test;
drop domain t07_posint;
drop schema con_s1;
drop role regress_constraint;
