Here's an updated version of the psql backslash patch that should apply cleanly to the current HEAD. To recap, this makes all the \dX commands (most importantly to most: \df) work like \dt does, in that it requires a \dXS to see system items. See the archives for much more discussion on the issue.
-- Greg Sabino Mullane
Index: command.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.196
diff -c -r1.196 command.c
*** command.c 15 Sep 2008 12:18:00 -0000 1.196
--- command.c 7 Nov 2008 20:59:30 -0000
***************
*** 329,341 ****
else if (cmd[0] == 'd')
{
char *pattern;
! bool show_verbose;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
OT_NORMAL, NULL, true);
show_verbose = strchr(cmd, '+') ? true : false;
switch (cmd[1])
{
--- 329,342 ----
else if (cmd[0] == 'd')
{
char *pattern;
! 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);
show_verbose = strchr(cmd, '+') ? true : false;
+ show_system = strchr(cmd, 'S') ? true: false;
switch (cmd[1])
{
***************
*** 345,372 ****
success = describeTableDetails(pattern, show_verbose);
else
/* standard listing of interesting things */
! success = listTables("tvs", NULL, show_verbose);
break;
case 'a':
! success = describeAggregates(pattern, show_verbose);
break;
case 'b':
success = describeTablespaces(pattern, show_verbose);
break;
case 'c':
! success = listConversions(pattern);
break;
case 'C':
success = listCasts(pattern);
break;
case 'd':
! success = objectDescription(pattern);
break;
case 'D':
! success = listDomains(pattern);
break;
case 'f':
! success = describeFunctions(pattern, show_verbose);
break;
case 'g':
/* no longer distinct from \du */
--- 346,373 ----
success = describeTableDetails(pattern, show_verbose);
else
/* standard listing of interesting things */
! success = listTables("tvs", NULL, show_verbose, show_system);
break;
case 'a':
! success = describeAggregates(pattern, show_verbose, show_system);
break;
case 'b':
success = describeTablespaces(pattern, show_verbose);
break;
case 'c':
! success = listConversions(pattern, show_system);
break;
case 'C':
success = listCasts(pattern);
break;
case 'd':
! success = objectDescription(pattern, show_system);
break;
case 'D':
! success = listDomains(pattern, show_system);
break;
case 'f':
! success = describeFunctions(pattern, show_verbose, show_system);
break;
case 'g':
/* no longer distinct from \du */
***************
*** 379,398 ****
success = listSchemas(pattern, show_verbose);
break;
case 'o':
! success = describeOperators(pattern);
break;
case 'p':
success = permissionsList(pattern);
break;
case 'T':
! success = describeTypes(pattern, show_verbose);
break;
case 't':
case 'v':
case 'i':
case 's':
case 'S':
! success = listTables(&cmd[1], pattern, show_verbose);
break;
case 'u':
success = describeRoles(pattern, show_verbose);
--- 380,399 ----
success = listSchemas(pattern, show_verbose);
break;
case 'o':
! success = describeOperators(pattern, show_system);
break;
case 'p':
success = permissionsList(pattern);
break;
case 'T':
! success = describeTypes(pattern, show_verbose, show_system);
break;
case 't':
case 'v':
case 'i':
case 's':
case 'S':
! success = listTables(&cmd[1], pattern, show_verbose, show_system);
break;
case 'u':
success = describeRoles(pattern, show_verbose);
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.186
diff -c -r1.186 describe.c
*** describe.c 3 Nov 2008 19:08:56 -0000 1.186
--- describe.c 7 Nov 2008 20:59:30 -0000
***************
*** 52,58 ****
* Takes an optional regexp to select particular aggregates
*/
bool
! describeAggregates(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 52,58 ----
* Takes an optional regexp to select particular aggregates
*/
bool
! describeAggregates(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 75,81 ****
" ELSE\n"
" pg_catalog.array_to_string(ARRAY(\n"
" SELECT\n"
! " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
" FROM\n"
" pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
" ), ', ')\n"
--- 75,81 ----
" ELSE\n"
" pg_catalog.array_to_string(ARRAY(\n"
" SELECT\n"
! " pg_catalog.format_type(p.proargtypes[s.i], NULL)\n"
" FROM\n"
" pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)\n"
" ), ', ')\n"
***************
*** 93,98 ****
--- 93,101 ----
"WHERE p.proisagg\n",
gettext_noop("Description"));
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 179,185 ****
* Takes an optional regexp to select particular functions
*/
bool
! describeFunctions(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 182,188 ----
* Takes an optional regexp to select particular functions
*/
bool
! describeFunctions(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 275,280 ****
--- 278,286 ----
" AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype\n"
" AND NOT p.proisagg\n");
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 303,309 ****
* describe types
*/
bool
! describeTypes(const char *pattern, bool verbose)
{
PQExpBufferData buf;
PGresult *res;
--- 309,315 ----
* describe types
*/
bool
! describeTypes(const char *pattern, bool verbose, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 363,368 ****
--- 369,377 ----
else
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname",
***************
*** 390,396 ****
/* \do
*/
bool
! describeOperators(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 399,405 ----
/* \do
*/
bool
! describeOperators(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 415,421 ****
gettext_noop("Result type"),
gettext_noop("Description"));
! processSQLNamePattern(pset.db, &buf, pattern, false, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 424,433 ----
gettext_noop("Result type"),
gettext_noop("Description"));
! if (!showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 584,590 ****
* lists of things, there are other \d? commands.
*/
bool
! objectDescription(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 596,602 ----
* lists of things, there are other \d? commands.
*/
bool
! objectDescription(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 611,616 ****
--- 623,632 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n"
" WHERE p.proisagg\n",
gettext_noop("aggregate"));
+
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 630,635 ****
--- 646,655 ----
" OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)\n"
" AND NOT p.proisagg\n",
gettext_noop("function"));
+
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname !~ 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)");
***************
*** 644,650 ****
" FROM pg_catalog.pg_operator o\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
gettext_noop("operator"));
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
--- 664,674 ----
" FROM pg_catalog.pg_operator o\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = o.oprnamespace\n",
gettext_noop("operator"));
!
! if (!showSystem)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "o.oprname", NULL,
"pg_catalog.pg_operator_is_visible(o.oid)");
***************
*** 658,664 ****
" FROM pg_catalog.pg_type t\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace\n",
gettext_noop("data type"));
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
--- 682,692 ----
" 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)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
!
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
***************
*** 679,684 ****
--- 707,715 ----
gettext_noop("view"),
gettext_noop("index"),
gettext_noop("sequence"));
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 695,700 ****
--- 726,735 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
" WHERE r.rulename != '_RETURN'\n",
gettext_noop("rule"));
+
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "r.rulename", NULL,
***************
*** 711,718 ****
" JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, false, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
--- 746,756 ----
" JOIN pg_catalog.pg_class c ON c.oid = t.tgrelid\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
/* XXX not sure what to do about visibility rule here? */
! processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "t.tgname", NULL,
"pg_catalog.pg_table_is_visible(c.oid)");
***************
*** 1840,1852 ****
* (any order of the above is fine)
*/
bool
! listTables(const char *tabtypes, const char *pattern, bool verbose)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
bool showViews = strchr(tabtypes, 'v') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
- bool showSystem = strchr(tabtypes, 'S') != NULL;
PQExpBufferData buf;
PGresult *res;
--- 1878,1889 ----
* (any order of the above is fine)
*/
bool
! listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem)
{
bool showTables = strchr(tabtypes, 't') != NULL;
bool showIndexes = strchr(tabtypes, 'i') != NULL;
bool showViews = strchr(tabtypes, 'v') != NULL;
bool showSeq = strchr(tabtypes, 's') != NULL;
PQExpBufferData buf;
PGresult *res;
***************
*** 1965,1971 ****
* Describes domains.
*/
bool
! listDomains(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 2002,2008 ----
* Describes domains.
*/
bool
! listDomains(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 1993,1998 ****
--- 2030,2038 ----
gettext_noop("Modifier"),
gettext_noop("Check"));
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname <> 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname", NULL,
"pg_catalog.pg_type_is_visible(t.oid)");
***************
*** 2020,2026 ****
* Describes conversions.
*/
bool
! listConversions(const char *pattern)
{
PQExpBufferData buf;
PGresult *res;
--- 2060,2066 ----
* Describes conversions.
*/
bool
! listConversions(const char *pattern, bool showSystem)
{
PQExpBufferData buf;
PGresult *res;
***************
*** 2045,2050 ****
--- 2085,2093 ----
gettext_noop("yes"), gettext_noop("no"),
gettext_noop("Default?"));
+ if (!showSystem)
+ appendPQExpBuffer(&buf, " AND n.nspname !~ 'pg_catalog'\n");
+
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.conname", NULL,
"pg_catalog.pg_conversion_is_visible(c.oid)");
Index: describe.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.h,v
retrieving revision 1.35
diff -c -r1.35 describe.h
*** describe.h 1 Jan 2008 19:45:56 -0000 1.35
--- describe.h 7 Nov 2008 20:59:30 -0000
***************
*** 10,28 ****
/* \da */
! extern bool describeAggregates(const char *pattern, bool verbose);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df */
! extern bool describeFunctions(const char *pattern, bool verbose);
/* \dT */
! extern bool describeTypes(const char *pattern, bool verbose);
/* \do */
! extern bool describeOperators(const char *pattern);
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
--- 10,28 ----
/* \da */
! extern bool describeAggregates(const char *pattern, bool verbose, bool showSystem);
/* \db */
extern bool describeTablespaces(const char *pattern, bool verbose);
/* \df */
! extern bool describeFunctions(const char *pattern, bool verbose, bool showSystem);
/* \dT */
! extern bool describeTypes(const char *pattern, bool verbose, bool showSystem);
/* \do */
! extern bool describeOperators(const char *pattern, bool showSystem);
/* \du, \dg */
extern bool describeRoles(const char *pattern, bool verbose);
***************
*** 31,37 ****
extern bool permissionsList(const char *pattern);
/* \dd */
! extern bool objectDescription(const char *pattern);
/* \d foo */
extern bool describeTableDetails(const char *pattern, bool verbose);
--- 31,37 ----
extern bool permissionsList(const char *pattern);
/* \dd */
! extern bool objectDescription(const char *pattern, bool showSystem);
/* \d foo */
extern bool describeTableDetails(const char *pattern, bool verbose);
***************
*** 52,64 ****
extern bool listAllDbs(bool verbose);
/* \dt, \di, \ds, \dS, etc. */
! extern bool listTables(const char *tabtypes, const char *pattern, bool verbose);
/* \dD */
! extern bool listDomains(const char *pattern);
/* \dc */
! extern bool listConversions(const char *pattern);
/* \dC */
extern bool listCasts(const char *pattern);
--- 52,64 ----
extern bool listAllDbs(bool verbose);
/* \dt, \di, \ds, \dS, etc. */
! extern bool listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSystem);
/* \dD */
! extern bool listDomains(const char *pattern, bool showSystem);
/* \dc */
! extern bool listConversions(const char *pattern, bool showSystem);
/* \dC */
extern bool listCasts(const char *pattern);
Index: help.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/help.c,v
retrieving revision 1.130
diff -c -r1.130 help.c
*** help.c 29 Aug 2008 15:52:07 -0000 1.130
--- help.c 7 Nov 2008 20:59:30 -0000
***************
*** 194,221 ****
fprintf(output, "\n");
fprintf(output, _("Informational\n"));
! fprintf(output, _(" \\d [NAME] describe table, index, sequence, or view\n"));
! fprintf(output, _(" \\d{t|i|s|v|S} [PATTERN] (add \"+\" for more detail)\n"
! " list tables/indexes/sequences/views/system tables\n"));
! fprintf(output, _(" \\da [PATTERN] list aggregate functions\n"));
! fprintf(output, _(" \\db [PATTERN] list tablespaces (add \"+\" for more detail)\n"));
! fprintf(output, _(" \\dc [PATTERN] list conversions\n"));
fprintf(output, _(" \\dC list casts\n"));
fprintf(output, _(" \\dd [PATTERN] show comment for object\n"));
! fprintf(output, _(" \\dD [PATTERN] list domains\n"));
! fprintf(output, _(" \\df [PATTERN] list functions (add \"+\" for more detail)\n"));
! fprintf(output, _(" \\dF [PATTERN] list text search configurations (add \"+\" for more detail)\n"));
! fprintf(output, _(" \\dFd [PATTERN] list text search dictionaries (add \"+\" for more detail)\n"));
fprintf(output, _(" \\dFt [PATTERN] list text search templates\n"));
! fprintf(output, _(" \\dFp [PATTERN] list text search parsers (add \"+\" for more detail)\n"));
fprintf(output, _(" \\dg [PATTERN] list roles (groups)\n"));
! fprintf(output, _(" \\dn [PATTERN] list schemas (add \"+\" for more detail)\n"));
! fprintf(output, _(" \\do [NAME] list operators\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
- fprintf(output, _(" \\dT [PATTERN] list data types (add \"+\" for more detail)\n"));
fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
- fprintf(output, _(" \\l list all databases (add \"+\" for more detail)\n"));
fprintf(output, _(" \\z [PATTERN] list table, view, and sequence access privileges (same as \\dp)\n"));
fprintf(output, "\n");
--- 194,227 ----
fprintf(output, "\n");
fprintf(output, _("Informational\n"));
! fprintf(output, _(" Modifiers: S = show system objects + = Additional detail\n"));
! fprintf(output, _(" \\l[+] list all databases\n"));
! fprintf(output, _(" \\d[S] list tables, views, and sequences\n"));
! fprintf(output, _(" \\d[S] NAME describe table, view, sequence, or index\n"));
! fprintf(output, _(" \\dt[S+] [PATTERN] list tables\n"));
! fprintf(output, _(" \\dv[S+] [PATTERN] list views\n"));
! fprintf(output, _(" \\ds[S+] [PATTERN] list sequences\n"));
! fprintf(output, _(" \\di[S+] [PATTERN] list indexes\n"));
! fprintf(output, _(" \\df[S+] [PATTERN] list functions\n"));
! fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n"));
! fprintf(output, _(" \\dd[S] [PATTERN] list comments on objects\n"));
! fprintf(output, _(" \\dD[S] [PATTERN] list domains\n"));
! fprintf(output, _(" \\do[S] [PATTERN] list operators\n"));
! fprintf(output, _(" \\da[S] [PATTERN] list aggregate functions\n"));
! fprintf(output, _(" \\dc[S] [PATTERN] list conversions\n"));
! fprintf(output, _(" \\db[+] [PATTERN] list tablespaces\n"));
! fprintf(output, _(" \\dn[+] [PATTERN] list schemas\n"));
fprintf(output, _(" \\dC list casts\n"));
fprintf(output, _(" \\dd [PATTERN] show comment for object\n"));
! fprintf(output, _(" \\dF[+] [PATTERN] list text search configurations\n"));
! fprintf(output, _(" \\dFd[+] [PATTERN] list text search dictionaries\n"));
fprintf(output, _(" \\dFt [PATTERN] list text search templates\n"));
! fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));
fprintf(output, _(" \\dg [PATTERN] list roles (groups)\n"));
! fprintf(output, _(" \\dn[+] [PATTERN]\n"));
fprintf(output, _(" \\dl list large objects, same as \\lo_list\n"));
fprintf(output, _(" \\dp [PATTERN] list table, view, and sequence access privileges\n"));
fprintf(output, _(" \\du [PATTERN] list roles (users)\n"));
fprintf(output, _(" \\z [PATTERN] list table, view, and sequence access privileges (same as \\dp)\n"));
fprintf(output, "\n");
signature.asc
Description: PGP signature
