Thanks for review.
With + it shows description:
# \dA+
List of access methods
Name |
Type | Handler | Description
--------+-------+----------------------+-------------------------------
---------
brin | index | brinhandler | block range index (BRIN)
access method
btree | index | bthandler | b-tree index access method
gin | index | ginhandler | GIN index access method
gist | index | gisthandler | GiST index access method
hash | index | hashhandler | hash index access method
heap | table | heap_tableam_handler | heap table access method
spgist | index | spghandler | SP-GiST index access method
(7 rows)
Looks nice, but this fails for 9.4 or 9.5 server. I'm not sure
how far back versions we should support, though.
The command \dA initially displayed an error message when working
on a server version below 9.6, and I did not change this logic.
I'm not sure, but it probably makes sense for versions 9.4 and 9.5
to output something like this query does:
SELECT
a.amname AS "AM",
d.description AS "Description"
FROM pg_am a
JOIN pg_description d ON a.oid = d.objoid
ORDER BY 1;
#\dA
AM | Description
--------+-----------------------------
btree | b-tree index access method
gin | GIN index access method
gist | GiST index access method
hash | hash index access method
spgist | SP-GiST index access method
SELECT
a.amname AS "AM",
CASE WHEN a.amcanorder THEN 'yes' ELSE 'no' END AS "Ordering",
CASE WHEN a.amcanunique THEN 'yes' ELSE 'no' END AS "Unique
indexes",
CASE WHEN a.amcanmulticol THEN 'yes' ELSE 'no' END AS "Multicol
indexes",
CASE WHEN a.amsearchnulls THEN 'yes' ELSE 'no' END AS "Searching
NULLs",
CASE WHEN a.amclusterable THEN 'yes' ELSE 'no' END AS "Clusterale"
FROM pg_am a
JOIN pg_description d ON a.oid = d.objoid
ORDER BY 1;
#dA NAME
AM | Ordering | Unique indexes | Multicol indexes | Searching
NULLs
| Clusterale
--------+----------+----------------+------------------+-----------------+------------
btree | yes | yes | yes | yes
| yes
gin | no | no | yes | no
| no
gist | no | no | yes | yes
| yes
hash | no | no | no | no
| no
spgist | no | no | no | yes
| no
(5 rows)
The functionality of the \dAp command has been moved to \dA NAME.
Now the user can query the properties of a particular AM (or several,
using the search pattern) as follows:
# \dA h*
Index access
method properties
AM | Can order | Support unique indexes | Support indexes with
multiple columns | Support exclusion constraints | Can include non-key
columns
------+-----------+------------------------+---------------------------
------------+-------------------------------+------------------------
-----
hash | no | no |
no | yes
|
no
(1 row)
In the earlier patches they were "Can order", "Can unique", "Can
multi col", "Can exclude" and they indeed look
too-short. Nevertheless the current column names occupies the top
four places on the podium by their length. "Foreign-data wrapeer"
is on the fifth place. Most of them are just one noun. Some of
them are two-or-three-word nouns. Some of them are single-word
adjective followed by '?'. \dicp uses single-word adverbs or
a-few-words nouns without trailing '?'. How about the following?
8 Ordering yes/no
14 Unique indexes yes/no
16 Multicol indexes yes/no
21 Exclusion constraints yes/no
23 Include non-key columns yes/no
=====
20 Foreign-data wrapper
Does anyone have better wordings? Or, are the current wordings OK?
I like this version.
# \dAo gin jsonb_ops
List operators of family related to access method
AM | Opfamily Schema | Opfamily Name | Operator
-----+-----------------+---------------+--------------------
gin | pg_catalog | jsonb_ops | @> (jsonb, jsonb)
gin | pg_catalog | jsonb_ops | ? (jsonb, text)
gin | pg_catalog | jsonb_ops | ?| (jsonb, text[])
gin | pg_catalog | jsonb_ops | ?& (jsonb, text[])
(4 rows)
I'm not sure but couldn't we show the opfamily name in full
qualified? The schema is not a property of the AM.
Now Opfamily Schema is shown if opfamily name is not visible in the
current
schema search path (check by pg_opfamily_is_visible().
# \dAo+ gist circle_ops
List operators of family related to access
method
AM | Opfamily Schema | Opfamily Name | Operator |
Strategy | Purpose | Sort family
------+-----------------+---------------+----------------------+-------
---+----------+-------------
gist | pg_catalog | circle_ops | << (circle,
circle) | 1 | search |
...
gist | pg_catalog | circle_ops | <-> (circle,
point) | 15 | ordering | float_ops
"Sort family" doesn't make sense. "Sort opfamily" or "Sort
operator family"?
Renamed.
The \dAop command has been renamed to \dAp.
It displays list of support procedures associated with access method
operator families.
# \dAp hash array_ops
List of operator family procedures
AM | Family schema | Family name | Left | Right | Number
------+---------------+-------------+----------+----------+--------
hash | pg_catalog | array_ops | anyarray | anyarray | 1
hash | pg_catalog | array_ops | anyarray | anyarray | 2
(2 rows)
# \dAp+ hash array_ops
List of operator family procedures
AM | Family schema | Family name | Left | Right | Number
| Proc name
------+---------------+-------------+----------+----------+--------+---
------------------
hash | pg_catalog | array_ops | anyarray | anyarray | 1 |
hash_array
hash | pg_catalog | array_ops | anyarray | anyarray | 2 |
hash_array_extended
(2 rows)
It may be easier for the user to navigate in this list if the defining
feature in addition to the number is also the procedure name.
Even if it does not carry important information, it improves the
readability of the list. Maybe it makes sense to return field "Proc
name" to the main output?
"Number", "Proc name" doens't seem descriptive enough. It is
mentioned as support function number in the documentation. The
"Left" and "Right" are not necessarily parameter types of "Proc
name". But I don't come up with better namings. It is a bit
different thing, but "Left/Right arg type" is used elsewhere as
parameter types.
How about "AM", "Operator family", "Left arg type", "Right arg
type" and "Support function number", "Support function"? The
second from the last is 23 characters long. It could be "Support
number" instead.
I have no better idea how to improve naming so I used the names you
suggested.
0002-psql_add_index_info-v5.patch
I'm fine with this, but fails for 9.4 and 9.5. Also \dicp fails.
Maybe I missed something, but it works well on 9.4 and 9.5 for me.
Regards,
Sergey Cherkashin.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f4aabf5dc7..9ed0f83996 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -675,7 +675,7 @@
search and ordering purposes.)
</para>
- <table>
+ <table id="catalog-pg-amop-table">
<title><structname>pg_amop</structname> Columns</title>
<tgroup cols="4">
@@ -818,7 +818,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">
@@ -4458,7 +4458,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">
@@ -4720,7 +4720,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 1b5d82ed8e..da85f63400 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1219,11 +1219,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 ab259c473a..7b57c7cb49 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,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 f94a7a9c30..d3e349498f 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"
@@ -146,7 +147,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;
@@ -168,31 +169,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);
@@ -5740,3 +5733,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];
+
+ psql_error("The server (version %s) does not support access methods.\n",
+ 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)
+ psql_error("Did not find any AM named \"%s\".\n", 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 4ff1f91f38..6cce80f260 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);
@@ -111,4 +111,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 6fc4ebab1e..a8d2b53b62 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -224,7 +224,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 d6d8fd02f5..770d08f843 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -472,6 +472,23 @@ static const SchemaQuery Query_for_list_of_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_operator_families = {
+ /* min_server_version */
+ 0,
+ /* catname */
+ "pg_catalog.pg_opfamily c",
+ /* selcondition */
+ NULL,
+ /* viscondition */
+ "true",
+ /* namespace */
+ "c.opfnamespace",
+ /* result */
+ "pg_catalog.quote_ident(c.opfname)",
+ /* qualresult */
+ NULL
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1378,7 +1395,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",
@@ -3492,6 +3510,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 aa101de906..7496ede282 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4578,3 +4578,56 @@ last error message: division by zero
\echo 'last error code:' :LAST_ERROR_SQLSTATE
last error code: 22012
\unset FETCH_COUNT
+-- 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)
+(1 row)
+
+\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 fb7d17fc76..74cc7142da 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1031,3 +1031,11 @@ select 1/(15-unique2) from tenk1 order by unique2 limit 19;
\echo 'last error code:' :LAST_ERROR_SQLSTATE
\unset FETCH_COUNT
+
+-- 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*
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
