From 59414cdedbc2ed418d66eddc0fcaf9dc40fbc27f Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Thu, 11 Aug 2011 11:16:29 -0400 Subject: [PATCH] Change psql's \dd command to do something more useful. Instead of displaying comments on an arbitrary subset of the object types which support them, make \dd display comments on exactly those object types which don't have their own backlash commands. We now regard the display of comments as properly the job of the relevant backslash command (though many of them do so only in verbose mode) rather than something that \dd should be responsible for. However, a handful of object types have no backlash command, so make \dd give information about those. Josh Kupershmidt --- doc/src/sgml/ref/psql-ref.sgml | 27 +++--- src/bin/psql/describe.c | 166 ++++++++++++++------------------- src/bin/psql/help.c | 2 +- 3 files changed, 81 insertions(+), 114 deletions(-) diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index db97df13c4..3a10e76320 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -990,24 +990,21 @@ testdb=> \dd[S] [ pattern ] - Shows the descriptions of objects matching the pattern, or of all visible objects if - no argument is given. But in either case, only objects that have - a description are listed. + Shows the descriptions of objects of type constraint, + operator class, operator family, + rule, and trigger. All + other comments may be viewed by the respective backslash commands for + those object types. + + + + \dd displays descriptions for objects matching the + pattern, or of visible + objects of the appropriate type if no argument is given. But in either + case, only objects that have a description are listed. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. - Object covers aggregates, functions, operators, - types, relations (tables, views, indexes, sequences), large - objects, rules, and triggers. For example: - -=> \dd version - Object descriptions - Schema | Name | Object | Description -------------+---------+----------+--------------------------- - pg_catalog | version | function | PostgreSQL version string -(1 row) - diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index f08f917501..18502fe477 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -830,8 +830,11 @@ listDefaultACLs(const char *pattern) * * \dd [foo] * - * Note: This only lists things that actually have a description. For complete - * lists of things, there are other \d? commands. + * Note: This command only lists comments for object types which do not have + * their comments displayed by their own backslash commands. The following + * types of objects will be displayed: constraint, operator class, + * operator family, rule, and trigger. + * */ bool objectDescription(const char *pattern, bool showSystem) @@ -851,110 +854,78 @@ objectDescription(const char *pattern, bool showSystem) gettext_noop("Object"), gettext_noop("Description")); - /* Aggregate descriptions */ - appendPQExpBuffer(&buf, - " SELECT p.oid as oid, p.tableoid as tableoid,\n" - " n.nspname as nspname,\n" - " CAST(p.proname AS pg_catalog.text) as name," - " CAST('%s' AS pg_catalog.text) as object\n" - " FROM pg_catalog.pg_proc p\n" - " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n" - " WHERE p.proisagg\n", - gettext_noop("aggregate")); - - if (!showSystem && !pattern) - appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" - " AND n.nspname <> 'information_schema'\n"); - - processSQLNamePattern(pset.db, &buf, pattern, true, false, - "n.nspname", "p.proname", NULL, - "pg_catalog.pg_function_is_visible(p.oid)"); - - /* Function descriptions */ + /* Constraint descriptions */ appendPQExpBuffer(&buf, - "UNION ALL\n" - " SELECT p.oid as oid, p.tableoid as tableoid,\n" + " SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n" " n.nspname as nspname,\n" - " CAST(p.proname AS pg_catalog.text) as name," + " CAST(pgc.conname AS pg_catalog.text) as name," " CAST('%s' AS pg_catalog.text) as object\n" - " FROM pg_catalog.pg_proc p\n" - " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n" - " WHERE NOT p.proisagg\n", - gettext_noop("function")); - - if (!showSystem && !pattern) - appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" - " AND n.nspname <> 'information_schema'\n"); - - processSQLNamePattern(pset.db, &buf, pattern, true, false, - "n.nspname", "p.proname", NULL, - "pg_catalog.pg_function_is_visible(p.oid)"); - - /* Operator descriptions */ - appendPQExpBuffer(&buf, - "UNION ALL\n" - " SELECT o.oid as oid, o.tableoid as tableoid,\n" - " n.nspname as nspname,\n" - " CAST(o.oprname AS pg_catalog.text) as name," - " CAST('%s' AS pg_catalog.text) as object\n" - " FROM pg_catalog.pg_operator o\n" - " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n", - gettext_noop("operator")); + " FROM pg_catalog.pg_constraint pgc\n" + " JOIN pg_catalog.pg_class c " + "ON c.oid = pgc.conrelid\n" + " LEFT JOIN pg_catalog.pg_namespace n " + " ON n.oid = c.relnamespace\n", + gettext_noop("constraint")); if (!showSystem && !pattern) appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" " AND n.nspname <> 'information_schema'\n"); - processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, - "n.nspname", "o.oprname", NULL, - "pg_catalog.pg_operator_is_visible(o.oid)"); - - /* Type descriptions */ - appendPQExpBuffer(&buf, - "UNION ALL\n" - " SELECT t.oid as oid, t.tableoid as tableoid,\n" - " n.nspname as nspname,\n" - " pg_catalog.format_type(t.oid, NULL) as name," - " CAST('%s' AS pg_catalog.text) as object\n" - " FROM pg_catalog.pg_type t\n" - " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n", - gettext_noop("data type")); - - if (!showSystem && !pattern) - appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" - " AND n.nspname <> 'information_schema'\n"); - - processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, - "n.nspname", "pg_catalog.format_type(t.oid, NULL)", - NULL, - "pg_catalog.pg_type_is_visible(t.oid)"); - - /* Relation (tables, views, indexes, sequences) descriptions */ - appendPQExpBuffer(&buf, - "UNION ALL\n" - " SELECT c.oid as oid, c.tableoid as tableoid,\n" - " n.nspname as nspname,\n" - " CAST(c.relname AS pg_catalog.text) as name,\n" - " CAST(\n" - " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 'f' THEN '%s' END" - " AS pg_catalog.text) as object\n" - " FROM pg_catalog.pg_class c\n" - " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" - " WHERE c.relkind IN ('r', 'v', 'i', 'S', 'f')\n", - gettext_noop("table"), - gettext_noop("view"), - gettext_noop("index"), - gettext_noop("sequence"), - gettext_noop("foreign table")); - - if (!showSystem && !pattern) - appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" - " AND n.nspname <> 'information_schema'\n"); - - processSQLNamePattern(pset.db, &buf, pattern, true, false, - "n.nspname", "c.relname", NULL, + processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, + false, "n.nspname", "pgc.conname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); + /* + * pg_opclass.opcmethod only available in 8.3+, and comment on operator + * family only available in 8.3+ + */ + if (pset.sversion >= 80300) + { + /* Operator class descriptions */ + appendPQExpBuffer(&buf, + "UNION ALL\n" + " SELECT o.oid as oid, o.tableoid as tableoid,\n" + " n.nspname as nspname,\n" + " CAST(o.opcname AS pg_catalog.text) as name,\n" + " CAST('%s' AS pg_catalog.text) as object\n" + " FROM pg_catalog.pg_opclass o\n" + " JOIN pg_catalog.pg_am am ON " + "o.opcmethod = am.oid\n" + " JOIN pg_catalog.pg_namespace n ON " + "n.oid = o.opcnamespace\n", + gettext_noop("operator class")); + + if (!showSystem && !pattern) + appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "o.opcname", NULL, + "pg_catalog.pg_opclass_is_visible(o.oid)"); + + /* Operator family descriptions */ + appendPQExpBuffer(&buf, + "UNION ALL\n" + " SELECT opf.oid as oid, opf.tableoid as tableoid,\n" + " n.nspname as nspname,\n" + " CAST(opf.opfname AS pg_catalog.text) AS name,\n" + " CAST('%s' AS pg_catalog.text) as object\n" + " FROM pg_catalog.pg_opfamily opf\n" + " JOIN pg_catalog.pg_am am " + "ON opf.opfmethod = am.oid\n" + " JOIN pg_catalog.pg_namespace n " + "ON opf.opfnamespace = n.oid\n", + gettext_noop("operator family")); + + if (!showSystem && !pattern) + appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "opf.opfname", NULL, + "pg_catalog.pg_opfamily_is_visible(opf.oid)"); + } + /* Rule descriptions (ignore rules for views) */ appendPQExpBuffer(&buf, "UNION ALL\n" @@ -972,7 +943,6 @@ objectDescription(const char *pattern, bool showSystem) appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n" " AND n.nspname <> 'information_schema'\n"); - /* XXX not sure what to do about visibility rule here? */ processSQLNamePattern(pset.db, &buf, pattern, true, false, "n.nspname", "r.rulename", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); @@ -993,7 +963,6 @@ objectDescription(const char *pattern, bool showSystem) appendPQExpBuffer(&buf, "WHERE n.nspname <> 'pg_catalog'\n" " AND n.nspname <> 'information_schema'\n"); - /* XXX not sure what to do about visibility rule here? */ processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern, false, "n.nspname", "t.tgname", NULL, "pg_catalog.pg_table_is_visible(c.oid)"); @@ -2739,6 +2708,7 @@ listDomains(const char *pattern, bool verbose, bool showSystem) gettext_noop("Schema"), gettext_noop("Name"), gettext_noop("Type")); + if (pset.sversion >= 90100) appendPQExpBuffer(&buf, " COALESCE((SELECT ' collate ' || c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n" diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index b99ba3acfb..53e4cd0c0e 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -197,7 +197,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n")); fprintf(output, _(" \\dc[S+] [PATTERN] list conversions\n")); fprintf(output, _(" \\dC[+] [PATTERN] list casts\n")); - fprintf(output, _(" \\dd[S] [PATTERN] show comments on objects\n")); + fprintf(output, _(" \\dd[S] [PATTERN] show object descriptions not displayed elsewhere\n")); fprintf(output, _(" \\ddp [PATTERN] list default privileges\n")); fprintf(output, _(" \\dD[S+] [PATTERN] list domains\n")); fprintf(output, _(" \\det[+] [PATTERN] list foreign tables\n")); -- 2.40.0