Showing index details with \d on psql - Mailing list pgsql-patches
From | Greg Sabino Mullane |
---|---|
Subject | Showing index details with \d on psql |
Date | |
Msg-id | 200110070109.VAA10810@smtp6.mindspring.com Whole thread Raw |
Responses |
Re: Showing index details with \d on psql
|
List | pgsql-patches |
-----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 */
pgsql-patches by date: