psql: Add option to use expanded mode to all list commands.

This allows "x" to be appended to any psql list-like meta-command,
forcing its output to be displayed in expanded mode. This improves
readability in cases where the output is very wide. For example,
"\dfx+" (or equivalently "\df+x") will produce a list of functions,
with additional details, in expanded mode.

This works with all \d* meta-commands, plus \l, \z, and \lo_list, with
the one exception that the expanded mode option "x" cannot be appended
to "\d" by itself, since "\dx" already means something else.

Dean Rasheed, reviewed by Greg Sabino Mullane.

Discussion: https://postgr.es/m/CAEZATCVXJk3KsmCncf7PAVbxdDAUDm3QzDgGT7mBYySWikuOYw@mail.gmail.com

5 files changed, 212 insertions, 62 deletions

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 5dd4c2d2687..613583145e2 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -377,7 +377,10 @@ exec_command(const char *cmd,
 	else if (strcmp(cmd, "if") == 0)
 		status = exec_command_if(scan_state, cstack, query_buf);
 	else if (strcmp(cmd, "l") == 0 || strcmp(cmd, "list") == 0 ||
-			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0)
+			 strcmp(cmd, "lx") == 0 || strcmp(cmd, "listx") == 0 ||
+			 strcmp(cmd, "l+") == 0 || strcmp(cmd, "list+") == 0 ||
+			 strcmp(cmd, "lx+") == 0 || strcmp(cmd, "listx+") == 0 ||
+			 strcmp(cmd, "l+x") == 0 || strcmp(cmd, "list+x") == 0)
 		status = exec_command_list(scan_state, active_branch, cmd);
 	else if (strncmp(cmd, "lo_", 3) == 0)
 		status = exec_command_lo(scan_state, active_branch, cmd);
@@ -424,7 +427,9 @@ exec_command(const char *cmd,
 									query_buf, previous_buf);
 	else if (strcmp(cmd, "x") == 0)
 		status = exec_command_x(scan_state, active_branch);
-	else if (strcmp(cmd, "z") == 0 || strcmp(cmd, "zS") == 0)
+	else if (strcmp(cmd, "z") == 0 ||
+			 strcmp(cmd, "zS") == 0 || strcmp(cmd, "zx") == 0 ||
+			 strcmp(cmd, "zSx") == 0 || strcmp(cmd, "zxS") == 0)
 		status = exec_command_z(scan_state, active_branch, cmd);
 	else if (strcmp(cmd, "!") == 0)
 		status = exec_command_shell_escape(scan_state, active_branch);
@@ -850,6 +855,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		char	   *pattern;
 		bool		show_verbose,
 					show_system;
