public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nikita Glukhov <[email protected]>
Subject: [PATCH 1/2] Add psql AM info commands
Date: Mon, 15 Jul 2019 15:52:49 +0300
---
doc/src/sgml/catalogs.sgml | 8 +-
doc/src/sgml/ref/psql-ref.sgml | 81 ++++++-
src/bin/psql/command.c | 20 +-
src/bin/psql/describe.c | 430 ++++++++++++++++++++++++++++++++++---
src/bin/psql/describe.h | 19 +-
src/bin/psql/help.c | 6 +-
src/bin/psql/tab-complete.c | 16 +-
src/test/regress/expected/psql.out | 141 ++++++++++++
src/test/regress/sql/psql.sql | 15 ++
9 files changed, 696 insertions(+), 40 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 68ad507..ec79c11 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -681,7 +681,7 @@
search and ordering purposes.)
</para>
- <table>
+ <table id="catalog-pg-amop-table">
<title><structname>pg_amop</structname> Columns</title>
<tgroup cols="4">
@@ -824,7 +824,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">
@@ -4467,7 +4467,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">
@@ -4729,7 +4729,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 7789fc6..e690c4d 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1222,11 +1222,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 c0a7a55..8cfcc9b 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -722,7 +722,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 (strncmp(cmd, "dA+", 3) == 0)
+ success = describeAccessMethodProperties(pattern);
+ else
+ success = listAccessMethods(pattern);
+
+ 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 8b4cd53..b819b3e 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"
@@ -148,48 +149,28 @@ 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(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
- static const bool translate_columns[] = {false, true, false, false};
-
- if (pset.sversion < 90600)
- {
- char sverbuf[32];
-
- pg_log_error("The server (version %s) does not support access methods.",
- formatPGVersionNumber(pset.sversion, false,
- sverbuf, sizeof(sverbuf)));
- return true;
- }
+ static const bool translate_columns[] = {false, true};
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
"SELECT amname AS \"%s\",\n"
- " CASE amtype"
+ " CASE %s"
" WHEN 'i' THEN '%s'"
" WHEN 't' THEN '%s'"
- " END AS \"%s\"",
+ " END AS \"%s\"\n"
+ "FROM pg_catalog.pg_am\n",
gettext_noop("Name"),
+ pset.sversion >= 90600 ? "amtype" : "'i'",
gettext_noop("Index"),
gettext_noop("Table"),
gettext_noop("Type"));
- if (verbose)
- {
- appendPQExpBuffer(&buf,
- ",\n amhandler AS \"%s\",\n"
- " pg_catalog.obj_description(oid, 'pg_am') AS \"%s\"",
- gettext_noop("Handler"),
- gettext_noop("Description"));
- }
-
- appendPQExpBufferStr(&buf,
- "\nFROM pg_catalog.pg_am\n");
-
processSQLNamePattern(pset.db, &buf, pattern, false, false,
NULL, "amname", NULL,
NULL);
@@ -207,7 +188,10 @@ describeAccessMethods(const char *pattern, bool verbose)
myopt.translate_columns = translate_columns;
myopt.n_translate_columns = lengthof(translate_columns);
- printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ if (PQntuples(res) > 0)
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ else
+ pg_log_error("Did not find any AM named \"%s\".", pattern);
PQclear(res);
return true;
@@ -5985,3 +5969,395 @@ 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;
+ bool have_where = false;
+ printQueryOpt myopt = pset.popt;
+ static const bool translate_columns_i[] = {false, true, true, true, true, true, false, true};
+ static const bool translate_columns_t[] = {false, false, true};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " a.amname AS \"%1$s\",\n"
+ " CASE WHEN %4$s THEN '%2$s' ELSE '%3$s' END AS \"%5$s\",\n"
+ " CASE WHEN %6$s THEN '%2$s' ELSE '%3$s' END AS \"%7$s\",\n"
+ " CASE WHEN %8$s THEN '%2$s' ELSE '%3$s' END AS \"%9$s\",\n"
+ " CASE WHEN %10$s THEN '%2$s' ELSE '%3$s' END AS \"%11$s\",\n"
+ " CASE WHEN %12$s THEN '%2$s' ELSE '%3$s' END AS \"%13$s\",\n"
+ " %14$s AS \"%15$s\",\n"
+ " pg_catalog.obj_description(a.oid, 'pg_am') AS \"%16$s\"\n"
+ "FROM pg_catalog.pg_am a\n",
+ gettext_noop("AM"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+
+ pset.sversion >= 90600
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_order')"
+ : pset.sversion >= 80300
+ ? "a.amcanorder"
+ : "am.orderstrategy <> 0",
+ gettext_noop("Ordering"),
+
+ pset.sversion >= 90600
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_unique')"
+ : "a.amcanunique",
+ gettext_noop("Unique indexes"),
+
+ pset.sversion >= 90600
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_multi_col')"
+ : "a.amcanmulticol",
+ gettext_noop("Multicol indexes"),
+
+ pset.sversion >= 90600
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_exclude')"
+ : pset.sversion >= 90000
+ ? "a.amgettuple <> 0"
+ : "FALSE",
+ gettext_noop("Exclusion constraints"),
+
+ pset.sversion >= 110000
+ ? "pg_catalog.pg_indexam_has_property(a.oid, 'can_include')"
+ : "FALSE",
+ gettext_noop("Include non-key columns"),
+
+ pset.sversion >= 90600 ? "a.amhandler" : "NULL",
+ gettext_noop("Handler"),
+ gettext_noop("Description"));
+
+ if (pset.sversion >= 120000)
+ {
+ appendPQExpBufferStr(&buf, " WHERE a.amtype = 'i'\n");
+ have_where = true;
+ }
+
+ processSQLNamePattern(pset.db, &buf, pattern, have_where, 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);
+
+ if (pset.sversion >= 120000)
+ {
+ /* Table AM */
+ initPQExpBuffer(&buf);
+ printfPQExpBuffer(&buf,
+ "SELECT a.amname 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("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)
+ pg_log_error("Did not find any AM named \"%s\".", pattern);
+
+ return true;
+}
+
+/*
+ * \dAo
+ * Lists operators associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listFamilyClassOperators(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, true, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', nsf.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " format ('%%s (%%s, %%s)',\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_operator_is_visible(op.oid) \n"
+ " THEN op.oprname::pg_catalog.text \n"
+ " ELSE o.amopopr::pg_catalog.regoper::pg_catalog.text \n"
+ " END,\n"
+ " pg_catalog.format_type(o.amoplefttype, NULL),\n"
+ " pg_catalog.format_type(o.amoprighttype, NULL)\n"
+ " ) AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Opfamily Name"),
+ gettext_noop("Operator"));
+
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", o.amopstrategy AS \"%s\",\n"
+ " CASE o.amoppurpose\n"
+ " WHEN 'o' THEN '%s'\n"
+ " WHEN 's' THEN '%s'\n"
+ " END AS \"%s\",\n"
+ " ofs.opfname AS \"%s\"\n",
+ gettext_noop("Strategy"),
+ gettext_noop("ordering"),
+ gettext_noop("search"),
+ gettext_noop("Purpose"),
+ gettext_noop("Sort opfamily"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amop o\n"
+ " LEFT JOIN pg_catalog.pg_operator op ON op.oid = o.amopopr\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = o.amopfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod AND am.oid = o.amopmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace nsf ON of.opfnamespace = nsf.oid\n");
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily ofs ON ofs.oid = o.amopsortfamily\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "nsf.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, o.amopstrategy, 3;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List operators of family related to access method");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAp
+ * Lists procedures associated with access method operator families.
+ *
+ * Takes an optional regexp to select particular access methods
+ * and operator families
+ */
+bool
+listOperatorFamilyProcedures(const char *access_method_pattern,
+ const char *family_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT\n"
+ " am.amname AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ns.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amproclefttype, NULL) AS \"%s\",\n"
+ " pg_catalog.format_type(ap.amprocrighttype, NULL) AS \"%s\",\n"
+ " ap.amprocnum AS \"%s\"\n",
+ gettext_noop("AM"),
+ gettext_noop("Operator family"),
+ gettext_noop("Left arg type"),
+ gettext_noop("Right arg type"),
+ gettext_noop("Support function"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ", ap.amproc::pg_catalog.regproc::pg_catalog.text AS \"%s\"\n",
+ gettext_noop("Proc name"));
+ appendPQExpBuffer(&buf,
+ "FROM pg_catalog.pg_amproc ap\n"
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = ap.amprocfamily\n"
+ " LEFT JOIN pg_catalog.pg_am am ON am.oid = of.opfmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace ns ON of.opfnamespace = ns.oid\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname",
+ NULL, NULL);
+ if (family_pattern)
+ processSQLNamePattern(pset.db, &buf, family_pattern, have_where, false,
+ "ns.nspname", "of.opfname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf,
+ "ORDER BY 1, 2, 3, 4, 5;");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("List of operator family procedures");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
+
+/*
+ * \dAc
+ * List index access method operator classes.
+ * Takes an optional regexp to select particular access method and operator class.
+ */
+bool
+describeAccessMethodOperatorClasses(const char *access_method_pattern,
+ const char *type_pattern, bool verbose)
+{
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool have_where = false;
+ static const bool translate_columns[] = {false, false, false, false, false,
+ false, false, false};
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT DISTINCT"
+ " am.amname AS \"%s\",\n"
+ " c.opcintype::pg_catalog.regtype AS \"%s\",\n"
+ " (CASE WHEN c.opckeytype <> 0 AND c.opckeytype <> c.opcintype\n"
+ " THEN c.opckeytype\n"
+ " ELSE NULL -- c.opcintype\n"
+ " END)::pg_catalog.regtype AS \"%s\",\n"
+ " CASE\n"
+ " WHEN pg_catalog.pg_opclass_is_visible(c.oid)\n"
+ " THEN format('%%I', c.opcname)\n"
+ " ELSE format('%%I.%%I', n.nspname, c.opcname)\n"
+ " END AS \"%s\",\n"
+ " (CASE WHEN c.opcdefault\n"
+ " THEN '%s'\n"
+ " ELSE '%s'\n"
+ " END) AS \"%s\"",
+ gettext_noop("AM"),
+ gettext_noop("Input type"),
+ gettext_noop("Storage type"),
+ gettext_noop("Operator class"),
+ gettext_noop("yes"),
+ gettext_noop("no"),
+ gettext_noop("Default?"));
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ ",\n CASE\n"
+ " WHEN pg_catalog.pg_opfamily_is_visible(of.oid)\n"
+ " THEN format('%%I', of.opfname)\n"
+ " ELSE format('%%I.%%I', ofn.nspname, of.opfname)\n"
+ " END AS \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(c.opcowner) AS \"%s\"\n",
+ gettext_noop("Operator family"),
+ gettext_noop("Owner"));
+ appendPQExpBuffer(&buf,
+ "\nFROM pg_catalog.pg_opclass c\n"
+ " LEFT JOIN pg_catalog.pg_am am on am.oid = c.opcmethod\n"
+ " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.opcnamespace\n"
+ " LEFT JOIN pg_catalog.pg_type t1 ON t1.oid = c.opcintype\n"
+ );
+ if (verbose)
+ appendPQExpBuffer(&buf,
+ " LEFT JOIN pg_catalog.pg_opfamily of ON of.oid = c.opcfamily\n"
+ " LEFT JOIN pg_catalog.pg_namespace ofn ON ofn.oid = of.opfnamespace\n");
+
+ if (access_method_pattern)
+ have_where = processSQLNamePattern(pset.db, &buf, access_method_pattern,
+ false, false, NULL, "am.amname", NULL, NULL);
+ if (type_pattern)
+ processSQLNamePattern(pset.db, &buf, type_pattern, have_where, false,
+ NULL, "t1.typname", NULL, NULL);
+
+ appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _("Index access method operator classes");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+ return true;
+}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 17736c3..6622534 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -13,7 +13,10 @@
extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
/* \dA */
-extern bool describeAccessMethods(const char *pattern, bool verbose);
+extern bool listAccessMethods(const char *pattern);
+
+/* \dA+ */
+extern bool describeAccessMethodProperties(const char *pattern);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
@@ -114,4 +117,18 @@ bool describePublications(const char *pattern);
/* \dRs */
bool describeSubscriptions(const char *pattern, bool verbose);
+/* \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 d9b982d..dc591c3 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -226,7 +226,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 3f7001f..5d14a93 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -495,6 +495,13 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
.result = "pg_catalog.quote_ident(c.relname)",
};
+static const SchemaQuery Query_for_list_of_operator_families = {
+ .catname = "pg_catalog.pg_opfamily c",
+ .viscondition = "pg_catalog.pg_opfamily_is_visible(c.oid)",
+ .namespace = "c.opfnamespace",
+ .result = "pg_catalog.quote_ident(c.opfname)",
+};
+
/* Relations supporting INSERT, UPDATE or DELETE */
static const SchemaQuery Query_for_list_of_updatables = {
.catname = "pg_catalog.pg_class c",
@@ -1417,7 +1424,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", "\\dP", "\\dPi", "\\dPt",
@@ -3590,6 +3598,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 9021c80..7738ee3 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -4748,3 +4748,144 @@ drop schema testpart;
set search_path to default;
set role to default;
drop role regress_partitioning_role;
+-- check printing info about access methods
+\dA
+List of access methods
+ Name | Type
+--------+-------
+ brin | Index
+ btree | Index
+ gin | Index
+ gist | Index
+ hash | Index
+ heap | Table
+ heap2 | Table
+ spgist | Index
+(8 rows)
+
+\dA *
+List of access methods
+ Name | Type
+--------+-------
+ brin | Index
+ btree | Index
+ gin | Index
+ gist | Index
+ hash | Index
+ heap | Table
+ heap2 | Table
+ spgist | Index
+(8 rows)
+
+\dA h*
+List of access methods
+ Name | Type
+-------+-------
+ hash | Index
+ heap | Table
+ heap2 | Table
+(3 rows)
+
+\dA foo
+Did not find any AM named "foo".
+\dA+
+ Index access method properties
+ AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns | Handler | Description
+--------+----------+----------------+------------------+-----------------------+-------------------------+-------------+----------------------------------------
+ brin | no | no | yes | no | no | brinhandler | block range index (BRIN) access method
+ btree | yes | yes | yes | yes | yes | bthandler | b-tree index access method
+ gin | no | no | yes | no | no | ginhandler | GIN index access method
+ gist | no | no | yes | yes | yes | gisthandler | GiST index access method
+ hash | no | no | no | yes | no | hashhandler | hash index access method
+ spgist | no | no | no | yes | no | spghandler | SP-GiST index access method
+(6 rows)
+
+ Table access method properties
+ Name | Handler | Description
+-------+----------------------+--------------------------
+ heap | heap_tableam_handler | heap table access method
+ heap2 | heap_tableam_handler |
+(2 rows)
+
+\dA+ *
+ Index access method properties
+ AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns | Handler | Description
+--------+----------+----------------+------------------+-----------------------+-------------------------+-------------+----------------------------------------
+ brin | no | no | yes | no | no | brinhandler | block range index (BRIN) access method
+ btree | yes | yes | yes | yes | yes | bthandler | b-tree index access method
+ gin | no | no | yes | no | no | ginhandler | GIN index access method
+ gist | no | no | yes | yes | yes | gisthandler | GiST index access method
+ hash | no | no | no | yes | no | hashhandler | hash index access method
+ spgist | no | no | no | yes | no | spghandler | SP-GiST index access method
+(6 rows)
+
+ Table access method properties
+ Name | Handler | Description
+-------+----------------------+--------------------------
+ heap | heap_tableam_handler | heap table access method
+ heap2 | heap_tableam_handler |
+(2 rows)
+
+\dA+ h*
+ Index access method properties
+ AM | Ordering | Unique indexes | Multicol indexes | Exclusion constraints | Include non-key columns | Handler | Description
+------+----------+----------------+------------------+-----------------------+-------------------------+-------------+--------------------------
+ hash | no | no | no | yes | no | hashhandler | hash index access method
+(1 row)
+
+ Table access method properties
+ Name | Handler | Description
+-------+----------------------+--------------------------
+ heap | heap_tableam_handler | heap table access method
+ heap2 | heap_tableam_handler |
+(2 rows)
+
+\dA+ foo
+Did not find any AM named "foo".
+\dAo brin uuid_minmax_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+------+-----------------+-----------------
+ brin | uuid_minmax_ops | < (uuid, uuid)
+ brin | uuid_minmax_ops | <= (uuid, uuid)
+ brin | uuid_minmax_ops | = (uuid, uuid)
+ brin | uuid_minmax_ops | >= (uuid, uuid)
+ brin | uuid_minmax_ops | > (uuid, uuid)
+(5 rows)
+
+\dAo * pg_catalog.jsonb_path_ops
+List operators of family related to access method
+ AM | Opfamily Name | Operator
+-----+----------------+----------------------
+ gin | jsonb_path_ops | @> (jsonb, jsonb)
+ gin | jsonb_path_ops | @? (jsonb, jsonpath)
+ gin | jsonb_path_ops | @@ (jsonb, jsonpath)
+(3 rows)
+
+\dAp brin uuid_minmax_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+------+-----------------+---------------+----------------+------------------
+ brin | uuid_minmax_ops | uuid | uuid | 1
+ brin | uuid_minmax_ops | uuid | uuid | 2
+ brin | uuid_minmax_ops | uuid | uuid | 3
+ brin | uuid_minmax_ops | uuid | uuid | 4
+(4 rows)
+
+\dAp * pg_catalog.uuid_ops
+ List of operator family procedures
+ AM | Operator family | Left arg type | Right arg type | Support function
+-------+-----------------+---------------+----------------+------------------
+ btree | uuid_ops | uuid | uuid | 1
+ btree | uuid_ops | uuid | uuid | 2
+ hash | uuid_ops | uuid | uuid | 1
+ hash | uuid_ops | uuid | uuid | 2
+(4 rows)
+
+\dAc brin pg*.oid*
+ Index access method operator classes
+ AM | Input type | Storage type | Operator class | Default?
+------+------------+--------------+----------------+----------
+ brin | oid | | oid_minmax_ops | yes
+(1 row)
+
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index cefe41b..650a540 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1131,3 +1131,18 @@ set search_path to default;
set role to default;
drop role regress_partitioning_role;
+
+-- check printing info about access methods
+\dA
+\dA *
+\dA h*
+\dA foo
+\dA+
+\dA+ *
+\dA+ h*
+\dA+ foo
+\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*
--
2.7.4
--------------19B35D84A6D9E14B89902F95
Content-Type: text/x-patch;
name="0002-Add-psql-index-info-commands-v08.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="0002-Add-psql-index-info-commands-v08.patch"
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [PATCH 1/2] Add psql AM info commands
In-Reply-To: <no-message-id-1882077@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox