Here are some fixes. But I'm not sure that the renaming of columns for
the '\dAp' command is sufficiently laconic and informative. If you have
any suggestions on how to improve them, I will be very grateful.
Best regards,
Sergey Cherkashin.
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c134bca809..e25412b7ce 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">
@@ -4421,7 +4421,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">
@@ -4683,7 +4683,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 6e6d0f42d1..bee8987c85 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1204,6 +1204,83 @@ testdb=>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term>
+ <literal>\dAfo
+ [<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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAfp
+ [<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>
+ List 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAoc
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">access-method-pattern</replaceable></link>
+ [<link linkend="app-psql-patterns"><replaceable class="parameter">operator-class-pattern</replaceable></link>]]
+ </literal>
+ </term>
+ <listitem>
+ <para>
+ Shows 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">operator-class-pattern</replaceable>
+ is specified, only procedures associated with families whose name
+ matches the pattern are shown.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>\dAp [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+
+ <listitem>
+ <para>
+ Shows access method properties listed in
+ <xref linkend="functions-info-indexam-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>\db[+] [ <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 ee88e1ca5c..7c35aed018 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -719,7 +719,24 @@ 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, "dAp", 3) == 0)
+ success = describeAccessMethodProperties(pattern);
+ else if (strncmp(cmd, "dAfo", 4) == 0)
+ success = listFamilyClassOperators(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAfp", 4) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAoc", 4) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else
+ success = describeAccessMethods(pattern, 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 0a181b01d9..4947fb69fa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5611,3 +5611,305 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dAp
+ * Describes access method properties.
+ *
+ * Takes an optional regexp to select particular access methods
+ */
+bool
+describeAccessMethodProperties(const char *pattern)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns[] = {false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ pset.sversion >= 90600 ?
+ "SELECT a.amname AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_order') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_unique') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_multi_col') AS \"%s\",\n"
+ " pg_catalog.pg_indexam_has_property(a.oid, 'can_exclude') AS \"%s\",\n"
+ :
+ "SELECT a.amname AS \"%s\",\n"
+ " a.amcanorder AS \"%s\",\n"
+ " a.amcanunique AS \"%s\",\n"
+ " a.amcanmulticol AS \"%s\",\n"
+ " a.amgettuple <> 0 AS \"%s\",\n",
+ gettext_noop("AM"),
+ 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
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_include') AS \"%s\""
+ : "FALSE AS \"%s\"",
+ gettext_noop("Can include non-key columns"));
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_am a\n");
+
+ processSQLNamePattern(pset.db, &buf, pattern, false, 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 = _("Access method 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;
+}
+
+/*
+ * \dAfo
+ * 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 DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " nsf.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\",\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL) AS \"%s\",\n"
+ " CASE 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 AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Schema"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Left type"),
+ gettext_noop("Right type"),
+ 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, 4, 5, 6;");
+
+ 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;
+}
+
+/*
+ * \dAfp
+ * 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;
+}
+
+/*
+ * \dAoc
+ * 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 *opclass_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"
+ " am.amname AS \"%s\",\n"
+ " n.nspname AS \"%s\",\n"
+ " c.opcname AS \"%s\",\n",
+ gettext_noop("Access method"),
+ gettext_noop("Schema"),
+ gettext_noop("Name"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\",\n",
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ " (CASE WHEN pg_catalog.pg_opfamily_is_visible(of.oid) THEN '' ELSE ofn.nspname || '.' END) || of.opfname AS \"%s\",\n"
+ " c.opcintype::pg_catalog.regtype AS \"%s\"\n",
+ gettext_noop("Family"),
+ gettext_noop("Indexed type"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", c.opcdefault AS \"%s\",\n"
+ " (CASE WHEN c.opckeytype = 0 OR c.opckeytype = c.opcintype\n"
+ " THEN NULL\n"
+ " ELSE c.opckeytype\n"
+ " END)::pg_catalog.regtype AS \"%s\"\n",
+ gettext_noop("Is default"),
+ gettext_noop("Stored type"));
+ appendPQExpBuffer(&buf,
+ "FROM 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_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 (opclass_pattern)
+ processSQLNamePattern(pset.db, &buf, opclass_pattern, have_where, false,
+ "n.nspname", "c.opcname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1,2,3;");
+ 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 a4cc5efae0..24a596598f 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -111,4 +111,21 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \dAfp */
+extern bool listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern,
+ bool verbose);
+
+/* \dAfo */
+extern bool listFamilyClassOperators(const char *accessMethod_pattern,
+ const char *family_pattern, bool verbose);
+
+/* \dAp */
+extern bool describeAccessMethodProperties(const char *pattern);
+
+/* \dAoc */
+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 586aebddd3..cdc8b0d210 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -224,6 +224,10 @@ slashUsage(unsigned short int pager)
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, _(" \\dAfo [AMPTRN [OPFPTRN]] list operators of family related to access method\n"));
+ fprintf(output, _(" \\dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related to access method\n"));
+ fprintf(output, _(" \\dAoc[+][AMPTRN [OPCPTRN]] list operator classes of index access methods\n"));
+ fprintf(output, _(" \\dAp [PATTERN] list access methods with properties\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 9dbd555166..7e9f6a7d11 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -460,6 +460,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",
@@ -1329,7 +1346,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", "\\dAfo", "\\dAfp", "\\dAoc",
+ "\\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",
@@ -3414,6 +3432,10 @@ 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("\\dAf*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+ else if (TailMatchesCS("\\dAf*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, 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 3818cfea7e..1b24a0a307 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -3243,3 +3243,94 @@ 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
+List of access methods
+ Name | Type
+--------+-------
+ brin | Index
+ btree | Index
+ gin | Index
+ gist | Index
+ hash | Index
+ spgist | Index
+(6 rows)
+
+\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
+ spgist | Index | spghandler | SP-GiST index access method
+(6 rows)
+
+\dA gin
+List of access methods
+ Name | Type
+------+-------
+ gin | Index
+(1 row)
+
+\dA+ gin
+ List of access methods
+ Name | Type | Handler | Description
+------+-------+------------+-------------------------
+ gin | Index | ginhandler | GIN index access method
+(1 row)
+
+\dAp gin
+ Access method properties
+ AM | Can order | Support unique indexes | Support indexes with multiple columns | Support exclusion constraints | Can include non-key columns
+-----+-----------+------------------------+---------------------------------------+-------------------------------+-----------------------------
+ gin | f | f | t | f | f
+(1 row)
+
+\dAfo brin uuid_minmax_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | 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)
+
+\dAfo * pg_catalog.jsonb_path_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Left type | Right type | Operator
+-----+-----------------+----------------+-----------+------------+----------
+ gin | pg_catalog | jsonb_path_ops | jsonb | jsonb | @>
+(1 row)
+
+\dAfp 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)
+
+\dAfp * 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)
+
+\dAoc brin pg*.oid*
+ Index access method operator classes
+ Access method | Schema | Name | Family | Indexed type
+---------------+------------+----------------+----------------+--------------
+ brin | pg_catalog | oid_minmax_ops | oid_minmax_ops | oid
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9bb8d..ffe0803767 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -688,3 +688,15 @@ 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
+\dA+
+\dA gin
+\dA+ gin
+\dAp gin
+\dAfo brin uuid_minmax_ops
+\dAfo * pg_catalog.jsonb_path_ops
+\dAfp brin uuid_minmax_ops
+\dAfp * pg_catalog.uuid_ops
+\dAoc brin pg*.oid*
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index bee8987c85..7294b1253c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1428,6 +1428,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 7c35aed018..a54dc27098 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -806,6 +806,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 4947fb69fa..dbc1cf7260 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -18,6 +18,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"
@@ -44,6 +45,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);
/*----------------
@@ -5913,3 +5917,264 @@ 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 ?
+ " pg_catalog.pg_index_has_property(c.oid, 'clusterable') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'index_scan') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'bitmap_scan') AS \"%s\",\n"
+ " pg_catalog.pg_index_has_property(c.oid, 'backward_scan') AS \"%s\"\n"
+ :
+ " am.amclusterable AS \"%s\",\n"
+ " am.amgettuple <> 0 AS \"%s\",\n"
+ " am.amgetbitmap <> 0 AS \"%s\",\n"
+ " am.amcanbackward AS \"%s\"\n",
+ 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 pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'asc')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " CASE\n"
+ " WHEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') = true \n"
+ " THEN pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'nulls_first')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'orderable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'distance_orderable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'returnable') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_array') AS \"%s\",\n"
+ " pg_catalog.pg_index_column_has_property(c.oid, a.attnum, 'search_nulls') AS \"%s\"\n",
+ 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 (i.indoption[a.attnum - 1] & %d) = 0 ELSE NULL END AS \"%s\",\n" /* INDOPTION_DESC */
+ " CASE WHEN am.amcanorder THEN (i.indoption[a.attnum - 1] & %d) <> 0 ELSE NULL END AS \"%s\",\n" /* INDOPTION_NULLS_FIRST */
+ " am.amcanorder AS \"%s\",\n"
+ " am.amcanorderbyop AS \"%s\",\n"
+ " am.amsearcharray AS \"%s\",\n"
+ " am.amsearchnulls AS \"%s\"\n",
+ INDOPTION_DESC,
+ 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 24a596598f..619514c40d 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 cdc8b0d210..af4a298ffb 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -246,6 +246,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 7e9f6a7d11..5ea9436b4d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1349,8 +1349,8 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\dAp", "\\dAfo", "\\dAfp", "\\dAoc",
"\\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/indexing.out b/src/test/regress/expected/indexing.out
index 3e61f50e7c..c3392d1d37 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1404,3 +1404,20 @@ insert into covidxpart values (4, 1);
insert into covidxpart values (4, 1);
ERROR: duplicate key value violates unique constraint "covidxpart4_a_b_idx"
DETAIL: Key (a)=(4) already exists.
+-- Test psql command for displaying information about indexes.
+\dip brinidx
+ Index properties
+ Schema | Name | Access method | Clusterable | Index scan | Bitmap scan | Backward scan
+--------+---------+---------------+-------------+------------+-------------+---------------
+ public | brinidx | brin | f | f | t | f
+(1 row)
+
+\dicp botharrayidx
+ Index public.botharrayidx
+ Column name | Expr | Opclass | ASC | Nulls first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+-------------+------+-----------+-----+-------------+-----------+--------------------+------------+--------------+--------------
+ i | i | array_ops | | | f | f | f | f | f
+ t | t | array_ops | | | f | f | f | f | f
+Table: array_index_op_test
+Access method: gin
+
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 400b7eb7ba..bf875d982a 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -753,3 +753,7 @@ create unique index on covidxpart4 (a);
alter table covidxpart attach partition covidxpart4 for values in (4);
insert into covidxpart values (4, 1);
insert into covidxpart values (4, 1);
+
+-- Test psql command for displaying information about indexes.
+\dip brinidx
+\dicp botharrayidx
