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 <[email protected]>
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
