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

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. Perhaps:

                           Table "mytable"
  Attribute |           Type        |         Modifier

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


---------------------------------------------------------------------------

>
> -----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 */

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026