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

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

> I like the idea of this but the format, as you say, could use
> improvement.  Can I recommend having a "*" next to the columns
> involved in the index and have the column names listed next to
> the index names? That would look better and be clearer, I think.

Agreed - it's probably more intuitive for someone to ask
"is this column in an index?" and "what does this index cover?"
rather than "which index is this column in?" Attached patch is
against 7.1.2. As a side benfit, it does multiple stars for
columns that belong to multiple indices, which is a nice feature
to be able to spot right away.

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


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

iQA/AwUBO8eqrbybkGcUlkrIEQI+BwCeMkkc2UfyJs+vaJNWS9BFSdZInvAAnjIF
vRuRPuHJWmQ8dqxorK72wLgM
=VdAI
-----END PGP SIGNATURE-----

*** ./src/bin/psql/describe.c.orig    Wed Mar 21 23:00:19 2001
--- ./src/bin/psql/describe.c    Fri Oct 12 22:27:25 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, 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",
***************
*** 810,826 ****
          /* 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 */
          for (i = 0; i < constr_count; i++)
          {
              sprintf(buf, "%s %s",
--- 810,847 ----
          /* print indices */
          for (i = 0; i < index_count; i++)
          {
!             char *indexname, *indexlist;
!             indexname = PQgetvalue(result1, i, 0);
!             sprintf(buf, "%s %s%s%s",
!                     index_count == 1 ? "*Index:  " : (i == 0 ? "*Indices:" : "         "),
!                     indexname,
!                     strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? " [PRIMARY KEY]" : "",
!                     strcmp(PQgetvalue(result1, i, 3), "t") == 0 ? " [UNIQUE]" : ""
                  );
!
!             /* strtokx is overkill here */
!             char indexchar[5]; /* Should be plenty */
!             int indexnumber=0;
!             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]); }
!             }
!             strcat(buf, ")");
              footers[count_footers++] = xstrdup(buf);
          }
!
!            /* print contraints */
          for (i = 0; i < constr_count; i++)
          {
              sprintf(buf, "%s %s",