On 01.07.2019 14:06, Thomas Munro wrote:
On Sun, Mar 31, 2019 at 2:13 PM <s.cherkas...@postgrespro.ru> wrote:
Thanks for review.
Hi Sergey,
A new Commitfest is here and this doesn't apply -- could you please
post a rebase?
Thanks,
Attached 7th version of the patches rebased onto current master.
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From c52bec3d2d5fa13b2b72a9e5367c98affa6b6227 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Mon, 15 Jul 2019 15:52:49 +0300
Subject: [PATCH 1/2] Add psql AM info commands
---
doc/src/sgml/catalogs.sgml | 8 +-
doc/src/sgml/ref/psql-ref.sgml | 81 +++++++-
src/bin/psql/command.c | 20 +-
src/bin/psql/describe.c | 416 +++++++++++++++++++++++++++++++++++--
src/bin/psql/describe.h | 19 +-
src/bin/psql/help.c | 6 +-
src/bin/psql/tab-complete.c | 16 +-
src/test/regress/expected/psql.out | 55 +++++
src/test/regress/sql/psql.sql | 8 +
9 files changed, 596 insertions(+), 33 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 68ad507..ec79c11 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -681,7 +681,7 @@
search and ordering purposes.)
</para>
- <table>
+ <table id="catalog-pg-amop-table">
<title><structname>pg_amop</structname> Columns</title>
<tgroup cols="4">
@@ -824,7 +824,7 @@
is one row for each support function belonging to an operator family.
</para>
- <table>
+ <table id="catalog-pg-amproc-table">
<title><structname>pg_amproc</structname> Columns</title>
<tgroup cols="4">
@@ -4467,7 +4467,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator classes are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opclass-table">
<title><structname>pg_opclass</structname> Columns</title>
<tgroup cols="4">
@@ -4729,7 +4729,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator families are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opfamily-table">
<title><structname>pg_opfamily</structname> Columns</title>
<tgroup cols="4">
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 7789fc6..e690c4d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1222,11 +1222,82 @@ testdb=>
<listitem>
<para>
- Lists access methods. If <replaceable
- class="parameter">pattern</replaceable> is specified, only access
- methods whose names match the pattern are shown. If
- <literal>+</literal> is appended to the command name, each access
- method is listed with its associated handler function and description.
+ Lists access methods with their associated handler function. If
+ <literal>+</literal> is appended to the command name, additional
+ description is provided.
+ If <replaceable class="parameter">pattern</replaceable> is specified,
+ the command displays the properties of the access methods whose names
+ match the search pattern.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAc[+]
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">input-type-pattern</replaceable></link>]]
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ Shows info index access method operator classes listed in
+ <xref linkend="catalog-pg-opclass-table"/>.
+ If <replaceable class="parameter">access-method-patttern</replaceable>
+ is specified, only operator classes associated with access method whose
+ name matches pattern are shown.
+ If <replaceable class="parameter">input-type-pattern</replaceable>
+ is specified, only procedures associated with families whose input type
+ matches the pattern are shown.
+ If <literal>+</literal> is appended to the command name, operator family
+ and owner are listed.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAo[+]
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Lists operators (<xref linkend="catalog-pg-amop-table"/>) associated
+ with access method operator families. If
+ <replaceable class="parameter">access-method-patttern</replaceable> is
+ specified, only operators associated with access method whose name
+ matches pattern are shown. If
+ <replaceable class="parameter">operator-family-pattern</replaceable> is
+ specified, only operators associated with families whose name matches
+ the pattern are shown.
+ If <literal>+</literal> is appended to the command name, displays
+ additional info.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAp[+]
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-family-pattern</replaceable></link>]]
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ Lists procedures (<xref linkend="catalog-pg-amproc-table"/>) associated
+ with access method operator families.
+ If <replaceable class="parameter">access-method-patttern</replaceable>
+ is specified, only procedures associated with access method whose name
+ matches pattern are shown.
+ If <replaceable class="parameter">operator-family-pattern</replaceable>
+ is specified, only procedures associated with families whose name
+ matches the pattern are shown.
+ If <literal>+</literal> is appended to the command name, procedures
+ listed with its names.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index c0a7a55..e6cb260 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -722,7 +722,25 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listTables("tvmsE", NULL, show_verbose, show_system);
break;
case 'A':
- success = describeAccessMethods(pattern, show_verbose);
+ {
+ char *pattern2 = NULL;
+ if (pattern)
+ pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
+
+ if (strncmp(cmd, "dAo", 3) == 0)
+ success = listFamilyClassOperators(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAp", 3) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAc", 3) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else if (pattern)
+ success = describeAccessMethodProperties(pattern);
+ else
+ success = listAccessMethods(show_verbose);
+
+ if (pattern2)
+ free(pattern2);
+ }
break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8b4cd53..9cae8c8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -14,6 +14,7 @@
#include <ctype.h>
+#include "catalog/pg_am.h"
#include "catalog/pg_attribute_d.h"
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
@@ -148,7 +149,7 @@ describeAggregates(const char *pattern, bool verbose, bool showSystem)
* Takes an optional regexp to select particular access methods
*/
bool
-describeAccessMethods(const char *pattern, bool verbose)
+listAccessMethods(bool verbose)
{
PQExpBufferData buf;
PGresult *res;
@@ -170,31 +171,23 @@ describeAccessMethods(const char *pattern, bool verbose)
printfPQExpBuffer(&buf,
"SELECT amname AS \"%s\",\n"
" CASE amtype"
- " WHEN 'i' THEN '%s'"
- " WHEN 't' THEN '%s'"
- " END AS \"%s\"",
+ " WHEN 'i' THEN '%s'"
+ " WHEN 't' THEN '%s'"
+ " END AS \"%s\",\n"
+ " amhandler AS \"%s\"",
gettext_noop("Name"),
- gettext_noop("Index"),
- gettext_noop("Table"),
- gettext_noop("Type"));
+ gettext_noop("index"),
+ gettext_noop("table"),
+ gettext_noop("Type"),
+ gettext_noop("Handler"));
if (verbose)
- {
appendPQExpBuffer(&buf,
- ",\n amhandler AS \"%s\",\n"
- " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
- gettext_noop("Handler"),
+ ",\n pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
gettext_noop("Description"));
- }
-
appendPQExpBufferStr(&buf,
- "\nFROM pg_catalog.pg_am\n");
-
- processSQLNamePattern(pset.db, &buf, pattern, false, false,
- NULL, "amname", NULL,
- NULL);
-
- appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ "\nFROM pg_catalog.pg_am\n"
+ "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
@@ -5985,3 +5978,386 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dA NAME
+ * Describes access method properties.
+ *
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethodProperties(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ bool found_result = false;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns_i[] = {true, true, true, true, true, true};
+ static const bool translate_columns_t[] = {true, true, true, true};
+
+ if (pset.sversion < 90600)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support access methods.",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ "SELECT a.amname AS \"%1$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_order')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%4$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_unique')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_multi_col')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%6$s\",\n"
+ " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_exclude')\n"
+ " THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n"
+ :
+ "SELECT a.amname AS \"%1$s\",\n"
+ " CASE WHEN a.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%4$s\",\n"
+ " CASE WHEN a.amcanunique THEN '%2$s' ELSE '%3$s' END AS \"%5$s\",\n"
+ " CASE WHEN a.amcanmulticol THEN '%2$s' ELSE '%3$s' END AS \"%6$s\",\n"
+ " CASE WHEN a.amgettuple <> 0 THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n",
+ gettext_noop("AM"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Ordering"),
+ gettext_noop("Unique indexes"),
+ gettext_noop("Multicol indexes"),
+ gettext_noop("Exclusion constraints"));
+
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 110000
+ ? " CASE WHEN pg_catalog.pg_indexam_has_property(a.oid, 'can_include')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\""
+ : " CASE WHEN false THEN '%1$s' ELSE '%2$s' END AS \"%3$s\"",
+ gettext_noop("yes"), gettext_noop("no"),
+ gettext_noop("Include non-key columns"));
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_am a\n"
+ " WHERE a.amtype = 'i'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ NULL, "amname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns_i;
+ myopt.n_translate_columns = lengthof(translate_columns_i);
+
+ if (PQntuples(res) > 0)
+ {
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ found_result = true;
+ }
+
+ PQclear(res);
+
+ /* Table AM */
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT a.amname AS \"%s\",\n"
+ " 'table' AS \"%s\",\n"
+ " a.amhandler AS \"%s\",\n"
+ " pg_catalog.obj_description(a.oid, 'pg_am') AS \"%s\"\n"
+ "FROM pg_catalog.pg_am a\n"
+ " WHERE a.amtype = 't'\n",
+ gettext_noop("Name"),
+ gettext_noop("Type"),
+ gettext_noop("Handler"),
+ gettext_noop("Description"));
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ NULL, "a.amname", NULL, NULL);
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Table access method properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns_t;
+ myopt.n_translate_columns = lengthof(translate_columns_t);
+
+ if (PQntuples(res) > 0)
+ {
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ found_result = true;
+ }
+
+ PQclear(res);
+
+ if (!found_result)
+ pg_log_error("Did not find any AM named \"%s\".", pattern);
+
+ return true;
+}
+
+/*
+ * \dAo
+ * Lists operators associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listFamilyClassOperators(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, true, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', nsf.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " format ('%%s (%%s, %%s)',\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_operator_is_visible(op.oid) \n"
+ " THEN op.oprname::pg_catalog.text \n"
+ " ELSE o.amopopr::pg_catalog.regoper::pg_catalog.text \n"
+ " END,\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL),\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL)\n"
+ " ) AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Operator"));
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", o.amopstrategy AS \"%s\",\n"
+ " CASE o.amoppurpose\n"
+ " WHEN 'o' THEN '%s'\n"
+ " WHEN 's' THEN '%s'\n"
+ " END AS \"%s\",\n"
+ " ofs.opfname AS \"%s\"\n",
+ gettext_noop("Strategy"),
+ gettext_noop("ordering"),
+ gettext_noop("search"),
+ gettext_noop("Purpose"),
+ gettext_noop("Sort opfamily"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amop o\n"
+ " LEFT JOIN pg_catalog.pg_operator op ON op.oid = o.amopopr\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "nsf.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List operators of family related to access method");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAp
+ * Lists procedures associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ns.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
+ " ap.amprocnum AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Operator family"),
+ gettext_noop("Left arg type"),
+ gettext_noop("Right arg type"),
+ gettext_noop("Support function"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", ap.amproc::pg_catalog.regproc::pg_catalog.text AS \"%s\"\n",
+ gettext_noop("Proc name"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amproc ap\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "ns.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf,
+ "ORDER BY 1, 2, 3, 4, 5;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator family procedures");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAc
+ * List index access method operator classes.
+ * Takes an optional regexp to select particular access method and operator class.
+ */
+bool
+describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *type_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT"
+ " am.amname AS \"%s\",\n"
+ " c.opcintype::pg_catalog.regtype AS \"%s\",\n"
+ " (CASE WHEN c.opckeytype <> 0 AND c.opckeytype <> c.opcintype\n"
+ " THEN c.opckeytype\n"
+ " ELSE NULL -- c.opcintype\n"
+ " END)::pg_catalog.regtype AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opclass_is_visible(c.oid)\n"
+ " THEN format('%%I', c.opcname)\n"
+ " ELSE format('%%I.%%I', n.nspname, c.opcname)\n"
+ " END AS \"%s\",\n"
+ " (CASE WHEN c.opcdefault\n"
+ " THEN '%s'\n"
+ " ELSE '%s'\n"
+ " END) AS \"%s\"",
+ gettext_noop("AM"),
+ gettext_noop("Input type"),
+ gettext_noop("Storage type"),
+ gettext_noop("Operator class"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Default?"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ofn.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\"\n",
+ gettext_noop("Operator family"),
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ "\nFROM pg_catalog.pg_opclass c\n"
+ " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
+ " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = c.opcintype\n"
+ );
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
+ " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname", NULL, NULL);
+ if (type_pattern)
+ processSQLNamePattern(pset.db, &buf, type_pattern, have_where, false,
+ NULL, "t1.typname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method operator classes");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 17736c3..6c87401 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,7 +13,7 @@
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
/* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool listAccessMethods(bool verbose);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
@@ -114,4 +114,21 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \dA foo*/
+extern bool describeAccessMethodProperties(const char *pattern);
+
+/* \dAp */
+extern bool listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern,
+ bool verbose);
+
+/* \dAo */
+extern bool listFamilyClassOperators(const char *accessMethod_pattern,
+ const char *family_pattern, bool verbose);
+
+/* \dAc */
+extern bool describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *opclass_pattern,
+ bool verbose);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index d9b982d..dc591c3 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -226,7 +226,11 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\d[S+] list tables, views, and sequences\n"));
fprintf(output, _(" \\d[S+] NAME describe table, view, sequence, or index\n"));
fprintf(output, _(" \\da[S] [PATTERN] list aggregates\n"));
- fprintf(output, _(" \\dA[+] [PATTERN] list access methods\n"));
+ fprintf(output, _(" \\dA[+] list access methods\n"));
+ fprintf(output, _(" \\dA NAME describe properties of access method\n"));
+ fprintf(output, _(" \\dAc[+] [AMPTRN [TYPEPTRN]] list operator classes of index access methods\n"));
+ fprintf(output, _(" \\dAo[+] [AMPTRN [OPFPTRN]] list operators of family related to access method\n"));
+ fprintf(output, _(" \\dAp[+] [AMPTRN [OPFPTRN]] list procedures of operator family related to access method\n"));
fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n"));
fprintf(output, _(" \\dC[+] [PATTERN] list casts\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 3f7001f..5d14a93 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -495,6 +495,13 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_operator_families = {
+ .catname = "pg_catalog.pg_opfamily c",
+ .viscondition = "pg_catalog.pg_opfamily_is_visible(c.oid)",
+ .namespace = "c.opfnamespace",
+ .result = "pg_catalog.quote_ident(c.opfname)",
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1417,7 +1424,8 @@ psql_completion(const char *text, int start, int end)
"\\a",
"\\connect", "\\conninfo", "\\C", "\\cd", "\\copy",
"\\copyright", "\\crosstabview",
- "\\d", "\\da", "\\dA", "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
+ "\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc",
+ "\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
@@ -3590,6 +3598,12 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS("\\da*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS("\\dAp*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
+ else if (TailMatchesCS("\\dAo*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
+ else if (TailMatchesCS("\\dAc*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL);
else if (TailMatchesCS("\\dA*"))
COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (TailMatchesCS("\\db*"))
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 9021c80..4d17be3 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4748,3 +4748,58 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role regress_partitioning_role;
+-- check printing info about access methods
+\dA gin
+ Index access method properties
+ AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns
+-----+----------+----------------+------------------+-----------------------+-------------------------
+ gin | no | no | yes | no | no
+(1 row)
+
+\dAo brin uuid_minmax_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+------+-----------------+-----------------
+ brin | uuid_minmax_ops | < (uuid, uuid)
+ brin | uuid_minmax_ops | <= (uuid, uuid)
+ brin | uuid_minmax_ops | = (uuid, uuid)
+ brin | uuid_minmax_ops | >= (uuid, uuid)
+ brin | uuid_minmax_ops | > (uuid, uuid)
+(5 rows)
+
+\dAo * pg_catalog.jsonb_path_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+-----+----------------+----------------------
+ gin | jsonb_path_ops | @> (jsonb, jsonb)
+ gin | jsonb_path_ops | @? (jsonb, jsonpath)
+ gin | jsonb_path_ops | @@ (jsonb, jsonpath)
+(3 rows)
+
+\dAp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+------+-----------------+---------------+----------------+------------------
+ brin | uuid_minmax_ops | uuid | uuid | 1
+ brin | uuid_minmax_ops | uuid | uuid | 2
+ brin | uuid_minmax_ops | uuid | uuid | 3
+ brin | uuid_minmax_ops | uuid | uuid | 4
+(4 rows)
+
+\dAp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+-------+-----------------+---------------+----------------+------------------
+ btree | uuid_ops | uuid | uuid | 1
+ btree | uuid_ops | uuid | uuid | 2
+ hash | uuid_ops | uuid | uuid | 1
+ hash | uuid_ops | uuid | uuid | 2
+(4 rows)
+
+\dAc brin pg*.oid*
+ Index access method operator classes
+ AM | Input type | Storage type | Operator class | Default?
+------+------------+--------------+----------------+----------
+ brin | oid | | oid_minmax_ops | yes
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index cefe41b..77941c4 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1131,3 +1131,11 @@ set search_path to default;
set role to default;
drop role regress_partitioning_role;
+
+-- check printing info about access methods
+\dA gin
+\dAo brin uuid_minmax_ops
+\dAo * pg_catalog.jsonb_path_ops
+\dAp brin uuid_minmax_ops
+\dAp * pg_catalog.uuid_ops
+\dAc brin pg*.oid*
--
2.7.4
>From 05e17933711d792473d8300db166a470081c6e6b Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Mon, 15 Jul 2019 17:11:06 +0300
Subject: [PATCH 2/2] Add psql index info commands
---
doc/src/sgml/ref/psql-ref.sgml | 28 ++++
src/bin/psql/command.c | 10 ++
src/bin/psql/describe.c | 291 ++++++++++++++++++++++++++++++++++++-
src/bin/psql/describe.h | 7 +
src/bin/psql/help.c | 2 +
src/bin/psql/tab-complete.c | 5 +-
src/test/regress/expected/psql.out | 16 ++
src/test/regress/sql/psql.sql | 3 +
8 files changed, 359 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index e690c4d..c4ff542 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1451,6 +1451,34 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>\dip [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Shows index properties listed in
+ <xref linkend="functions-info-index-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dicp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Shows index column properties listed in
+ <xref linkend="functions-info-index-column-props"/>.
+ If <replaceable class="parameter">pattern</replaceable> is
+ specified, only access methods whose names match the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><literal>\des[+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e6cb260..36e2efe 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -827,6 +827,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
case 'v':
case 'm':
case 'i':
+ if (strncmp(cmd, "dip", 3) == 0)
+ {
+ success = describeIndexProperties(pattern, show_system);
+ break;
+ }
+ else if (strncmp(cmd, "dicp", 4) == 0)
+ {
+ success = describeIndexColumnProperties(pattern, show_system);
+ break;
+ }
case 's':
case 'E':
success = listTables(&cmd[1], pattern, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 9cae8c8..86c3bd3 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -19,6 +19,7 @@
#include "catalog/pg_cast_d.h"
#include "catalog/pg_class_d.h"
#include "catalog/pg_default_acl_d.h"
+#include "catalog/pg_index.h"
#include "common/logging.h"
#include "fe_utils/mbprint.h"
@@ -47,7 +48,11 @@ static bool describeOneTSConfig(const char *oid, const char *nspname,
const char *pnspname, const char *prsname);
static void printACLColumn(PQExpBuffer buf, const char *colname);
static bool listOneExtensionContents(const char *extname, const char *oid);
-
+static bool describeOneIndexColumnProperties(const char *oid,
+ const char *nspname,
+ const char *idxname,
+ const char *amname,
+ const char *tabname);
/*----------------
* Handlers for various slash commands displaying some sort of list
@@ -6361,3 +6366,287 @@ describeAccessMethodOperatorClasses(const char *access_method_pattern,
PQclear(res);
return true;
}
+
+/*
+ * \dip
+ * Describes index properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexProperties(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT"
+ " n.nspname AS \"%s\",\n"
+ " c.relname AS \"%s\",\n"
+ " am.amname AS \"%s\",\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Access method"));
+ appendPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'clusterable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'index_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_has_property(c.oid, 'backward_scan')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n"
+ :
+ " CASE WHEN am.amclusterable THEN '%1$s' ELSE '%2$s' END AS \"%3$s\",\n"
+ " CASE WHEN am.amgettuple <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%4$s\",\n"
+ " CASE WHEN am.amgetbitmap <> 0 THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN am.amcanbackward THEN '%1$s' ELSE '%2$s' END AS \"%6$s\"\n",
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Clusterable"),
+ gettext_noop("Index scan"),
+ gettext_noop("Bitmap scan"),
+ gettext_noop("Backward scan"));
+ appendPQExpBufferStr(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"
+ "WHERE c.relkind='i'\n"
+ " AND n.nspname !~ 'pg_toast'\n");
+
+ if (!showSystem && !pattern)
+ appendPQExpBufferStr(&buf,
+ " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, true, false,
+ "n.nspname", "c.relname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index properties");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dicp
+ * Describes index index column properties.
+ *
+ * Takes an optional regexp to select particular index.
+ */
+bool
+describeIndexColumnProperties(const char *index_pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ int i;
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT c.oid,\n"
+ " n.nspname,\n"
+ " c.relname,\n"
+ " am.amname,\n"
+ " c2.relname\n"
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = relnamespace\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n"
+ " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid\n");
+
+ appendPQExpBufferStr(&buf, "WHERE c.relkind='i'\n");
+
+ if (!showSystem && !index_pattern)
+ appendPQExpBufferStr(&buf, "AND n.nspname <> 'pg_catalog'\n"
+ "AND n.nspname <> 'information_schema'\n");
+
+ processSQLNamePattern(pset.db, &buf, index_pattern, true, false,
+ "n.nspname", "c.relname", NULL,
+ "pg_catalog.pg_table_is_visible(c.oid)");
+
+ appendPQExpBufferStr(&buf, "ORDER BY 2, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ {
+ if (index_pattern)
+ pg_log_error("Did not find any index named \"%s\".",
+ index_pattern);
+ else
+ pg_log_error("Did not find any relations.");
+ }
+ PQclear(res);
+ return false;
+ }
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ const char *oid = PQgetvalue(res, i, 0);
+ const char *nspname = PQgetvalue(res, i, 1);
+ const char *idxname = PQgetvalue(res, i, 2);
+ const char *amname = PQgetvalue(res, i, 3);
+ const char *tabname = PQgetvalue(res, i, 4);
+
+ if (!describeOneIndexColumnProperties(oid, nspname, idxname, amname,
+ tabname))
+ {
+ PQclear(res);
+ return false;
+ }
+ if (cancel_pressed)
+ {
+ PQclear(res);
+ return false;
+ }
+ }
+
+ PQclear(res);
+ return true;
+}
+
+static bool
+describeOneIndexColumnProperties(const char *oid,
+ const char *nspname,
+ const char *idxname,
+ const char *amname,
+ const char *tabname)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ char *footers[3] = {NULL, NULL};
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " a.attname AS \"%s\",\n"
+ " pg_catalog.pg_get_indexdef(i.indexrelid, a.attnum, true) AS \"%s\",\n"
+ " CASE WHEN pg_catalog.pg_opclass_is_visible(o.oid) THEN '' ELSE n.nspname || '.' END || o.opcname AS \"%s\",\n",
+ gettext_noop("Column name"),
+ gettext_noop("Expr"),
+ gettext_noop("Opclass"));
+
+ if (pset.sversion >= 90600)
+ appendPQExpBuffer(&buf,
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n"
+ " THEN '%1$s' ELSE '%2$s' END \n"
+ " ELSE NULL"
+ " END AS \"%3$s\","
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n"
+ " THEN '%1$s' ELSE '%2$s' END \n"
+ " ELSE NULL"
+ " END AS \"%4$s\","
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%5$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%6$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%7$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%8$s\",\n"
+ " CASE WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls')\n"
+ " THEN '%1$s' ELSE '%2$s' END AS \"%9$s\"\n",
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("ASC"),
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Returnable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+ else
+ appendPQExpBuffer(&buf,
+ " CASE WHEN am.amcanorder THEN CASE\n"
+ " WHEN (i.indoption[a.attnum - 1] & %1$d) = 0\n" /* INDOPTION_DESC */
+ " THEN '%2$s' ELSE '%3$s' END\n"
+ " ELSE NULL END AS \"%4$s\",\n"
+ " CASE WHEN am.amcanorder THEN CASE\n"
+ " WHEN (i.indoption[a.attnum - 1] & %5$d) <> 0\n" /* INDOPTION_NULLS_FIRST */
+ " THEN '%2$s' ELSE '%3$s' END\n"
+ " ELSE NULL END AS \"%6$s\",\n"
+ " CASE WHEN am.amcanorder THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n"
+ " CASE WHEN am.amcanorderbyop THEN '%2$s' ELSE '%3$s' END AS \"%8$s\",\n"
+ " CASE WHEN am.amsearcharray THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n"
+ " CASE WHEN am.amsearchnulls THEN '%2$s' ELSE '%3$s' END AS \"%10$s\"\n",
+ INDOPTION_DESC,
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("ASC"),
+ INDOPTION_NULLS_FIRST,
+ gettext_noop("Nulls first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_class c\n"
+ " LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n"
+ " LEFT JOIN pg_catalog.pg_opclass o ON o.oid = (i.indclass::pg_catalog.oid[])[a.attnum - 1]\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.opcnamespace\n");
+ if (pset.sversion < 90600)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam\n");
+ appendPQExpBuffer(&buf,
+ "WHERE c.oid = %s\n"
+ "ORDER BY a.attnum",
+ oid);
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+ if (PQntuples(res) == 0)
+ {
+ PQclear(res);
+ return true;
+ }
+
+ myopt.nullPrint = NULL;
+ myopt.title = psprintf(_("Index %s.%s"), nspname, idxname);
+ footers[0] = psprintf(_("Table: %s"), tabname);
+ footers[1] = psprintf(_("Access method: %s"), amname);
+ myopt.footers = footers;
+ myopt.topt.default_footer = false;
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 6c87401..f9c3ea6 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -131,4 +131,11 @@ extern bool describeAccessMethodOperatorClasses(const char *access_method_patter
const char *opclass_pattern,
bool verbose);
+/* \dip */
+extern bool describeIndexProperties(const char *pattern, bool showSystem);
+
+/* \dicp */
+extern bool describeIndexColumnProperties(const char *indexPattern,
+ bool showSystem);
+
#endif /* DESCRIBE_H */
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index dc591c3..dbb873e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -249,6 +249,8 @@ slashUsage(unsigned short int pager)
fprintf(output, _(" \\dFt[+] [PATTERN] list text search templates\n"));
fprintf(output, _(" \\dg[S+] [PATTERN] list roles\n"));
fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
+ fprintf(output, _(" \\dip[S] [PATTERN] list indexes with properties\n"));
+ fprintf(output, _(" \\dicp[S][PATTERN] show index column properties\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dL[S+] [PATTERN] list procedural languages\n"));
fprintf(output, _(" \\dm[S+] [PATTERN] list materialized views\n"));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5d14a93..0e3a1eff 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1427,8 +1427,9 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\dAp", "\\dAo", "\\dAp", "\\dAc",
"\\db", "\\dc", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
- "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
- "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
+ "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip",
+ "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO",
+ "\\dp", "\\dP", "\\dPi", "\\dPt",
"\\drds", "\\dRs", "\\dRp", "\\ds", "\\dS",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dy",
"\\e", "\\echo", "\\ef", "\\elif", "\\else", "\\encoding",
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 4d17be3..2580bae 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4803,3 +4803,19 @@ List operators of family related to access method
brin | oid | | oid_minmax_ops | yes
(1 row)
+-- check printing info about indexes
+\dip pg_am_name_index
+ Index properties
+ Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan
+------------+------------------+---------------+-------------+------------+-------------+---------------
+ pg_catalog | pg_am_name_index | btree | yes | yes | yes | yes
+(1 row)
+
+\dicp pg_am_name_index
+ Index pg_catalog.pg_am_name_index
+ Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+-------------+--------+----------+-----+-------------+-----------+--------------------+------------+--------------+--------------
+ amname | amname | name_ops | yes | no | yes | no | yes | yes | yes
+Table: pg_am
+Access method: btree
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 77941c4..95e7dbe 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1139,3 +1139,6 @@ drop role regress_partitioning_role;
\dAp brin uuid_minmax_ops
\dAp * pg_catalog.uuid_ops
\dAc brin pg*.oid*
+-- check printing info about indexes
+\dip pg_am_name_index
+\dicp pg_am_name_index
--
2.7.4
