Hi Justin,
Thanks for your comments!
Currently, DBAs need the table name to see the constraint information.
Or, they can query pg_constraint or information_schema: check_constraints,
table_constraints.
Yeah, right.
If they can use the meta-command instead of a long query against pg_constraint
or information_schema and also pg_attribulte, it would be helpful, I believe.
:-D
- success = listConversions(pattern,
show_verbose, show_system);
+ if (strncmp(cmd, "dco", 3) == 0) /* Constraint
*/
+ switch (cmd[3])
+ {
+ case '\0':
+ case '+':
Does "+" do anything ?
No, it doesn't. Removed.
+++ b/src/bin/psql/help.c
@@ -231,6 +231,7 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n"));
+ fprintf(output, _(" \\dco[S] [PATTERN] list constraint\n"));
Should be plural "constraints".
I think "exclude" should be called "exclusion" ("exclude" sounded to me like
you're going to provide a way to "exclude" types of constraints, like "xc"
would show everything except check constraints).
Thanks! Fixed the both.
Attached file is new patch. It includes:
- Fix help message s/constraint/constraints/
- s/Exclude/Exclusion/
- Remove unused modifier "+"
- Add document for \dco
Thanks,
Tatsuro Yamada
From eee92ee549e49d0b5aef438aff10236611db410e 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 POC3
- Fix help message s/constraint/constraints/
- s/Exclude/Exclusion/
- Remove unused modifier "+"
- Add document for \dco
---
doc/src/sgml/ref/psql-ref.sgml | 18 ++++++++
src/bin/psql/command.c | 18 +++++++-
src/bin/psql/describe.c | 98 ++++++++++++++++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 ++
src/bin/psql/help.c | 1 +
5 files changed, 137 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 48248f7..c6704d7 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1388,6 +1388,24 @@ testdb=>
<varlistentry>
+ <term><literal>\dco[cfptuxS] [ <link
linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists constraints.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only entries whose name matches the pattern are listed.
+ The modifiers <literal>c</literal> (check), <literal>f</literal>
(foreign key),
+ <literal>p</literal> (primary key), <literal>t</literal> (trigger),
+ <literal>u</literal> (unique), <literal>x</literal> (exclusion) can be
+ appended to the command, filtering the kind of constraints to list.
+ By default, only user-created constraints are shown; supply the
+ <literal>S</literal> modifier to include system objects.
+ </para>
+ </listitem>
+ </varlistentry>
+
+
+ <varlistentry>
<term><literal>\dC[+] [ <link linkend="app-psql-patterns"><replaceable
class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3de9d09..0379610 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -769,7 +769,23 @@ 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 '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..4724d63 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 exclusion
+ *
+ * and you can mix and match these in any order.
+ */
+bool
+listConstraints(const char *contypes, const char *pattern, bool showSystem)
+{
+ bool showCheck = strchr(contypes, CONSTRAINT_CHECK) != NULL;
+ bool showForeign = strchr(contypes, CONSTRAINT_FOREIGN) !=
NULL;
+ bool showPrimary = strchr(contypes, CONSTRAINT_PRIMARY) !=
NULL;
+ bool showTrigger = strchr(contypes, CONSTRAINT_TRIGGER) !=
NULL;
+ bool showUnique = strchr(contypes, CONSTRAINT_UNIQUE) !=
NULL;
+ bool showExclusion = strchr(contypes, CONSTRAINT_EXCLUSION)
!= 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 || showExclusion))
+ 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 (showExclusion)
+ 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..0804b9c 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[cfptuxS] [PATTERN] list constraints\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
