postgres.git / summary / log / commit / refs

commit    1ac7db4468e1989ea7c775ad786468b7fd3baa4e
Author:   Bruce Momjian <bruce@momjian.us>
Date:     Sat Jul 20 05:57:31 2002 +0000

    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)
    
    Joe Conway


 src/bin/psql/describe.c | 19 +++++++++++--------
 1 file changed, 11 insertions(+), 8 deletions(-)

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index d3c386284b4..d60483870a9 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3,7 +3,7 @@
  *
  * Copyright 2000 by PostgreSQL Global Development Group
  *
- * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.55 2002/07/12 18:43:19 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.56 2002/07/20 05:57:31 momjian Exp $
  */
 #include "postgres_fe.h"
 #include "describe.h"
@@ -1022,9 +1022,10 @@ listTables(const char *infotype, const char *name, bool desc)
 
 	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"), _("table"), _("view"), _("index"), _("sequence"),
+			 _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
 			 _("special"), _("Type"), _("Owner"));
 
 	if (desc)
@@ -1034,14 +1035,16 @@ listTables(const char *infotype, const char *name, bool desc)
     if (showIndexes)
 		appendPQExpBuffer(&buf,
 						  ",\n c2.relname as \"%s\""
-						  "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
+						  "\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\n"
-						  "WHERE c.relowner = u.usesysid\n");
+						  "\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,14 +1061,14 @@ listTables(const char *infotype, const char *name, bool desc)
 	appendPQExpBuffer(&buf, ")\n");
 
 	if (showSystem)
-		appendPQExpBuffer(&buf, "  AND c.relname ~ '^pg_'\n");
+		appendPQExpBuffer(&buf, "  AND n.nspname ~ '^pg_'\n");
 	else
-		appendPQExpBuffer(&buf, "  AND c.relname !~ '^pg_'\n");
+		appendPQExpBuffer(&buf, "  AND n.nspname !~ '^pg_'\n");
 
 	if (name)
 		appendPQExpBuffer(&buf, "  AND c.relname ~ '^%s'\n", name);
 
-	appendPQExpBuffer(&buf, "ORDER BY 1;");
+	appendPQExpBuffer(&buf, "ORDER BY 2,1;");
 
 	res = PSQLexec(buf.data);
 	termPQExpBuffer(&buf);


[parent: 1ce03603ccc6]