Hello!
There are command in psql to list access methods, but there are no fast
way to look detailed info about them. So here a patch with new
commands:
\dAp [PATTERN] list access methods with properties (Table
pg_am)
\dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method. +
prints owner of operator family. (Table pg_opfamily)
\dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related
to access method (Table pg_amproc)
\dAfo [AMPTRN [OPFPTRN]] list operators of family related to access
method (Table pg_amop)
\dAoc[+] [AMPTRN [OPCPTRN]] list operator classes of index access
methods. + prints owner of operator class. (Table pg_opclass)
\dip[S] [PATTERN] list indexes with properties (Table
pg_class)
\dicp[S] [IDXNAME [COLNAME]] show index column properties (Table
pg_class)
You can display information only on the access methods, specified by a
template. You can also filter operator classes, operator families, or
the name of the indexed column.
I also have a question about testing commands \dAf+ and \dAoc+: is it
good idea to test them by changing an owner of one operator family or
class to created new one, checking the output, and restoring the owner
back? Or we should create a new opclass or opfamily with proper owner.
Or maybe it is not necesary to test these commands?
Best regards,
Sergey Cherkashin
s.cherkas...@postgrespro.ru
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 3ed9021..b699548 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 procedure belonging to an operator family.
</para>
- <table>
+ <table id="catalog-pg-amproc-table">
<title><structname>pg_amproc</structname> Columns</title>
<tgroup cols="4">
@@ -4458,7 +4458,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator classes are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opclass-table">
<title><structname>pg_opclass</structname> Columns</title>
<tgroup cols="4">
@@ -4720,7 +4720,7 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
Operator families are described at length in <xref linkend="xindex"/>.
</para>
- <table>
+ <table id="catalog-pg-opfamily-table">
<title><structname>pg_opfamily</structname> Columns</title>
<tgroup cols="4">
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 10b9795..b5d2095 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1203,6 +1203,95 @@ testdb=>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <literal>\dAf
+ [ <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 operator families (<xref linkend="catalog-pg-opfamily-table"/>). If <replaceable class="parameter">access-method-pattern</replaceable> is specified, only
+ families whose access method name matches the pattern are shown.
+ If <replaceable class="parameter">operator-family-pattern</replaceable> is specified, only
+ opereator families associated with whose name matches the pattern are shown.
+ If <literal>+</literal> is appended to the command name, each operator
+ family is listed with it's owner.
+ </para>
+ </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
+ opereators 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"/>) accociated 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>\dAop
+ [<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>
@@ -1351,6 +1440,35 @@ 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">index-name-pattern</replaceable></link>
+ [ <link linkend="app-psql-patterns"><replaceable class="parameter">column-name-pattern</replaceable></link> ]]
+ </literal>
+ </term>
+
+ <listitem>
+ <para>
+ Shows index column properties listed in <xref
+ linkend="functions-info-index-column-props"/>. If <replaceable
+ class="parameter">column_name</replaceable> is specified, only column
+ with such name is 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 4c85f43..2d21911 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -723,7 +723,22 @@ 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 = 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);
+ else if (strncmp(cmd, "dAfp", 4) == 0)
+ success = listOperatorFamilyProcedures(pattern, pattern2);
+ else if (strncmp(cmd, "dAf", 3) == 0)
+ success = listAccessMethodOperatorFamilies(pattern, pattern2, show_verbose);
+ else if (strncmp(cmd, "dAoc", 4) == 0)
+ success = describeAccessMethodOperatorClasses(pattern, pattern2, show_verbose);
+ else
+ success = describeAccessMethods(pattern, show_verbose);
+ free(pattern2);
+ }
break;
case 'a':
success = describeAggregates(pattern, show_verbose, show_system);
@@ -792,6 +807,23 @@ 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)
+ {
+ char *column_pattern = NULL;
+
+ if (pattern)
+ column_pattern = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, true);
+
+ success = describeIndexColumnProperties(pattern, column_pattern, show_system);
+ free(column_pattern);
+ 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 e5b3c1e..735b87f 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5501,3 +5501,484 @@ printACLColumn(PQExpBuffer buf, const char *colname)
"pg_catalog.array_to_string(%s, '\\n') AS \"%s\"",
colname, gettext_noop("Access privileges"));
}
+
+/*
+ * \dip
+ * Takes an optional regexp to select particular index. Describes index proerties
+ */
+bool
+describeIndexProperties(const char *pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ /* what for ? */
+ 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"
+ " pg_index_has_property(c.relname::regclass, 'clusterable') as \"%s\",\n"
+ " pg_index_has_property(c.relname::regclass, 'index_scan') as \"%s\",\n"
+ " pg_index_has_property(c.relname::regclass, 'bitmap_scan') as \"%s\",\n"
+ " pg_index_has_property(c.relname::regclass, 'backward_scan') as \"%s\"\n"
+ "FROM pg_class c\n"
+ " LEFT JOIN pg_namespace n ON n.oid = c.relnamespace\n"
+ " LEFT JOIN pg_am am ON am.oid = c.relam\n"
+ "WHERE c.relkind='i'\n"
+ " AND n.nspname !~ 'pg_toast'\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Access method"),
+ gettext_noop("Clusterable"),
+ gettext_noop("Index scan"),
+ gettext_noop("Bitmap scan"),
+ gettext_noop("Backward scan"));
+
+ 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;
+}
+
+/* \dAp
+ * 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, 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,
+ "SELECT a.amname AS \"%s\",\n"
+ " pg_indexam_has_property(a.oid, 'can_order') AS \"%s\",\n"
+ " pg_indexam_has_property(a.oid, 'can_unique') AS \"%s\",\n"
+ " pg_indexam_has_property(a.oid, 'can_multi_col') AS \"%s\",\n"
+ " pg_indexam_has_property(a.oid, 'can_exclude') AS \"%s\"",
+ gettext_noop("AM Name"),
+ gettext_noop("Can order"),
+ gettext_noop("Can unique"),
+ gettext_noop("Can multi col"),
+ gettext_noop("Can exclude"));
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM 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;
+}
+
+/* \dAf */
+bool
+listAccessMethodOperatorFamilies(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};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " am.amname AS \"%s\",\n"
+ " ns.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\"\n",
+ gettext_noop("Access method"),
+ gettext_noop("Operator family schema"),
+ gettext_noop("Operator family name"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", usr.usename AS \"%s\"\n",
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ " FROM pg_opfamily of\n"
+ " LEFT JOIN pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_namespace ns ON of.opfnamespace = ns.oid\n");
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_user usr ON of.opfowner = usr.usesysid\n"
+ );
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern, false, false,
+ "am.amname", "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;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator families");
+ 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 */
+bool
+listFamilyClassOperators(const char *access_method_pattern, const char *family_pattern)
+{
+ 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, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " nsf.nspname AS \"%s\",\n"
+ " of.opfname AS \"%s\",\n"
+ " o.amopstrategy AS \"%s\",\n"
+ " CASE WHEN pg_operator_is_visible(op.oid) \n"
+ " THEN op.oprname::text \n"
+ " ELSE o.amopopr::pg_catalog.regoper::text \n"
+ " END AS \"%s\",\n"
+ " format_type(o.amoplefttype , NULL) AS \"%s\",\n"
+ " format_type(o.amoprighttype , NULL) AS \"%s\",\n"
+ " CASE o.amoppurpose\n"
+ " WHEN 'o' THEN 'ordering'\n"
+ " WHEN 's' THEN 'search'\n"
+ " END AS \"%s\",\n"
+ " ofs.opfname AS \"%s\"\n"
+ "FROM pg_amop o\n"
+ " LEFT JOIN pg_operator op ON op.oid = o.amopopr\n"
+ " LEFT JOIN pg_opfamily of ON of.oid = o.amopfamily\n"
+ " LEFT JOIN pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n"
+ " LEFT JOIN pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
+ " LEFT JOIN pg_namespace nsf ON of.opfnamespace = nsf.oid\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Schema"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Strategy"),
+ gettext_noop("Operator"),
+ gettext_noop("Left type"),
+ gettext_noop("Right type"),
+ gettext_noop("Purpose"),
+ gettext_noop("Sort family"));
+
+ 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;");
+
+ 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 */
+bool
+listOperatorFamilyProcedures(const char *access_method_pattern, const char *family_pattern)
+{
+ 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"
+ " ap.amprocnum AS \"%s\",\n"
+ " ap.amproc::pg_catalog.regproc AS \"%s\",\n"
+ " format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
+ " format_type(ap.amprocrighttype, NULL) AS \"%s\"\n"
+ "FROM pg_amproc ap\n"
+ " LEFT JOIN pg_opfamily of ON of.oid = ap.amprocfamily\n"
+ " LEFT JOIN pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_namespace ns ON of.opfnamespace = ns.oid\n",
+ gettext_noop("AM"),
+ gettext_noop("Family schema"),
+ gettext_noop("Family name"),
+ gettext_noop("Number"),
+ gettext_noop("Proc name"),
+ gettext_noop("Left"),
+ gettext_noop("Right"));
+
+ 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;");
+
+ 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;
+}
+
+/* \dicp */
+bool
+describeIndexColumnProperties(const char *index_pattern, const char *column_pattern, bool showSystem)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ static const bool translate_columns[] = {false, false, false, false, false, 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"
+ " ip.col_pos AS \"%s\",\n"
+ " CASE\n"
+ " WHEN i.indkey[ip.col_pos - 1] > 0 THEN ic.column_name\n"
+ " WHEN i.indkey[ip.col_pos - 1] = -2 THEN 'oid'\n"
+ " ELSE pg_get_expr(i.indexprs, i.indrelid)\n"
+ " END AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'orderable') = true \n"
+ " THEN pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'asc')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " CASE\n"
+ " WHEN pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'orderable') = true \n"
+ " THEN pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'nulls_first')\n"
+ " ELSE NULL"
+ " END AS \"%s\","
+ " pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'orderable') AS \"%s\",\n"
+ " pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'distance_orderable') AS \"%s\",\n"
+ " pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'returnable') AS \"%s\",\n"
+ " pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'search_array') AS \"%s\",\n"
+ " pg_index_column_has_property(c.relname::regclass, ip.col_pos, 'search_nulls') AS \"%s\"\n"
+ "FROM pg_class c\n"
+ " LEFT JOIN pg_namespace n ON n.oid = relnamespace\n"
+ " LEFT JOIN 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"
+ " LEFT JOIN information_schema.columns ic ON ic.table_name = c2.relname AND ic.ordinal_position = ANY(i.indkey::int[]) \n"
+ " LEFT JOIN LATERAL (SELECT array_position(i.indkey, un) + 1 AS col_pos FROM unnest(i.indkey) un) AS ip ON true\n"
+ "WHERE c.relkind='i'\n"
+ " AND n.nspname !~ '^pg_toast'\n",
+ gettext_noop("Schema"),
+ gettext_noop("Index"),
+ gettext_noop("Access method"),
+ gettext_noop("Column #"),
+ gettext_noop("Expr"),
+ gettext_noop("ASC"),
+ gettext_noop("Null first"),
+ gettext_noop("Orderable"),
+ gettext_noop("Distance orderable"),
+ gettext_noop("Returnable"),
+ gettext_noop("Search array"),
+ gettext_noop("Search nulls"));
+
+ 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,
+ NULL);
+ if (column_pattern)
+ processSQLNamePattern(pset.db, &buf, column_pattern, true, false,
+ NULL, "ic.column_name", NULL,
+ NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index column 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;
+}
+
+/*
+ * \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,
+ " of.opfname AS \"%s\",\n"
+ " c.opcintype::regtype AS \"%s\",\n"
+ " c.opcdefault AS \"%s\",\n"
+ " (CASE c.opckeytype\n"
+ " WHEN 0\n"
+ " THEN c.opcintype\n"
+ " ELSE c.opckeytype\n"
+ " END)::regtype::regtype AS \"%s\"\n"
+ "FROM pg_opclass c\n"
+ " LEFT JOIN pg_am am on am.oid = c.opcmethod\n"
+ " LEFT JOIN pg_namespace n ON n.oid = c.opcnamespace\n"
+ " LEFT JOIN pg_opfamily of ON of.oid = c.opcfamily\n",
+ gettext_noop("Family"),
+ gettext_noop("Indexed type"),
+ gettext_noop("Is default"),
+ gettext_noop("Stored type"));
+
+ 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 a4cc5ef..b145f20 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -111,4 +111,25 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \dAf */
+extern bool listAccessMethodOperatorFamilies(const char *access_method_pattern, const char *family_pattern, bool verbose);
+
+/* \dAfp */
+extern bool listOperatorFamilyProcedures(const char *access_method_pattern, const char *family_pattern);
+
+/* \dAfo */
+extern bool listFamilyClassOperators(const char *accessMethod_pattern, const char *family_pattern);
+
+/* \dAp */
+extern bool describeAccessMethodProperties(const char *pattern);
+
+/* \dip */
+extern bool describeIndexProperties(const char *pattern, bool showSystem);
+
+/* \dicp */
+extern bool describeIndexColumnProperties(const char *indexPattern, const char *columnPattern, bool showSystem);
+
+/* \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 702e742..dbe62e8 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -224,6 +224,11 @@ 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, _(" \\dAf[+] [AMPTRN [OPFPTRN]] list operator families of access method\n"));
+ fprintf(output, _(" \\dAfp [AMPTRN [OPFPTRN]] list procedures of operator family related to access method\n"));
+ fprintf(output, _(" \\dAfo [AMPTRN [OPFPTRN]] list operators of 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"));
@@ -242,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] [IDXNAME [COLNAME]] 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 7bb47ea..1c3c209 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -638,6 +638,23 @@ static const SchemaQuery Query_for_list_of_constraints_with_schema = {
NULL
};
+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 = {
/* min_server_version */
@@ -1618,7 +1635,7 @@ 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", "\\dAf", "\\dAoc", "\\dicp", "\\dip", "\\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",
@@ -3635,6 +3652,10 @@ psql_completion(const char *text, int start, int end)
}
else if (TailMatchesCS1("\\da*"))
COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_aggregates, NULL);
+ else if (TailMatchesCS1("\\dAf*"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
+ else if (TailMatchesCS2("\\dAf*", MatchAny))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_operator_families, NULL);
else if (TailMatchesCS1("\\dA*"))
COMPLETE_WITH_QUERY(Query_for_list_of_access_methods);
else if (TailMatchesCS1("\\db*"))
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 2c2bf44..726b1be 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1337,3 +1337,29 @@ 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 column properties
+ Schema | Index | Access method | Column # | Expr | ASC | Null first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+--------+--------------+---------------+----------+------+-----+------------+-----------+--------------------+------------+--------------+--------------
+ public | botharrayidx | gin | 1 | i | | | f | f | f | f | f
+ public | botharrayidx | gin | 2 | i | | | f | f | f | f | f
+ public | botharrayidx | gin | 1 | t | | | f | f | f | f | f
+ public | botharrayidx | gin | 2 | t | | | f | f | f | f | f
+(4 rows)
+
+\dicp botharrayidx t
+ Index column properties
+ Schema | Index | Access method | Column # | Expr | ASC | Null first | Orderable | Distance orderable | Returnable | Search array | Search nulls
+--------+--------------+---------------+----------+------+-----+------------+-----------+--------------------+------------+--------------+--------------
+ public | botharrayidx | gin | 1 | t | | | f | f | f | f | f
+ public | botharrayidx | gin | 2 | t | | | f | f | f | f | f
+(2 rows)
+
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3818cfe..c02ee4c 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -3243,3 +3243,158 @@ 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 gin
+List of access methods
+ Name | Type
+------+-------
+ gin | Index
+(1 row)
+
+\dA foo
+List of access methods
+ Name | Type
+------+------
+(0 rows)
+
+\dAp gin
+ Access method properties
+ AM Name | Can order | Can unique | Can multi col | Can exclude
+---------+-----------+------------+---------------+-------------
+ gin | f | f | t | f
+(1 row)
+
+\dAp foo
+ Access method properties
+ AM Name | Can order | Can unique | Can multi col | Can exclude
+---------+-----------+------------+---------------+-------------
+(0 rows)
+
+\dAf gin
+ List of operator families
+ Access method | Operator family schema | Operator family name
+---------------+------------------------+----------------------
+ gin | pg_catalog | array_ops
+ gin | pg_catalog | tsvector_ops
+ gin | pg_catalog | jsonb_ops
+ gin | pg_catalog | jsonb_path_ops
+(4 rows)
+
+\dAf foo
+ List of operator families
+ Access method | Operator family schema | Operator family name
+---------------+------------------------+----------------------
+(0 rows)
+
+\dAfo brin uuid_minmax_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Strategy | Operator | Left type | Right type | Purpose | Sort family
+------+-----------------+-----------------+----------+----------+-----------+------------+---------+-------------
+ brin | pg_catalog | uuid_minmax_ops | 1 | < | uuid | uuid | search |
+ brin | pg_catalog | uuid_minmax_ops | 2 | <= | uuid | uuid | search |
+ brin | pg_catalog | uuid_minmax_ops | 3 | = | uuid | uuid | search |
+ brin | pg_catalog | uuid_minmax_ops | 4 | >= | uuid | uuid | search |
+ brin | pg_catalog | uuid_minmax_ops | 5 | > | uuid | uuid | search |
+(5 rows)
+
+\dAfo brin bar
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Strategy | Operator | Left type | Right type | Purpose | Sort family
+----+-----------------+---------------+----------+----------+-----------+------------+---------+-------------
+(0 rows)
+
+\dAfo foo bar
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Strategy | Operator | Left type | Right type | Purpose | Sort family
+----+-----------------+---------------+----------+----------+-----------+------------+---------+-------------
+(0 rows)
+
+\dAfo * pg_catalog.jsonb_path_ops
+ List operators of family related to access method
+ AM | Opfamily Schema | Opfamily Name | Strategy | Operator | Left type | Right type | Purpose | Sort family
+-----+-----------------+----------------+----------+----------+-----------+------------+---------+-------------
+ gin | pg_catalog | jsonb_path_ops | 7 | @> | jsonb | jsonb | search |
+(1 row)
+
+\dAfp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Number | Proc name | Left | Right
+------+---------------+-----------------+--------+------------------------+------+-------
+ brin | pg_catalog | uuid_minmax_ops | 1 | brin_minmax_opcinfo | uuid | uuid
+ brin | pg_catalog | uuid_minmax_ops | 2 | brin_minmax_add_value | uuid | uuid
+ brin | pg_catalog | uuid_minmax_ops | 3 | brin_minmax_consistent | uuid | uuid
+ brin | pg_catalog | uuid_minmax_ops | 4 | brin_minmax_union | uuid | uuid
+(4 rows)
+
+\dAfp brin bar
+ List of operator family procedures
+ AM | Family schema | Family name | Number | Proc name | Left | Right
+----+---------------+-------------+--------+-----------+------+-------
+(0 rows)
+
+\dAfp foo bar
+ List of operator family procedures
+ AM | Family schema | Family name | Number | Proc name | Left | Right
+----+---------------+-------------+--------+-----------+------+-------
+(0 rows)
+
+\dAfp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Family schema | Family name | Number | Proc name | Left | Right
+-------+---------------+-------------+--------+--------------------+------+-------
+ btree | pg_catalog | uuid_ops | 1 | uuid_cmp | uuid | uuid
+ btree | pg_catalog | uuid_ops | 2 | uuid_sortsupport | uuid | uuid
+ hash | pg_catalog | uuid_ops | 1 | uuid_hash | uuid | uuid
+ hash | pg_catalog | uuid_ops | 2 | uuid_hash_extended | uuid | uuid
+(4 rows)
+
+\dAoc brin pg*.oid*
+ Index access method operator classes
+ Access method | Schema | Name | Family | Indexed type | Is default | Stored type
+---------------+------------+----------------+----------------+--------------+------------+-------------
+ brin | pg_catalog | oid_minmax_ops | oid_minmax_ops | oid | t | oid
+(1 row)
+
+\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 | Handler | Description
+------+-------+------------+-------------------------
+ gin | Index | ginhandler | GIN index access method
+(1 row)
+
+\dA+ foo
+ List of access methods
+ Name | Type | Handler | Description
+------+------+---------+-------------
+(0 rows)
+
+\dAf+ foo
+ List of operator families
+ Access method | Operator family schema | Operator family name | Owner
+---------------+------------------------+----------------------+-------
+(0 rows)
+
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 29333b3..f8a7518 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -720,3 +720,8 @@ 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
+\dicp botharrayidx t
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9b..ec5a673 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -688,3 +688,26 @@ 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 gin
+\dA foo
+\dAp gin
+\dAp foo
+\dAf gin
+\dAf foo
+\dAfo brin uuid_minmax_ops
+\dAfo brin bar
+\dAfo foo bar
+\dAfo * pg_catalog.jsonb_path_ops
+\dAfp brin uuid_minmax_ops
+\dAfp brin bar
+\dAfp foo bar
+\dAfp * pg_catalog.uuid_ops
+\dAoc brin pg*.oid*
+
+\dA+
+\dA+ gin
+\dA+ foo
+\dAf+ foo
