Showing index details with \d on psql

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

While hacking around with psql, I came up with the idea of
identifying the columns in a table as to which index they
are in (and conversly, showing which columns an index
contains). I find this useful because a normal listing that
only tells you the name of the index is not very helpful
and usually needs a separate \d index_name entry. The only
concern is how to do it:
as a separate "index" column?
appending the name of the index to the Description column?
numbering the indexes and using the number to save space?
(The latter is used in the enclosed patch and
example below). Which columns the index affects follows the
name, and it also tells you if an index is a primary key. Here
is some sample output:

data=> \d mytable
Table "mytable"

                          Table "mytable"
 Attribute |           Type        |         Modifier

- -----------+-----------------------+-----------------------------
 post      | integer               | not null (index #1)
 thread    | smallint              | (index #2) (index #3)
 reply     | smallint              | not null
 subject   | character varying(60) | default 'foo' (index #2)
Indices:   1. mytable_foobar (1) (PRIMARY KEY)
           2. alphabet (4 2)
           3. badname (2)


The numbers at the end of the index names are ugly, but it does
show you instantly the composition and order of the index. I
think once you get used to it, it can be very valuable and
save on calls to \d index_name. My big concern is the size that
each "(index #x)" takes up, but having them separate does make
them stand out more, and in most cases, columns will not belong
to a lot of indices.

The attached (rough) patch is against 7.1.2. Feedback
welcome, as always. :)

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

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

iQA/AwUBO7+rPrybkGcUlkrIEQKz7gCcCyPUDAGGwMbwPa09Rc2pqMbD0cYAn1qY
Yw6/kJdux/vwdN4waU5rdPmH
=/PN6
-----END PGP SIGNATURE-----
*** ./src/bin/psql/describe.c.orig    Wed Mar 21 23:00:19 2001
--- ./src/bin/psql/describe.c    Sat Oct  6 20:46:45 2001
***************
*** 748,754 ****
          /* count indices */
          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"
                      "ORDER BY c2.relname",
--- 748,754 ----
          /* count indices */
          if (!error && tableinfo.hasindex)
          {
!             sprintf(buf, "SELECT c2.relname, i.indkey, i.indisprimary\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",
***************
*** 810,823 ****
          /* print indices */
          for (i = 0; i < index_count; i++)
          {
!             sprintf(buf, "%s %s",
!                     index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : "        "),
!                     PQgetvalue(result1, i, 0)
                  );
-             if (i < index_count - 1)
-                 strcat(buf, ",");

              footers[count_footers++] = xstrdup(buf);
          }

          /* print contraints */
--- 810,845 ----
          /* print indices */
          for (i = 0; i < index_count; i++)
          {
!           char *indexname, *indexlist;
!           indexname = PQgetvalue(result1, i, 0);
!           indexlist = PQgetvalue(result1, i, 1);
!             sprintf(buf, "%s %3d. %s (%s)%s",
!                     index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : "        "),i+1,
!                     indexname,indexlist,
!                     strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? " (PRIMARY KEY)" : ""
                  );

              footers[count_footers++] = xstrdup(buf);
+
+             /* strtokx is overkill here */
+             int j;
+             char dummy[6]; /* Should be plenty */
+             char showindex[10+31];
+             int bar=0;
+             for (j=0; j<=strlen(indexlist); j++) {
+               if (indexlist[j]==0 || indexlist[j]==32) {
+                 bar = atoi(dummy);
+                 if (bar>0) /* pg_class has a -2! */
+                 {
+                     sprintf(showindex, "(index #%d)", i+1);
+                     if (cells[(bar-1) * cols + 2][0])
+                         strcat(cells[(bar-1) * cols + 2], " ");
+                     strcat(cells[(bar-1) * cols + 2], showindex);
+                 }
+                 dummy[0] = '\0';
+               }
+               else { strcat(dummy,&indexlist[j]); }
+             }
          }

          /* print contraints */