+		unsigned short int save_expanded;
 
 		/* We don't do SQLID reduction on the pattern yet */
 		pattern = psql_scan_slash_option(scan_state,
@@ -858,6 +864,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 		show_verbose = strchr(cmd, '+') ? true : false;
 		show_system = strchr(cmd, 'S') ? true : false;
 
+		/*
+		 * The 'x' option turns expanded mode on for this command only. This
+		 * is allowed in all \d* commands, except \d by itself, since \dx is a
+		 * separate command. So the 'x' option cannot appear immediately after
+		 * \d, but it can appear after \d followed by other options.
+		 */
+		save_expanded = pset.popt.topt.expanded;
+		if (cmd[1] != '\0' && strchr(&cmd[2], 'x'))
+			pset.popt.topt.expanded = 1;
+
 		switch (cmd[1])
 		{
 			case '\0':
@@ -873,13 +889,14 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				{
 					char	   *pattern2 = NULL;
 
-					if (pattern && cmd[2] != '\0' && cmd[2] != '+')
+					if (pattern && cmd[2] != '\0' && cmd[2] != '+' && cmd[2] != 'x')
 						pattern2 = psql_scan_slash_option(scan_state, OT_NORMAL, NULL, true);
 
 					switch (cmd[2])
 					{
 						case '\0':
 						case '+':
+						case 'x':
 							success = describeAccessMethods(pattern, show_verbose);
 							break;
 						case 'c':
@@ -941,6 +958,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 					case 'p':
 					case 't':
 					case 'w':
+					case 'x':
 						success = exec_command_dfo(scan_state, cmd, pattern,
 												   show_verbose, show_system);
 						break;
@@ -981,6 +999,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 						case 't':
 						case 'i':
 						case 'n':
+						case 'x':
 							success = listPartitionedTables(&cmd[2], pattern, show_verbose);
 							break;
 						default:
@@ -1041,6 +1060,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				{
 					case '\0':
 					case '+':
+					case 'x':
 						success = listTSConfigs(pattern, show_verbose);
 						break;
 					case 'p':
@@ -1093,6 +1113,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 				status = PSQL_CMD_UNKNOWN;
 		}
 
+		/* Restore original expanded mode */
+		pset.popt.topt.expanded = save_expanded;
+
 		free(pattern);
 	}
 	else
@@ -2044,14 +2067,23 @@ exec_command_list(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	{
 		char	   *pattern;
 		bool		show_verbose;
+		unsigned short int save_expanded;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
 		show_verbose = strchr(cmd, '+') ? true : false;
 
+		/* if 'x' option specified, force expanded mode */
+		save_expanded = pset.popt.topt.expanded;
+		if (strchr(cmd, 'x'))
+			pset.popt.topt.expanded = 1;
+
 		success = listAllDbs(pattern, show_verbose);
 
+		/* restore original expanded mode */
+		pset.popt.topt.expanded = save_expanded;
+
 		free(pattern);
 	}
 	else
@@ -2107,10 +2139,23 @@ exec_command_lo(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			}
 		}
 
-		else if (strcmp(cmd + 3, "list") == 0)
-			success = listLargeObjects(false);
-		else if (strcmp(cmd + 3, "list+") == 0)
-			success = listLargeObjects(true);
+		else if (strncmp(cmd + 3, "list", 4) == 0)
+		{
+			bool		show_verbose;
+			unsigned short int save_expanded;
+
+			show_verbose = strchr(cmd, '+') ? true : false;
+
+			/* if 'x' option specified, force expanded mode */
+			save_expanded = pset.popt.topt.expanded;
+			if (strchr(cmd, 'x'))
+				pset.popt.topt.expanded = 1;
+
+			success = listLargeObjects(show_verbose);
+
+			/* restore original expanded mode */
+			pset.popt.topt.expanded = save_expanded;
+		}
 
 		else if (strcmp(cmd + 3, "unlink") == 0)
 		{
@@ -3061,14 +3106,23 @@ exec_command_z(PsqlScanState scan_state, bool active_branch, const char *cmd)
 	{
 		char	   *pattern;
 		bool		show_system;
+		unsigned short int save_expanded;
 
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
 
 		show_system = strchr(cmd, 'S') ? true : false;
 
+		/* if 'x' option specified, force expanded mode */
+		save_expanded = pset.popt.topt.expanded;
+		if (strchr(cmd, 'x'))
+			pset.popt.topt.expanded = 1;
+
 		success = permissionsList(pattern, show_system);
 
+		/* restore original expanded mode */
+		pset.popt.topt.expanded = save_expanded;
+
 		free(pattern);
 	}
 	else
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 54ebc889c3b..2ef99971ac0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -309,9 +309,9 @@ describeFunctions(const char *functypes, const char *func_pattern,
 	/* No "Parallel" column before 9.6 */
 	static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, true, false, false, false, false};
 
-	if (strlen(functypes) != strspn(functypes, "anptwS+"))
+	if (strlen(functypes) != strspn(functypes, "anptwSx+"))
 	{
-		pg_log_error("\\df only takes [anptwS+] as options");
+		pg_log_error("\\df only takes [anptwSx+] as options");
 		return true;
 	}
 
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index fda83465efa..da8e1ade5df 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -219,67 +219,67 @@ slashUsage(unsigned short int pager)
 	HELP0("\n");
 
 	HELP0("Informational\n");
-	HELP0("  (options: S = show system objects, + = additional detail)\n");
-	HELP0("  \\d[S+]                 list tables, views, and sequences\n");
-	HELP0("  \\d[S+]  NAME           describe table, view, sequence, or index\n");
-	HELP0("  \\da[S]  [PATTERN]      list aggregates\n");
-	HELP0("  \\dA[+]  [PATTERN]      list access methods\n");
-	HELP0("  \\dAc[+] [AMPTRN [TYPEPTRN]]  list operator classes\n");
-	HELP0("  \\dAf[+] [AMPTRN [TYPEPTRN]]  list operator families\n");
-	HELP0("  \\dAo[+] [AMPTRN [OPFPTRN]]   list operators of operator families\n");
-	HELP0("  \\dAp[+] [AMPTRN [OPFPTRN]]   list support functions of operator families\n");
-	HELP0("  \\db[+]  [PATTERN]      list tablespaces\n");
-	HELP0("  \\dc[S+] [PATTERN]      list conversions\n");
-	HELP0("  \\dconfig[+] [PATTERN]  list configuration parameters\n");
-	HELP0("  \\dC[+]  [PATTERN]      list casts\n");
-	HELP0("  \\dd[S]  [PATTERN]      show object descriptions not displayed elsewhere\n");
-	HELP0("  \\dD[S+] [PATTERN]      list domains\n");
-	HELP0("  \\ddp    [PATTERN]      list default privileges\n");
-	HELP0("  \\dE[S+] [PATTERN]      list foreign tables\n");
-	HELP0("  \\des[+] [PATTERN]      list foreign servers\n");
-	HELP0("  \\det[+] [PATTERN]      list foreign tables\n");
-	HELP0("  \\deu[+] [PATTERN]      list user mappings\n");
-	HELP0("  \\dew[+] [PATTERN]      list foreign-data wrappers\n");
-	HELP0("  \\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]\n"
+	HELP0("  (options: S = show system objects, x = expanded mode, + = additional detail)\n");
+	HELP0("  \\d[Sx+]                list tables, views, and sequences\n");
+	HELP0("  \\d[S+]   NAME          describe table, view, sequence, or index\n");
+	HELP0("  \\da[Sx]  [PATTERN]     list aggregates\n");
+	HELP0("  \\dA[x+]  [PATTERN]     list access methods\n");
+	HELP0("  \\dAc[x+] [AMPTRN [TYPEPTRN]]  list operator classes\n");
+	HELP0("  \\dAf[x+] [AMPTRN [TYPEPTRN]]  list operator families\n");
+	HELP0("  \\dAo[x+] [AMPTRN [OPFPTRN]]   list operators of operator families\n");
+	HELP0("  \\dAp[x+] [AMPTRN [OPFPTRN]]   list support functions of operator families\n");
+	HELP0("  \\db[x+]  [PATTERN]     list tablespaces\n");
+	HELP0("  \\dc[Sx+] [PATTERN]     list conversions\n");
+	HELP0("  \\dconfig[x+] [PATTERN] list configuration parameters\n");
+	HELP0("  \\dC[x+]  [PATTERN]     list casts\n");
+	HELP0("  \\dd[Sx]  [PATTERN]     show object descriptions not displayed elsewhere\n");
+	HELP0("  \\dD[Sx+] [PATTERN]     list domains\n");
+	HELP0("  \\ddp[x]  [PATTERN]     list default privileges\n");
+	HELP0("  \\dE[Sx+] [PATTERN]     list foreign tables\n");
+	HELP0("  \\des[x+] [PATTERN]     list foreign servers\n");
+	HELP0("  \\det[x+] [PATTERN]     list foreign tables\n");
+	HELP0("  \\deu[x+] [PATTERN]     list user mappings\n");
+	HELP0("  \\dew[x+] [PATTERN]     list foreign-data wrappers\n");
+	HELP0("  \\df[anptw][Sx+] [FUNCPTRN [TYPEPTRN ...]]\n"
 		  "                         list [only agg/normal/procedure/trigger/window] functions\n");
-	HELP0("  \\dF[+]  [PATTERN]      list text search configurations\n");
-	HELP0("  \\dFd[+] [PATTERN]      list text search dictionaries\n");
-	HELP0("  \\dFp[+] [PATTERN]      list text search parsers\n");
-	HELP0("  \\dFt[+] [PATTERN]      list text search templates\n");
-	HELP0("  \\dg[S+] [PATTERN]      list roles\n");
-	HELP0("  \\di[S+] [PATTERN]      list indexes\n");
-	HELP0("  \\dl[+]                 list large objects, same as \\lo_list\n");
-	HELP0("  \\dL[S+] [PATTERN]      list procedural languages\n");
-	HELP0("  \\dm[S+] [PATTERN]      list materialized views\n");
-	HELP0("  \\dn[S+] [PATTERN]      list schemas\n");
-	HELP0("  \\do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
+	HELP0("  \\dF[x+]  [PATTERN]     list text search configurations\n");
+	HELP0("  \\dFd[x+] [PATTERN]     list text search dictionaries\n");
+	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("  \\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");
+	HELP0("  \\dm[Sx+] [PATTERN]     list materialized views\n");
+	HELP0("  \\dn[Sx+] [PATTERN]     list schemas\n");
+	HELP0("  \\do[Sx+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]\n"
 		  "                         list operators\n");
-	HELP0("  \\dO[S+] [PATTERN]      list collations\n");
-	HELP0("  \\dp[S]  [PATTERN]      list table, view, and sequence access privileges\n");
-	HELP0("  \\dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]\n");
-	HELP0("  \\drds [ROLEPTRN [DBPTRN]] list per-database role settings\n");
-	HELP0("  \\drg[S] [PATTERN]      list role grants\n");
-	HELP0("  \\dRp[+] [PATTERN]      list replication publications\n");
-	HELP0("  \\dRs[+] [PATTERN]      list replication subscriptions\n");
-	HELP0("  \\ds[S+] [PATTERN]      list sequences\n");
-	HELP0("  \\dt[S+] [PATTERN]      list tables\n");
-	HELP0("  \\dT[S+] [PATTERN]      list data types\n");
-	HELP0("  \\du[S+] [PATTERN]      list roles\n");
-	HELP0("  \\dv[S+] [PATTERN]      list views\n");
-	HELP0("  \\dx[+]  [PATTERN]      list extensions\n");
-	HELP0("  \\dX     [PATTERN]      list extended statistics\n");
-	HELP0("  \\dy[+]  [PATTERN]      list event triggers\n");
-	HELP0("  \\l[+]   [PATTERN]      list databases\n");
-	HELP0("  \\sf[+]  FUNCNAME       show a function's definition\n");
-	HELP0("  \\sv[+]  VIEWNAME       show a view's definition\n");
-	HELP0("  \\z[S]   [PATTERN]      same as \\dp\n");
+	HELP0("  \\dO[Sx+] [PATTERN]     list collations\n");
+	HELP0("  \\dp[Sx]  [PATTERN]     list table, view, and sequence access privileges\n");
+	HELP0("  \\dP[itnx+] [PATTERN]   list [only index/table] partitioned relations [n=nested]\n");
+	HELP0("  \\drds[x] [ROLEPTRN [DBPTRN]] list per-database role settings\n");
+	HELP0("  \\drg[Sx] [PATTERN]     list role grants\n");
+	HELP0("  \\dRp[x+] [PATTERN]     list replication publications\n");
+	HELP0("  \\dRs[x+] [PATTERN]     list replication subscriptions\n");
+	HELP0("  \\ds[Sx+] [PATTERN]     list sequences\n");
+	HELP0("  \\dt[Sx+] [PATTERN]     list tables\n");
+	HELP0("  \\dT[Sx+] [PATTERN]     list data types\n");
+	HELP0("  \\du[Sx+] [PATTERN]     list roles\n");
+	HELP0("  \\dv[Sx+] [PATTERN]     list views\n");
+	HELP0("  \\dx[x+]  [PATTERN]     list extensions\n");
+	HELP0("  \\dX[x]   [PATTERN]     list extended statistics\n");
+	HELP0("  \\dy[x+]  [PATTERN]     list event triggers\n");
+	HELP0("  \\l[x+]   [PATTERN]     list databases\n");
+	HELP0("  \\sf[+]   FUNCNAME      show a function's definition\n");
+	HELP0("  \\sv[+]   VIEWNAME      show a view's definition\n");
+	HELP0("  \\z[Sx]   [PATTERN]     same as \\dp\n");
 	HELP0("\n");
 
 	HELP0("Large Objects\n");
 	HELP0("  \\lo_export LOBOID FILE write large object to file\n");
 	HELP0("  \\lo_import FILE [COMMENT]\n"
 		  "                         read large object from file\n");
-	HELP0("  \\lo_list[+]            list large objects\n");
+	HELP0("  \\lo_list[x+]           list large objects\n");
 	HELP0("  \\lo_unlink LOBOID      delete a large object\n");
 	HELP0("\n");
 
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 954ad86aee2..e6f7b9013d9 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2841,6 +2841,19 @@ Owned by: public.psql_serial_tab.id
  pg_catalog | exp  | double precision | double precision    | func
  pg_catalog | exp  | numeric          | numeric             | func
 
+\dfx exp
+Schema              | pg_catalog
+Name                | exp
+Result data type    | double precision
+Argument data types | double precision
+Type                | func
+--------------------+-----------------
+Schema              | pg_catalog
+Name                | exp
+Result data type    | numeric
+Argument data types | numeric
+Type                | func
+
 \pset tuples_only false
 \pset expanded on
 \d psql_serial_tab_id_seq
@@ -3047,6 +3060,49 @@ Access method: heap
  tableam_display | view_heap_psql     | view              | regress_display_role | permanent   | 0 bytes | 
 (4 rows)
 
+-- \d with 'x' enables expanded mode, but only without a pattern
+\d+x tbl_heap
+                                 Table "tableam_display.tbl_heap"
+ Column |      Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
+--------+----------------+-----------+----------+---------+----------+--------------+-------------
+ f1     | integer        |           |          |         | plain    |              | 
+ f2     | character(100) |           |          |         | extended |              | 
+
+\d+x
+List of relations
+-[ RECORD 1 ]---------------------
+Schema      | tableam_display
+Name        | mat_view_heap_psql
+Type        | materialized view
+Owner       | regress_display_role
+Persistence | permanent
+Size        | 0 bytes
+Description | 
+-[ RECORD 2 ]---------------------
+Schema      | tableam_display
+Name        | tbl_heap
+Type        | table
+Owner       | regress_display_role
+Persistence | permanent
+Size        | 0 bytes
+Description | 
+-[ RECORD 3 ]---------------------
+Schema      | tableam_display
+Name        | tbl_heap_psql
+Type        | table
+Owner       | regress_display_role
+Persistence | permanent
+Size        | 0 bytes
+Description | 
+-[ RECORD 4 ]---------------------
+Schema      | tableam_display
+Name        | view_heap_psql
+Type        | view
+Owner       | regress_display_role
+Persistence | permanent
+Size        | 0 bytes
+Description | 
+
 RESET ROLE;
 RESET search_path;
 DROP SCHEMA tableam_display CASCADE;
@@ -5238,6 +5294,30 @@ List of access methods
  btree | float_ops       | real                 | double precision      |      3 | in_range(real,real,double precision,boolean,boolean)
 (8 rows)
 
+\dApx+ btree time_ops
+List of support functions of operator families
+-[ RECORD 1 ]---------+---------------------------------------------------------------------------------
+AM                    | btree
+Operator family       | time_ops
+Registered left type  | time without time zone
+Registered right type | time without time zone
+Number                | 1
+Function              | time_cmp(time without time zone,time without time zone)
+-[ RECORD 2 ]---------+---------------------------------------------------------------------------------
+AM                    | btree
+Operator family       | time_ops
+Registered left type  | time without time zone
+Registered right type | time without time zone
+Number                | 4
+Function              | btequalimage(oid)
+-[ RECORD 3 ]---------+---------------------------------------------------------------------------------
+AM                    | btree
+Operator family       | time_ops
+Registered left type  | time without time zone
+Registered right type | interval
+Number                | 3
+Function              | in_range(time without time zone,time without time zone,interval,boolean,boolean)
+
 \dAp * pg_catalog.uuid_ops
                             List of support functions of operator families
   AM   | Operator family | Registered left type | Registered right type | Number |      Function      
@@ -6831,5 +6911,15 @@ CREATE TABLE defprivs (a int);
  public | defprivs | table | (default)         |                   | 
 (1 row)
 
+\zx defprivs
+Access privileges
+-[ RECORD 1 ]-----+----------
+Schema            | public
+Name              | defprivs
+Type              | table
+Access privileges | (default)
+Column privileges | 
+Policies          | 
+
 \pset null ''
 DROP TABLE defprivs;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index f6c5aa1f8bc..c58308ce14f 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -498,6 +498,7 @@ create table psql_serial_tab (id serial);
 \d psql_serial_tab_id_seq
 \pset tuples_only true
 \df exp
+\dfx exp
 \pset tuples_only false
 \pset expanded on
 \d psql_serial_tab_id_seq
@@ -560,6 +561,9 @@ CREATE MATERIALIZED VIEW mat_view_heap_psql USING heap_psql AS SELECT f1 from tb
 \dv+
 \set HIDE_TABLEAM on
 \d+
+-- \d with 'x' enables expanded mode, but only without a pattern
+\d+x tbl_heap
+\d+x
 RESET ROLE;
 RESET search_path;
 DROP SCHEMA tableam_display CASCADE;
@@ -1309,6 +1313,7 @@ drop role regress_partitioning_role;
 \dAo+ btree array_ops|float_ops
 \dAo * pg_catalog.jsonb_path_ops
 \dAp+ btree float_ops
+\dApx+ btree time_ops
 \dAp * pg_catalog.uuid_ops
 
 -- check \dconfig
@@ -1927,5 +1932,6 @@ ROLLBACK;
 CREATE TABLE defprivs (a int);
 \pset null '(default)'
 \z defprivs
+\zx defprivs
 \pset null ''
 DROP TABLE defprivs;