0002-psql_add_index_info-v6.patch

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index da85f63400..16d45d5c49 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1446,6 +1446,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 7b57c7cb49..0ab4a4c2ab 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -807,6 +807,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 d3e349498f..de66534973 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 "fe_utils/string_utils.h"
 
 #include "common.h"
@@ -45,6 +46,9 @@ 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);
 
 
 /*----------------
@@ -6116,3 +6120,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)
+				psql_error("Did not find any index named \"%s\"\n",
+						   index_pattern);
+			else
+				psql_error("Did not find any relations.\n");
+		}
+		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 6cce80f260..86ce18071b 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -128,4 +128,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 a8d2b53b62..1c9a242e2e 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -247,6 +247,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 770d08f843..93218741c2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1398,8 +1398,8 @@ 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",
+		"\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dicp", "\\dip",
+		"\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp",
 		"\\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 7496ede282..33036cadad 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4631,3 +4631,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 74cc7142da..0b7daa5d0e 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1039,3 +1039,6 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
 \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