Re: Showing index details with \d on psql
| От | Greg Sabino Mullane |
|---|---|
| Тема | Re: Showing index details with \d on psql |
| Дата | |
| Msg-id | 200110132213.SAA01774@mclean.mail.mindspring.net обсуждение исходный текст |
| Ответ на | Showing index details with \d on psql ("Greg Sabino Mullane" <greg@turnstep.com>) |
| Ответы |
Re: Showing index details with \d on psql
Re: Showing index details with \d on psql |
| Список | 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 ----
В списке pgsql-patches по дате отправления: