Re: Showing index details with \d on psql - Mailing list pgsql-patches

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> Please use "(primary key)" and add a colon after "Indexes".

Done and done. I capitalized and used brackets to make it
stand out more from the index contents, but it is now changed
back.

> I don't like the '*' things.  They look ugly and convey little
> real information.

They convey "this column is indexed" and also indicate in how many
indexes it appears.

Anyway, revised patch attached.

Revised sample output:

                          Table "b1"
 Column |           Type           |        Modifiers
- --------+--------------------------+--------------------------
 post   | integer                  | *
 thread | smallint                 | not null *
 reply  | smallint                 | not null *
 stat   | character varying(1)     | not null default 'N' * *
 uid    | integer                  | not null
 ctime  | timestamp with time zone | default 'now'
 atime  | timestamp with time zone |
Indexes: abc (post, reply, stat)
         def (stat)
         foo_pkey (primary key) (thread)


Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110131806


-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO8i8YrybkGcUlkrIEQI1ngCcCtzjcBB8hMrBsAh+wbSsExJCpUYAoP4E
FWCa51FCDi1BP8zTgdujnDlu
=4P3T
-----END PGP SIGNATURE-----
*** ./src/bin/psql/describe.c.orig    Sat Oct 13 10:13:10 2001
--- ./src/bin/psql/describe.c    Sat Oct 13 11:17:27 2001
***************
*** 707,718 ****
          PGresult        *result1 = NULL,
                          *result2 = NULL,
                          *result3 = NULL,
!                         *result4 = NULL,
!                         *result5 = NULL,
!                         *result6 = NULL;
          int            index_count = 0,
-                         primary_count = 0,
-                         unique_count = 0,
                          constr_count = 0,
                          rule_count = 0,
                          trigger_count = 0;
--- 707,714 ----
          PGresult        *result1 = NULL,
                          *result2 = NULL,
                          *result3 = NULL,
!                         *result4 = NULL;
          int            index_count = 0,
                          constr_count = 0,
                          rule_count = 0,
                          trigger_count = 0;
