Taking into account the wishes of all the reviewers, the current
position of the patch is as follows:
The \dA command displays a list of access methods.
# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | index | brinhandler
btree | index | bthandler
gin | index | ginhandler
gist | index | gisthandler
hash | index | hashhandler
heap | table | heap_tableam_handler
spgist | index | spghandler
(7 rows)
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)
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)
Table access method properties
Name | Type | Handler | Description
------+-------+----------------------+--------------------------
heap | table | heap_tableam_handler | heap table access method
(1 row)
Note that for heap, as well as for future table AM, a separate table is
displayed, since it is not clear which properties can be displayed for
them.
The \dAoc command has been renamed to \dAc.
The command displays information about operator classes. The "Input
type" field was left, because the user may first be interested in what
type of data opclass can work with,
and in the second - how it will keep this type inside. Nikita also
chose to leave the opfamily field as additional information.
# \dAc btree name
Index access method operator classes
AM | Input type | Storage type | Operator class | Default?
-------+------------+--------------+----------------+----------
btree | name | cstring | name_ops | yes
(1 row)
# \dAc+ btree record
Index access method operator classes
AM | Input type | Storage type | Operator class | Default? |
Operator family | Owner
-------+------------+--------------+------------------+----------+-----
-------------+-------
btree | record | | record_image_ops | no |
record_image_ops | zloj
btree | record | | record_ops | yes |
record_ops | zloj
(2 rows)
The \dAfo command has been renamed to \dAo.
\dAo displays information about operators as follows:
# \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)
# \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
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?
0002-psql_add_index_info-v5.patch
The commands \dip and \dicp have so far been left in the current form,
because although they display properties common to the whole AM,
as Nikita already wrote, this properties can be redefined.
# \dip pg_am_oid_index
Index properties
Schema | Name | Access method | Clusterable | Index
scan | Bitmap scan | Backward scan
------------+-----------------+---------------+-------------+--------
----+-------------+---------------
pg_catalog | pg_am_oid_index | btree | yes |
yes | yes | yes
(1 row)
# \dicp pg_amop_opr_fam_index
Index
pg_catalog.pg_amop_opr_fam_index
Column name | Expr | Opclass | ASC | Nulls first | Orderable |
Distance orderable | Returnable | Search array | Search nulls
-------------+-------------+----------+-----+-------------+-----------
+--------------------+------------+--------------+--------------
amopopr | amopopr | oid_ops | yes | no | yes |
no | yes | yes | yes
amoppurpose | amoppurpose | char_ops | yes | no | yes |
no | yes | yes | yes
amopfamily | amopfamily | oid_ops | yes | no | yes |
no | yes | yes | yes
Table: pg_amop
Access method: btree
Also please look through the documentation for these features. I am
sure that the information specified there can be submitted in a more
accurate and convenient form.
P.S. Since the formatting of the letter can brake the form of the
tables, I attach a text file with the same content so that you do not
have to do too much copy/paste to see original view =)
Sincerely
Sergey Cherkashin.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 0fd792ff1a..6105680be6 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">
@@ -4438,7 +4438,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">
@@ -4700,7 +4700,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 779e48437c..e5535e619c 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,29 +169,23 @@ describeAccessMethods(const char *pattern, bool verbose)
printfPQExpBuffer(&buf,
"SELECT amname AS \"%s\",\n"
" CASE amtype"
- " WHEN 'i' 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("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);
@@ -5625,3 +5620,382 @@ 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("Can order"),
+ gettext_noop("Support unique indexes"),
+ gettext_noop("Support indexes with multiple columns"),
+ gettext_noop("Support 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("Can 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"
+ " nsf.nspname AS \"%s\",\n"
+ " of.opfname 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 Schema"),
+ 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 family"));
+ 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, 3, o.amopstrategy, 4;");
+
+ 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"
+ " ns.nspname AS \"%s\",\n"
+ " of.opfname 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("Family schema"),
+ gettext_noop("Family name"),
+ gettext_noop("Left"),
+ gettext_noop("Right"),
+ gettext_noop("Number"));
+ 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, 6;");
+
+ 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 10ae21cc61..3a280ccd94 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",
@@ -3476,6 +3494,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..78961ef2ee 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 | Can order | Support unique indexes | Support indexes with multiple columns | Support exclusion constraints | Can 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 Schema | Opfamily Name | Operator
+------+-----------------+-----------------+-----------------
+ brin | pg_catalog | uuid_minmax_ops | < (uuid, uuid)
+ brin | pg_catalog | uuid_minmax_ops | <= (uuid, uuid)
+ brin | pg_catalog | uuid_minmax_ops | = (uuid, uuid)
+ brin | pg_catalog | uuid_minmax_ops | >= (uuid, uuid)
+ brin | pg_catalog | uuid_minmax_ops | > (uuid, uuid)
+(5 rows)
+
+\dAo * pg_catalog.jsonb_path_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Operator
+-----+-----------------+----------------+-------------------
+ gin | pg_catalog | jsonb_path_ops | @> (jsonb, jsonb)
+(1 row)
+
+\dAp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number
+------+---------------+-----------------+------+-------+--------
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 1
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 2
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 3
+ brin | pg_catalog | uuid_minmax_ops | uuid | uuid | 4
+(4 rows)
+
+\dAp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Left | Right | Number
+-------+---------------+-------------+------+-------+--------
+ btree | pg_catalog | uuid_ops | uuid | uuid | 1
+ btree | pg_catalog | uuid_ops | uuid | uuid | 2
+ hash | pg_catalog | uuid_ops | uuid | uuid | 1
+ hash | pg_catalog | 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 e5535e619c..2e61c0f9ff 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);
/*----------------
@@ -5999,3 +6003,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 3a280ccd94..2c6e47d0fd 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 78961ef2ee..32731e9242 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4631,3 +4631,19 @@ last error code: 22012
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
Taking into account the wishes of all the reviewers, the current position of
the patch is as follows:
The \dA command displays a list of access methods.
# \dA
List of access methods
Name | Type | Handler
--------+-------+----------------------
brin | index | brinhandler
btree | index | bthandler
gin | index | ginhandler
gist | index | gisthandler
hash | index | hashhandler
heap | table | heap_tableam_handler
spgist | index | spghandler
(7 rows)
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)
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)
Table access method properties
Name | Type | Handler | Description
------+-------+----------------------+--------------------------
heap | table | heap_tableam_handler | heap table access method
(1 row)
Note that for heap, as well as for future table AM, a separate table is
displayed, since it is not clear which properties can be displayed for them.
The \dAoc command has been renamed to \dAc.
The command displays information about operator classes. The "Input type" field
was left, because the user may first be interested in what type of data opclass
can work with,
and in the second - how it will keep this type inside. Nikita also chose to
leave the opfamily field as additional information.
# \dAc btree name
Index access method operator classes
AM | Input type | Storage type | Operator class | Default?
-------+------------+--------------+----------------+----------
btree | name | cstring | name_ops | yes
(1 row)
# \dAc+ btree record
Index access method operator classes
AM | Input type | Storage type | Operator class | Default? | Operator
family | Owner
-------+------------+--------------+------------------+----------+------------------+-------
btree | record | | record_image_ops | no |
record_image_ops | zloj
btree | record | | record_ops | yes | record_ops
| zloj
(2 rows)
The \dAfo command has been renamed to \dAo.
\dAo displays information about operators as follows:
# \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)
# \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
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?
0002-psql_add_index_info-v5.patch
The commands \dip and \dicp have so far been left in the current form, because
although they display properties common to the whole AM,
as Nikita already wrote, this properties can be redefined.
# \dip pg_am_oid_index
Index properties
Schema | Name | Access method | Clusterable | Index scan |
Bitmap scan | Backward scan
------------+-----------------+---------------+-------------+------------+-------------+---------------
pg_catalog | pg_am_oid_index | btree | yes | yes | yes
| yes
(1 row)
# \dicp pg_amop_opr_fam_index
Index
pg_catalog.pg_amop_opr_fam_index
Column name | Expr | Opclass | ASC | Nulls first | Orderable |
Distance orderable | Returnable | Search array | Search nulls
-------------+-------------+----------+-----+-------------+-----------+--------------------+------------+--------------+--------------
amopopr | amopopr | oid_ops | yes | no | yes | no
| yes | yes | yes
amoppurpose | amoppurpose | char_ops | yes | no | yes | no
| yes | yes | yes
amopfamily | amopfamily | oid_ops | yes | no | yes | no
| yes | yes | yes
Table: pg_amop
Access method: btree
Also please look through the documentation for these features. I am sure that
the information specified there can be submitted in a more accurate and
convenient form.
Sincerely
Sergey Cherkashin.
