small psql patch - show Schema name for \dt \dv \dS - Mailing list pgsql-patches

I can't remember who said they were working on schema related psql
changes, but I kept finding myself wishing I could see what schema a
table or view exists in when I use \dt, \dv, etc. So, here is a patch
which does just that.

It sorts on "Schema" first, and "Name" second.

It also changes the test for system objects to key off the namespace
name starting with 'pg_' instead of the object name.

Sample output:

test=# create schema testschema;
CREATE SCHEMA
test=# create view testschema.ts_view as select 1;
CREATE VIEW
test=# \dv
                  List of relations
         Name        |   Schema   | Type |  Owner
--------------------+------------+------+----------
  __testpassbyval    | public     | view | postgres
  fooview            | public     | view | postgres
  master_pg_proc     | public     | view | postgres
  rmt_pg_proc        | public     | view | postgres
  vw_dblink_get_pkey | public     | view | postgres
  vw_dblink_replace  | public     | view | postgres
  ts_view            | testschema | view | postgres
(7 rows)

If there are no objections, please apply.

Thanks!

Joe

Index: src/bin/psql/describe.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.55
diff -c -r1.55 describe.c
*** src/bin/psql/describe.c    12 Jul 2002 18:43:19 -0000    1.55
--- src/bin/psql/describe.c    18 Jul 2002 21:53:52 -0000
***************
*** 1022,1030 ****

      printfPQExpBuffer(&buf,
               "SELECT c.relname as \"%s\",\n"
               "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n" 
               "  u.usename as \"%s\"",
!              _("Name"), _("table"), _("view"), _("index"), _("sequence"),
               _("special"), _("Type"), _("Owner"));

      if (desc)
--- 1022,1031 ----

      printfPQExpBuffer(&buf,
               "SELECT c.relname as \"%s\",\n"
+              "  n.nspname as \"%s\",\n"
               "  CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n" 
               "  u.usename as \"%s\"",
!              _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
               _("special"), _("Type"), _("Owner"));

      if (desc)
***************
*** 1034,1047 ****
      if (showIndexes)
          appendPQExpBuffer(&buf,
                            ",\n c2.relname as \"%s\""
!                           "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
                            "WHERE c.relowner = u.usesysid\n"
                            "AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
                            _("Table"));
      else
          appendPQExpBuffer(&buf,
!                           "\nFROM pg_class c, pg_user u\n"
!                           "WHERE c.relowner = u.usesysid\n");

      appendPQExpBuffer(&buf, "AND c.relkind IN (");
      if (showTables)
--- 1035,1050 ----
      if (showIndexes)
          appendPQExpBuffer(&buf,
                            ",\n c2.relname as \"%s\""
!                           "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u, pg_namespace n\n"
                            "WHERE c.relowner = u.usesysid\n"
+                           "AND c.relnamespace = n.oid\n"
                            "AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
                            _("Table"));
      else
          appendPQExpBuffer(&buf,
!                           "\nFROM pg_class c, pg_user u, pg_namespace n\n"
!                           "WHERE c.relowner = u.usesysid\n"
!                           "AND c.relnamespace = n.oid\n");

      appendPQExpBuffer(&buf, "AND c.relkind IN (");
      if (showTables)
***************
*** 1058,1071 ****
      appendPQExpBuffer(&buf, ")\n");

      if (showSystem)
!         appendPQExpBuffer(&buf, "  AND c.relname ~ '^pg_'\n");
      else
!         appendPQExpBuffer(&buf, "  AND c.relname !~ '^pg_'\n");

      if (name)
          appendPQExpBuffer(&buf, "  AND c.relname ~ '^%s'\n", name);

!     appendPQExpBuffer(&buf, "ORDER BY 1;");

      res = PSQLexec(buf.data);
      termPQExpBuffer(&buf);
--- 1061,1074 ----
      appendPQExpBuffer(&buf, ")\n");

      if (showSystem)
!         appendPQExpBuffer(&buf, "  AND n.nspname ~ '^pg_'\n");
      else
!         appendPQExpBuffer(&buf, "  AND n.nspname !~ '^pg_'\n");

      if (name)
          appendPQExpBuffer(&buf, "  AND c.relname ~ '^%s'\n", name);

!     appendPQExpBuffer(&buf, "ORDER BY 2,1;");

      res = PSQLexec(buf.data);
      termPQExpBuffer(&buf);