Attached is a patch that modifies psql \dX commands to treat objects
in information_schema as "system objects". This prevents them from
showing up in \dX *.* and polluting the user objects list. This is
especially annoying if user objects are in multiple schemas, and
one wants to get a quick overview by running \dX *.*
regards,
Martin
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***************
*** 23,28 ****
--- 23,34 ----
#include "variables.h"
+ /* Macros for exluding system objects from \d commands */
+ #define NOT_SYSTEM_OBJECT " n.nspname NOT IN ('pg_catalog', 'information_schema')\n"
+ #define AND_NOT_SYSTEM_OBJECT " AND" NOT_SYSTEM_OBJECT
+ #define WHERE_NOT_SYSTEM_OBJECT " WHERE" NOT_SYSTEM_OBJECT
+
+
static bool describeOneTableDetails(const char *schemaname,
const char *relationname,
const char *oid,
***************
*** 95,101 **** describeAggregates(const char *pattern, bool verbose, bool showSystem)
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,
--- 101,107 ----
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 282,288 **** describeFunctions(const char *pattern, bool verbose, bool showSystem)
" 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,
--- 288,294 ----
" AND NOT p.proisagg\n");
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 373,379 **** describeTypes(const char *pattern, bool verbose, bool showSystem)
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,
--- 379,385 ----
appendPQExpBuffer(&buf, " AND t.typname !~ '^_'\n");
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
/* Match name pattern against either internal or external name */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 428,434 **** describeOperators(const char *pattern, bool showSystem)
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
--- 434,440 ----
gettext_noop("Description"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, true,
"n.nspname", "o.oprname", NULL,
***************
*** 632,638 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 638,644 ----
gettext_noop("aggregate"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 655,661 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 661,667 ----
gettext_noop("function"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "p.proname", NULL,
***************
*** 673,679 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 679,685 ----
gettext_noop("operator"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "o.oprname", NULL,
***************
*** 691,697 **** objectDescription(const char *pattern, bool showSystem)
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)",
--- 697,703 ----
gettext_noop("data type"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "pg_catalog.format_type(t.oid, NULL)",
***************
*** 715,721 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 721,727 ----
gettext_noop("index"),
gettext_noop("sequence"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.relname", NULL,
***************
*** 735,741 **** objectDescription(const char *pattern, bool showSystem)
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,
--- 741,747 ----
gettext_noop("rule"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 754,760 **** objectDescription(const char *pattern, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
if (!showSystem)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
--- 760,766 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n",
gettext_noop("trigger"));
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
/* XXX not sure what to do about visibility rule here? */
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
***************
*** 809,815 **** describeTableDetails(const char *pattern, bool verbose, bool showSystem)
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
if (!showSystem)
! appendPQExpBuffer(&buf, " WHERE n.nspname <> 'pg_catalog'\n");
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "c.relname", NULL,
--- 815,821 ----
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n");
if (!showSystem)
! appendPQExpBuffer(&buf, WHERE_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, !showSystem, false,
"n.nspname", "c.relname", NULL,
***************
*** 2018,2024 **** listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
if (!showSystem)
/* Exclude system and pg_toast objects, but show temp tables */
appendPQExpBuffer(&buf,
! " AND n.nspname <> 'pg_catalog'\n"
" AND n.nspname !~ '^pg_toast'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
--- 2024,2030 ----
if (!showSystem)
/* Exclude system and pg_toast objects, but show temp tables */
appendPQExpBuffer(&buf,
! AND_NOT_SYSTEM_OBJECT
" AND n.nspname !~ '^pg_toast'\n");
processSQLNamePattern(pset.db, &buf, pattern, true, false,
***************
*** 2090,2096 **** listDomains(const char *pattern, bool showSystem)
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,
--- 2096,2102 ----
gettext_noop("Check"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "t.typname", NULL,
***************
*** 2145,2151 **** listConversions(const char *pattern, bool showSystem)
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,
--- 2151,2157 ----
gettext_noop("Default?"));
if (!showSystem)
! appendPQExpBuffer(&buf, AND_NOT_SYSTEM_OBJECT);
processSQLNamePattern(pset.db, &buf, pattern, true, false,
"n.nspname", "c.conname", NULL,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
