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)
>From fdcde33f93af544eb1be0f327ffa49a133397da3 Mon Sep 17 00:00:00 2001 From: Justin Pryzby <pryz...@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, "e, 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