psql \d commands and information_schema - Mailing list pgsql-hackers

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,