Date: Sun, 10 Nov 2019 15:29:28 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Subject: psql \d for wide tables / pattern for individual columns
Message-ID: <20191110212928.GQ4999@telsasoft.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="47eKBCiAZYFK5l32"
Content-Disposition: inline
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--47eKBCiAZYFK5l32
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

We have some very wide tables (historically, up to 1600 columns ; this is
improved now, but sometimes still several hundred, with numerous pages output
to psql pager).  Is is reasonable to suggest adding a psql command to show a
table's definition, without all the columns listed?

Or limit display to matching columns ?  That's more general than the above
functionality, if "empty string" is taken to mean "show no columns", like \d
table "" or \d table *id or \d table ????

Attached minimal patch for the latter.

postgres=# \d pg_attribute ""
        Table "pg_catalog.pg_attribute"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute "attn*|attrel*"
            Table "pg_catalog.pg_attribute"
   Column   |   Type   | Collation | Nullable | Default 
------------+----------+-----------+----------+---------
 attrelid   | oid      |           | not null | 
 attname    | name     |           | not null | 
 attnum     | smallint |           | not null | 
 attndims   | integer  |           | not null | 
 attnotnull | boolean  |           | not null | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute ??????
           Table "pg_catalog.pg_attribute"
 Column |   Type    | Collation | Nullable | Default 
--------+-----------+-----------+----------+---------
 attlen | smallint  |           | not null | 
 attnum | smallint  |           | not null | 
 attacl | aclitem[] |           |          | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

postgres=# \d pg_attribute *id
         Table "pg_catalog.pg_attribute"
  Column  | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 attrelid | oid  |           | not null | 
 atttypid | oid  |           | not null | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

--47eKBCiAZYFK5l32
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v1-0001-psql-Allow-filtering-columns-shown-by-d.patch"

From fdcde33f93af544eb1be0f327ffa49a133397da3 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sun, 10 Nov 2019 15:02:00 -0600
Subject: [PATCH v1] psql: Allow filtering columns shown by \d

---
 src/bin/psql/command.c  |  7 +++++--
 src/bin/psql/describe.c | 26 ++++++++++++++++----------
 src/bin/psql/describe.h |  2 +-
 3 files changed, 22 insertions(+), 13 deletions(-)

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ecfa1bc..7746f63 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -699,13 +699,16 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 
 	if (active_branch)
 	{
-		char	   *pattern;
+		char	   *pattern, *pattern2, quote;
 		bool		show_verbose,
 					show_system;
 
 		/* We don't do SQLID reduction on the pattern yet */
 		pattern = psql_scan_slash_option(scan_state,
 										 OT_NORMAL, NULL, true);
+		if (pattern)
+			pattern2 = psql_scan_slash_option(scan_state,
+										 OT_SQLID, &quote, true);
 
 		show_verbose = strchr(cmd, '+') ? true : false;
 		show_system = strchr(cmd, 'S') ? true : false;
@@ -716,7 +719,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
 			case '+':
 			case 'S':
 				if (pattern)
-					success = describeTableDetails(pattern, show_verbose, show_system);
+					success = describeTableDetails(pattern, show_verbose, show_system, pattern2);
 				else
 					/* standard listing of interesting things */
 					success = listTables("tvmsE", NULL, show_verbose, show_system);
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 02740dd..dce4a4e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -30,7 +30,8 @@
 static bool describeOneTableDetails(const char *schemaname,
 									const char *relationname,
 									const char *oid,
-									bool verbose);
+									bool verbose,
+									const char *pattern2);
 static void add_tablespace_footer(printTableContent *const cont, char relkind,
 								  Oid tablespace, const bool newline);
 static void add_role_attribute(PQExpBuffer buf, const char *const str);
@@ -1363,7 +1364,7 @@ objectDescription(const char *pattern, bool showSystem)
  * verbose: if true, this is \d+
  */
 bool
-describeTableDetails(const char *pattern, bool verbose, bool showSystem)
+describeTableDetails(const char *pattern, bool verbose, bool showSystem, const char *pattern2)
 {
 	PQExpBufferData buf;
 	PGresult   *res;
@@ -1417,7 +1418,7 @@ describeTableDetails(const char *pattern, bool verbose, bool showSystem)
 		nspname = PQgetvalue(res, i, 1);
 		relname = PQgetvalue(res, i, 2);
 
-		if (!describeOneTableDetails(nspname, relname, oid, verbose))
+		if (!describeOneTableDetails(nspname, relname, oid, verbose, pattern2))
 		{
 			PQclear(res);
 			return false;
@@ -1444,7 +1445,8 @@ static bool
 describeOneTableDetails(const char *schemaname,
 						const char *relationname,
 						const char *oid,
-						bool verbose)
+						bool verbose,
+						const char *pattern2)
 {
 	bool		retval = false;
 	PQExpBufferData buf;
@@ -1917,12 +1919,16 @@ describeOneTableDetails(const char *schemaname,
 
 	appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_attribute a");
 	appendPQExpBuffer(&buf, "\nWHERE a.attrelid = '%s' AND a.attnum > 0 AND NOT a.attisdropped", oid);
-	appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
-
-	res = PSQLexec(buf.data);
-	if (!res)
-		goto error_return;
-	numrows = PQntuples(res);
+	if (pattern2 && !*pattern2) {
+		numrows = 0;
+	} else {
+		processSQLNamePattern(pset.db, &buf, pattern2, true, false, NULL, "attname", NULL, NULL);
+		appendPQExpBufferStr(&buf, "\nORDER BY a.attnum;");
+		res = PSQLexec(buf.data);
+		if (!res)
+			goto error_return;
+		numrows = PQntuples(res);
+	}
 
 	/* Make title */
 	switch (tableinfo.relkind)
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index b50c7d2..a585645 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -43,7 +43,7 @@ extern bool listDefaultACLs(const char *pattern);
 extern bool objectDescription(const char *pattern, bool showSystem);
 
 /* \d foo */
-extern bool describeTableDetails(const char *pattern, bool verbose, bool showSystem);
+extern bool describeTableDetails(const char *pattern, bool verbose, bool showSystem, const char *pattern2);
 
 /* \dF */
 extern bool listTSConfigs(const char *pattern, bool verbose);
-- 
2.7.4


--47eKBCiAZYFK5l32--