> Sorry an id was missing in psql documentation, thus CI was failing.
Combining patches into one, as otherwise CI does not combine them.
Best Regards
Sadeq Dousti
From ad81b71fe67174aebd7f6c02a82aa0bbbbab88ff Mon Sep 17 00:00:00 2001
From: Sadeq Dousti <3616518+msdousti@users.noreply.github.com>
Date: Sat, 22 Feb 2025 23:16:44 +0100
Subject: [PATCH v2] psql \dh: list high-level tables and indexes
---
doc/src/sgml/ref/psql-ref.sgml | 22 ++++
src/bin/psql/command.c | 18 +++
src/bin/psql/describe.c | 196 +++++++++++++++++++++++++++++
src/bin/psql/describe.h | 3 +
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.in.c | 43 ++++++-
src/test/regress/expected/psql.out | 59 +++++++++
src/test/regress/sql/psql.sql | 13 ++
8 files changed, 354 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 3edbd65e46c..17b55b47d50 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1871,6 +1871,28 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-dh">
+ <term><literal>\dh[it+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
+ <listitem>
+ <para>
+ Lists high-level (root) relations. This means relations that are
+ either not partitioned, or partitioned and have no parent.
+ If <replaceable class="parameter">pattern</replaceable>
+ is specified, only entries whose name matches the pattern are listed.
+ The modifiers <literal>t</literal> (tables) and <literal>i</literal>
+ (indexes) can be appended to the command, filtering the kind of
+ relations to list. By default, high-level tables and indexes are
+ listed.
+ </para>
+
+ <para>
+ If <literal>+</literal> is appended to the command, the size of the
+ relation (when it's not partitioned), or the sum of the sizes of
+ the table's partitions (when the relation is partitioned) is also
+ displayed, along with the associated description.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry id="app-psql-meta-command-dl-lc">
<term><literal>\dl[x+]</literal></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 0f27bf7a91f..9fff1864010 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1117,6 +1117,24 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
/* no longer distinct from \du */
success = describeRoles(pattern, show_verbose, show_system);
break;
+ case 'h':
+ /* high-level (i.e., root) tables and indexes */
+ {
+ switch (cmd[2])
+ {
+ case '\0':
+ case '+':
+ case 't':
+ case 'i':
+ case 'x':
+ success = listRootTables(&cmd[2], pattern, show_verbose);
+ break;
+ default:
+ status = PSQL_CMD_UNKNOWN;
+ break;
+ }
+ }
+ break;
case 'l':
success = listLargeObjects(show_verbose);
break;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..33270650511 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -4247,6 +4247,202 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
return true;
}
+/*
+ * \dh
+ * Takes an optional regexp to select particular relations
+ *
+ * As with \d, you can specify the kinds of relations you want:
+ *
+ * t for tables
+ * i for indexes
+ *
+ * and you can mix and match them in any order.
+ */
+bool
+listRootTables(const char *reltypes, const char *pattern, bool verbose)
+{
+ bool showTables = strchr(reltypes, 't') != NULL;
+ bool showIndexes = strchr(reltypes, 'i') != NULL;
+ PQExpBufferData buf;
+ PQExpBufferData title;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+ bool translate_columns[] = {false, false, false, false, false, false, false, false};
+ const char *tabletitle;
+ bool mixed_output = false;
+
+ /*
+ * Note: Declarative table partitioning is only supported as of Pg 10.0.
+ */
+ if (pset.sversion < 100000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support declarative table partitioning.",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ /* If no relation kind was selected, show them all */
+ if (!showTables && !showIndexes)
+ showTables = showIndexes = true;
+
+ if (showIndexes && !showTables)
+ tabletitle = _("List of root indexes"); /* \dhi */
+ else if (showTables && !showIndexes)
+ tabletitle = _("List of root tables"); /* \dht */
+ else
+ {
+ /* show all kinds */
+ tabletitle = _("List of root relations");
+ mixed_output = true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf,
+ "SELECT n.nspname as \"%s\",\n"
+ " c.relname as \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(c.relowner) as \"%s\"",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Owner"));
+
+ if (mixed_output)
+ {
+ appendPQExpBuffer(&buf,
+ ",\n CASE c.relkind"
+ " WHEN " CppAsString2(RELKIND_RELATION) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_PARTITIONED_TABLE) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_INDEX) " THEN '%s'"
+ " WHEN " CppAsString2(RELKIND_PARTITIONED_INDEX) " THEN '%s'"
+ " END as \"%s\"",
+ gettext_noop("table"),
+ gettext_noop("partitioned table"),
+ gettext_noop("index"),
+ gettext_noop("partitioned index"),
+ gettext_noop("Type"));
+
+ translate_columns[3] = true;
+ }
+
+ if (showIndexes)
+ appendPQExpBuffer(&buf,
+ ",\n c2.oid::pg_catalog.regclass as \"%s\"",
+ gettext_noop("Table"));
+
+ if (verbose)
+ {
+ /*
+ * Table access methods were introduced in v12, and can be set on
+ * partitioned tables since v17.
+ */
+ appendPQExpBuffer(&buf, ",\n am.amname as \"%s\"",
+ gettext_noop("Access method"));
+
+ /* Sizes of all partitions are considered in this case. */
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.pg_size_pretty(GREATEST(pg_catalog.pg_table_size(c.oid), s.tps)) as \"%s\"",
+ gettext_noop("Total size"));
+
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
+ gettext_noop("Description"));
+ }
+
+ appendPQExpBufferStr(&buf,
+ "\nFROM pg_catalog.pg_class c"
+ "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
+
+ if (showIndexes)
+ appendPQExpBufferStr(&buf,
+ "\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");
+
+ if (verbose)
+ {
+ appendPQExpBufferStr(&buf,
+ "\n LEFT JOIN pg_catalog.pg_am am ON c.relam = am.oid");
+
+ if (pset.sversion < 120000)
+ {
+ appendPQExpBufferStr(&buf,
+ ",\n LATERAL (WITH RECURSIVE d\n"
+ " AS (SELECT inhrelid AS oid, 1 AS level\n"
+ " FROM pg_catalog.pg_inherits\n"
+ " WHERE inhparent = c.oid\n"
+ " UNION ALL\n"
+ " SELECT inhrelid, level + 1\n"
+ " FROM pg_catalog.pg_inherits i\n"
+ " JOIN d ON i.inhparent = d.oid)\n"
+ " SELECT sum(pg_catalog.pg_table_size(d.oid)) AS tps\n"
+ " FROM d) s");
+ }
+ else
+ {
+ /* PostgreSQL 12 has pg_partition_tree function */
+ appendPQExpBufferStr(&buf,
+ ",\n LATERAL (SELECT sum(pg_catalog.pg_table_size(ppt.relid)) AS tps"
+ "\n FROM pg_catalog.pg_partition_tree(c.oid) ppt) s");
+ }
+ }
+
+ appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
+ if (showTables)
+ {
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_RELATION) ",");
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_TABLE) ",");
+ }
+ if (showIndexes)
+ {
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_INDEX) ",");
+ appendPQExpBufferStr(&buf, CppAsString2(RELKIND_PARTITIONED_INDEX) ",");
+ }
+ appendPQExpBufferStr(&buf, "''"); /* dummy */
+ appendPQExpBufferStr(&buf, ")\n");
+
+ appendPQExpBufferStr(&buf, " AND NOT c.relispartition\n");
+
+ if (!pattern)
+ appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
+ " AND n.nspname !~ '^pg_toast'\n"
+ " AND n.nspname <> 'information_schema'\n");
+
+ if (!validateSQLNamePattern(&buf, pattern, true, false,
+ "n.nspname", "c.relname", NULL,
+ "pg_catalog.pg_table_is_visible(c.oid)",
+ NULL, 3))
+ {
+ termPQExpBuffer(&buf);
+ return false;
+ }
+
+ appendPQExpBuffer(&buf, "ORDER BY \"Schema\", %s\"Name\";",
+ mixed_output ? "\"Type\" DESC, " : "");
+
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
+
+ initPQExpBuffer(&title);
+ appendPQExpBufferStr(&title, tabletitle);
+
+ myopt.title = title.data;
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
+
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ termPQExpBuffer(&title);
+
+ PQclear(res);
+ return true;
+}
+
+
/*
* \dP
* Takes an optional regexp to select particular relations
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 18ecaa60949..6abd7ba31b6 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -70,6 +70,9 @@ extern bool listAllDbs(const char *pattern, bool verbose);
/* \dt, \di, \ds, \dS, etc. */
extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
+/* \dh */
+extern bool listRootTables(const char *reltypes, const char *pattern, bool verbose);
+
/* \dP */
extern bool listPartitionedTables(const char *reltypes, const char *pattern, bool verbose);
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 714b8619233..3f75be4644f 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -254,6 +254,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dFp[x+] [PATTERN] list text search parsers\n");
HELP0(" \\dFt[x+] [PATTERN] list text search templates\n");
HELP0(" \\dg[Sx+] [PATTERN] list roles\n");
+ HELP0(" \\dh[tix+] [PATTERN] list high-level (root) relations\n");
HELP0(" \\di[Sx+] [PATTERN] list indexes\n");
HELP0(" \\dl[x+] list large objects, same as \\lo_list\n");
HELP0(" \\dL[Sx+] [PATTERN] list procedural languages\n");
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..2e0ff93b69a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -691,6 +691,16 @@ static const SchemaQuery Query_for_list_of_partitioned_tables = {
.result = "c.relname",
};
+static const SchemaQuery Query_for_list_of_root_tables = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
static const SchemaQuery Query_for_list_of_tables_for_constraint = {
.catname = "pg_catalog.pg_class c, pg_catalog.pg_constraint con",
.selcondition = "c.oid=con.conrelid and c.relkind IN ("
@@ -795,6 +805,16 @@ static const SchemaQuery Query_for_list_of_partitioned_indexes = {
.result = "c.relname",
};
+static const SchemaQuery Query_for_list_of_root_indexes = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_INDEX) ", "
+ CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
/* All relations */
static const SchemaQuery Query_for_list_of_relations = {
@@ -814,6 +834,19 @@ static const SchemaQuery Query_for_list_of_partitioned_relations = {
.result = "c.relname",
};
+/* root relations */
+static const SchemaQuery Query_for_list_of_root_relations = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition = "c.relispartition = false AND "
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
+ CppAsString2(RELKIND_INDEX) ", "
+ CppAsString2(RELKIND_PARTITIONED_INDEX) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "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)",
@@ -1880,7 +1913,8 @@ psql_completion(const char *text, int start, int end)
"\\d", "\\da", "\\dA", "\\dAc", "\\dAf", "\\dAo", "\\dAp",
"\\db", "\\dc", "\\dconfig", "\\dC", "\\dd", "\\ddp", "\\dD",
"\\des", "\\det", "\\deu", "\\dew", "\\dE", "\\df",
- "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL",
+ "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\dh", "\\dhi", "\\dht",
+ "\\di", "\\dl", "\\dL",
"\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt",
"\\drds", "\\drg", "\\dRs", "\\dRp", "\\ds",
"\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy",
@@ -5269,8 +5303,15 @@ match_previous_words(int pattern_id,
else if (TailMatchesCS("\\dFt*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_templates);
/* must be at end of \dF alternatives: */
+
else if (TailMatchesCS("\\dF*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_ts_configurations);
+ else if (TailMatchesCS("\\dhi*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_indexes);
+ else if (TailMatchesCS("\\dht*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_tables);
+ else if (TailMatchesCS("\\dh*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_root_relations);
else if (TailMatchesCS("\\di*"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_indexes);
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 6543e90de75..7ba2fff3919 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5017,6 +5017,33 @@ create index testpart_orange_index on testpart_orange(logdate);
testpart | testpart_apple_index | regress_partitioning_role | | testpart_apple
(1 row)
+-- only root relations should be displayed
+\dh test*apple*
+ List of root relations
+ Schema | Name | Owner | Type | Table
+----------+-----------------------+---------------------------+-------------------+-----------------
+ testpart | testtable_apple | regress_partitioning_role | table |
+ testpart | testpart_apple | regress_partitioning_role | partitioned table |
+ testpart | testpart_apple_index | regress_partitioning_role | partitioned index | testpart_apple
+ testpart | testtable_apple_index | regress_partitioning_role | index | testtable_apple
+(4 rows)
+
+\dht test*apple*
+ List of root tables
+ Schema | Name | Owner
+----------+-----------------+---------------------------
+ testpart | testpart_apple | regress_partitioning_role
+ testpart | testtable_apple | regress_partitioning_role
+(2 rows)
+
+\dhi test*apple*
+ List of root indexes
+ Schema | Name | Owner | Table
+----------+-----------------------+---------------------------+-----------------
+ testpart | testpart_apple_index | regress_partitioning_role | testpart_apple
+ testpart | testtable_apple_index | regress_partitioning_role | testtable_apple
+(2 rows)
+
drop table testtable_apple;
drop table testtable_orange;
drop table testpart_apple;
@@ -5038,6 +5065,7 @@ create table child_30_35 partition of child_30_40
create table child_35_40 partition of child_30_40
for values from (35) to (40);
insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
\dPt
List of partitioned tables
Schema | Name | Owner
@@ -5106,6 +5134,37 @@ insert into parent_tab values (generate_series(30,39));
testpart | child_30_40_id_idx | regress_partitioning_role | partitioned index | parent_index | child_30_40
(4 rows)
+-- only root relations should be displayed
+\dht
+ List of root tables
+ Schema | Name | Owner
+----------+------------+---------------------------
+ testpart | parent_tab | regress_partitioning_role
+(1 row)
+
+\dhi
+ List of root indexes
+ Schema | Name | Owner | Table
+----------+--------------+---------------------------+------------
+ testpart | parent_index | regress_partitioning_role | parent_tab
+(1 row)
+
+\dh testpart.*
+ List of root relations
+ Schema | Name | Owner | Type | Table
+----------+--------------+---------------------------+-------------------+------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table |
+ testpart | parent_index | regress_partitioning_role | partitioned index | parent_tab
+(2 rows)
+
+\dh
+ List of root relations
+ Schema | Name | Owner | Type | Table
+----------+--------------+---------------------------+-------------------+------------
+ testpart | parent_tab | regress_partitioning_role | partitioned table |
+ testpart | parent_index | regress_partitioning_role | partitioned index | parent_tab
+(2 rows)
+
drop table parent_tab cascade;
drop schema testpart;
set search_path to default;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 97d1be3aac3..5606f70a106 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1257,6 +1257,11 @@ create index testpart_orange_index on testpart_orange(logdate);
\dPt test*apple*
\dPi test*apple*
+-- only root relations should be displayed
+\dh test*apple*
+\dht test*apple*
+\dhi test*apple*
+
drop table testtable_apple;
drop table testtable_orange;
drop table testpart_apple;
@@ -1280,6 +1285,7 @@ create table child_35_40 partition of child_30_40
for values from (35) to (40);
insert into parent_tab values (generate_series(30,39));
+-- only partition related object should be displayed
\dPt
\dPi
@@ -1291,6 +1297,13 @@ insert into parent_tab values (generate_series(30,39));
\dPn
\dPn testpart.*
+-- only root relations should be displayed
+\dht
+\dhi
+
+\dh testpart.*
+\dh
+
drop table parent_tab cascade;
drop schema testpart;
--
2.43.0
