Hi Dag,
The patch adds the command "\dco" to list constraints in psql. This
seems useful to me.
Thank you!
The patch applies cleanly to HEAD, although some hunks have rather large
offsets.
As far as I can tell, the "\dco" command works as documented.
I have however found the following issues with the patch:
* A TAB character has been added to doc/src/sgml/ref/psql-ref.sgml -
this should be replaced with spaces.
Fixed.
* The call to listConstraints in line src/bin/psql/command.c 794 refers
to &cmd[2], this should rather be &cmd[3].
* The patch kills the "\dc" command in src/bin/psql/command.c
This can be fixed by adding the following at line 800:
else
success =
listConversions(pattern, show_verbose, show_system);
Oh, you are right! Fixed.
Another comment is that the "\dco" command outputs quite a lot of
information, which only fits in a wide terminal window. Would it be an
idea to only display the columns "Schema" and "Name" by default, and
use "+" to specify inclusion of the columns "Definition" and "Table".
I fixed the output columns as you proposed.
The current status of this patch is:
- Addressed Dag's comments
- Not implemented yet:
- Tab completion
- Regression test
- NOT NULL constraint, and so on (based on pg_attribute)
Please find attached new patch.
Thanks,
Tatsuro Yamada
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index caabb06..125ae3d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1388,6 +1388,26 @@ 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.
+ If <literal>+</literal> is appended to the command name, each object
+ is listed with its associated description.
+ </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 079f4a1..05ae25e 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -780,7 +780,26 @@ 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 't':
+ case 'u':
+ case 'x':
+ success =
listConstraints(&cmd[3], pattern, show_verbose, show_system);
+ break;
+ default:
+ status =
PSQL_CMD_UNKNOWN;
+ break;
+ }
+ else
+ success = listConversions(pattern,
show_verbose, show_system);
break;
case 'C':
success = listCasts(pattern, show_verbose);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 4dddf08..7acd25a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -19,6 +19,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"
@@ -4599,6 +4600,109 @@ 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 verbose, 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.nspname AS \"%s\", \n"
+ " cst.conname AS \"%s\" ",
+ gettext_noop("Schema"),
+ gettext_noop("Name")
+ );
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n
pg_catalog.pg_get_constraintdef(cst.oid) AS \"%s\", \n"
+ "
conrelid::pg_catalog.regclass AS \"%s\" ",
+ gettext_noop("Definition"),
+ gettext_noop("Table")
+ );
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM
pg_catalog.pg_constraint cst \n"
+ " JOIN
pg_catalog.pg_namespace n ON n.oid = cst.connamespace \n"
+ );
+
+ 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;");
+
+ 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 fd60796..0929ff5 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
verbose, 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 56afa68..6cfee66 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"));