***************
*** 721,730 ****
          /* count indexes */
          if (!error && tableinfo.hasindex)
          {
!             sprintf(buf, "SELECT c2.relname\n"
                      "FROM pg_class c, pg_class c2, pg_index i\n"
                      "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!                "AND NOT i.indisunique ORDER BY c2.relname",
                      name);
              result1 = PSQLexec(buf);
              if (!result1)
--- 717,726 ----
          /* count indexes */
          if (!error && tableinfo.hasindex)
          {
!             sprintf(buf, "SELECT c2.relname, i.indkey, i.indisprimary, i.indisunique\n"
                      "FROM pg_class c, pg_class c2, pg_index i\n"
                      "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
!                "ORDER BY c2.relname",
                      name);
              result1 = PSQLexec(buf);
              if (!result1)
***************
*** 733,774 ****
                  index_count = PQntuples(result1);
          }

-         /* count primary keys */
-         if (!error && tableinfo.hasindex)
-         {
-             sprintf(buf, "SELECT c2.relname\n"
-                     "FROM pg_class c, pg_class c2, pg_index i\n"
-                     "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
-                "AND i.indisprimary AND i.indisunique ORDER BY c2.relname",
-                     name);
-             result5 = PSQLexec(buf);
-             if (!result5)
-                 error = true;
-             else
-                 primary_count = PQntuples(result5);
-         }
-
-         /* count unique constraints */
-         if (!error && tableinfo.hasindex)
-         {
-             sprintf(buf, "SELECT c2.relname\n"
-                     "FROM pg_class c, pg_class c2, pg_index i\n"
-                     "WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
-                "AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname",
-                     name);
-             result6 = PSQLexec(buf);
-             if (!result6)
-                 error = true;
-             else
-                 unique_count = PQntuples(result6);
-         }
-
          /* count table (and column) constraints */
          if (!error && tableinfo.checks)
          {
              sprintf(buf, "SELECT rcsrc, rcname\n"
                      "FROM pg_relcheck r, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = r.rcrelid",
                      name);
              result2 = PSQLexec(buf);
              if (!result2)
--- 729,741 ----
                  index_count = PQntuples(result1);
          }

          /* count table (and column) constraints */
          if (!error && tableinfo.checks)
          {
              sprintf(buf, "SELECT rcsrc, rcname\n"
                      "FROM pg_relcheck r, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = r.rcrelid\n"
!                     "ORDER BY rcname",
                      name);
              result2 = PSQLexec(buf);
              if (!result2)
***************
*** 783,789 ****
              sprintf(buf,
                      "SELECT r.rulename\n"
                      "FROM pg_rewrite r, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = r.ev_class",
                      name);
              result3 = PSQLexec(buf);
              if (!result3)
--- 750,757 ----
              sprintf(buf,
                      "SELECT r.rulename\n"
                      "FROM pg_rewrite r, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = r.ev_class\n"
!                     "ORDER BY r.rulename",
                      name);
              result3 = PSQLexec(buf);
              if (!result3)
***************
*** 798,804 ****
              sprintf(buf,
                      "SELECT t.tgname\n"
                      "FROM pg_trigger t, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = t.tgrelid",
                      name);
              result4 = PSQLexec(buf);
              if (!result4)
--- 766,773 ----
              sprintf(buf,
                      "SELECT t.tgname\n"
                      "FROM pg_trigger t, pg_class c\n"
!                     "WHERE c.relname='%s' AND c.oid = t.tgrelid\n"
!                     "ORDER BY t.tgname",
                      name);
              result4 = PSQLexec(buf);
              if (!result4)
***************
*** 807,858 ****
                  trigger_count = PQntuples(result4);
          }

!         footers = xmalloc((index_count + primary_count + unique_count +
!                                     constr_count + rule_count + trigger_count + 1)
!                                     * sizeof(*footers));

          /* print indexes */
          for (i = 0; i < index_count; i++)
          {
              char   *s = _("Indexes");

!             if (i == 0)
!                 snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result1, i, 0));
!             else
!                 snprintf(buf, sizeof(buf), "%*s  %s", (int)strlen(s), "", PQgetvalue(result1, i, 0));
!             if (i < index_count - 1)
!                 strcat(buf, ",");
!
!             footers[count_footers++] = xstrdup(buf);
!         }
!
!         /* print primary keys */
!         for (i = 0; i < primary_count; i++)
!         {
!             char   *s = _("Primary key");
!
!             if (i == 0)
!                 snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result5, i, 0));
!             else
!                 snprintf(buf, sizeof(buf), "%*s  %s", (int)strlen(s), "", PQgetvalue(result5, i, 0));
!             if (i < primary_count - 1)
!                 strcat(buf, ",");
!
!             footers[count_footers++] = xstrdup(buf);
!         }
!
!         /* print unique constraints */
!         for (i = 0; i < unique_count; i++)
!         {
!             char   *s = _("Unique keys");
!
!             if (i == 0)
!                 snprintf(buf, sizeof(buf), "%s: %s", s, PQgetvalue(result6, i, 0));
!             else
!                 snprintf(buf, sizeof(buf), "%*s  %s", (int)strlen(s), "", PQgetvalue(result6, i, 0));
!             if (i < unique_count - 1)
!                 strcat(buf, ",");
!
              footers[count_footers++] = xstrdup(buf);
          }

--- 776,816 ----
                  trigger_count = PQntuples(result4);
          }

!         footers = xmalloc((index_count + constr_count + rule_count + trigger_count + 1)
!                           * sizeof(*footers));

          /* print indexes */
          for (i = 0; i < index_count; i++)
          {
              char   *s = _("Indexes:");

!             snprintf(buf, sizeof(buf), "%*s %s%s%s",
!                      (int)strlen(s),
!                      i == 0 ? s : "",
!                      PQgetvalue(result1, i, 0),
!                      strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? _(" (primary key)") : "",
!                      strcmp(PQgetvalue(result1, i, 3), "t") == 0 ? _(" (unique)") : "");
!
!             char indexchar[5]; /* Should be plenty */
!             int indexnumber=0;
!             char * indexlist = PQgetvalue(result1, i, 1);
!             int j,found;
!             for (j=0,found=0;j<=strlen(indexlist); j++) {
!               if (indexlist[j] == 0 || indexlist[j] == 32) {
!                 indexnumber = atoi(indexchar);
!                 if (indexnumber>0) /* pg_class has a -2! */
!                   {
!                   strcat(cells[(indexnumber-1) * cols + 2],
!                          cells[(indexnumber-1) * cols +2][0] ? " *" : "*");
!                   strcat(buf, ++found==1 ? " (" : ", ");
!                   strcat(buf, cells[(indexnumber-1) * cols]);
!                   }
!                 indexchar[0] = '\0';
!                 }
!               else { strcat(indexchar,&indexlist[j]); }
!             }
!             if (found) /* must cover for pg_class again */
!               strcat(buf, ")");
              footers[count_footers++] = xstrdup(buf);
          }

***************
*** 907,914 ****
          PQclear(result2);
          PQclear(result3);
          PQclear(result4);
-         PQclear(result5);
-         PQclear(result6);
      }

      if (!error)
--- 865,870 ----