Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit925d60e

Browse files
committed
Showing index details with \d on psql.
Greg Sabino Mullane
1 parent521017c commit925d60e

File tree

1 file changed

+55
-102
lines changed

1 file changed

+55
-102
lines changed

‎src/bin/psql/describe.c

Lines changed: 55 additions & 102 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright 2000 by PostgreSQL Global Development Group
55
*
6-
* $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.42 2001/11/12 15:57:08 tgl Exp $
6+
* $Header: /cvsroot/pgsql/src/bin/psql/describe.c,v 1.43 2002/03/05 02:42:56 momjian Exp $
77
*/
88
#include"postgres_fe.h"
99
#include"describe.h"
@@ -532,15 +532,23 @@ describeTableDetails(const char *name, bool desc)
532532
headers[cols]=NULL;
533533

534534

535-
/* Get column info */
536-
strcpy(buf,"SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum");
535+
/* Get column info (index requires additional checks) */
536+
if (tableinfo.relkind=='i')
537+
strcpy(buf,"SELECT\n CASE i.indproc WHEN ('-'::regproc) THEN a.attname\n ELSE SUBSTR(pg_get_indexdef(attrelid),\n POSITION('(' in pg_get_indexdef(attrelid)))\n END, ");
538+
else
539+
strcpy(buf,"SELECT a.attname, ");
540+
strcat(buf,"format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum");
537541
if (desc)
538542
strcat(buf,", col_description(a.attrelid, a.attnum)");
539-
strcat(buf,"\nFROM pg_class c, pg_attribute a\n"
540-
"WHERE c.relname = '");
543+
strcat(buf,"\nFROM pg_class c, pg_attribute a");
544+
if (tableinfo.relkind=='i')
545+
strcat(buf,", pg_index i");
546+
strcat(buf,"\nWHERE c.relname = '");
541547
strncat(buf,name,NAMEDATALEN);
542-
strcat(buf,"'\n AND a.attnum > 0 AND a.attrelid = c.oid\n"
543-
"ORDER BY a.attnum");
548+
strcat(buf,"'\n AND a.attnum > 0 AND a.attrelid = c.oid");
549+
if (tableinfo.relkind=='i')
550+
strcat(buf," AND a.attrelid = i.indexrelid");
551+
strcat(buf,"\nORDER BY a.attnum");
544552

545553
res=PSQLexec(buf);
546554
if (!res)
@@ -651,11 +659,11 @@ describeTableDetails(const char *name, bool desc)
651659
{
652660
/* Footer information about an index */
653661
PGresult*result;
654-
655-
sprintf(buf,"SELECT i.indisunique, i.indisprimary, a.amname,\n"
656-
" pg_get_expr(i.indpred, i.indrelid) as indpred\n"
657-
"FROM pg_index i, pg_class c, pg_am a\n"
658-
"WHERE i.indexrelid =c.oid AND c.relname = '%s' AND c.relam = a.oid",
662+
sprintf(buf,"SELECT i.indisunique, i.indisprimary, a.amname, c2.relname,\n"
663+
"pg_get_expr(i.indpred,i.indrelid)\n"
664+
"FROM pg_index i, pg_class c, pg_class c2, pg_am a\n"
665+
"WHERE i.indexrelid = c.oid AND c.relname = '%s' AND c.relam = a.oid\n"
666+
"AND i.indrelid =c2.oid",
659667
name);
660668

661669
result=PSQLexec(buf);
@@ -666,27 +674,18 @@ describeTableDetails(const char *name, bool desc)
666674
char*indisunique=PQgetvalue(result,0,0);
667675
char*indisprimary=PQgetvalue(result,0,1);
668676
char*indamname=PQgetvalue(result,0,2);
669-
char*indpred=PQgetvalue(result,0,3);
677+
char*indtable=PQgetvalue(result,0,3);
678+
char*indpred=PQgetvalue(result,0,4);
670679

671-
footers=xmalloc(3*sizeof(*footers));
680+
footers=xmalloc(2*sizeof(*footers));
672681
/* XXX This construction is poorly internationalized. */
673-
footers[0]=xmalloc(NAMEDATALEN+128);
674-
snprintf(footers[0],NAMEDATALEN+128,"%s%s",
682+
footers[0]=xmalloc(NAMEDATALEN*4+128);
683+
snprintf(footers[0],NAMEDATALEN*4+128,"%s%s for %s \"%s\"%s%s",
684+
strcmp(indisprimary,"t")==0 ?_("primary key ") :
675685
strcmp(indisunique,"t")==0 ?_("unique ") :"",
676-
indamname);
677-
if (strcmp(indisprimary,"t")==0)
678-
snprintf(footers[0]+strlen(footers[0]),
679-
NAMEDATALEN+128-strlen(footers[0]),
680-
_(" (primary key)"));
681-
if (strlen(indpred)>0)
682-
{
683-
footers[1]=xmalloc(64+strlen(indpred));
684-
snprintf(footers[1],64+strlen(indpred),
685-
_("Index predicate: %s"),indpred);
686-
footers[2]=NULL;
687-
}
688-
else
689-
footers[1]=NULL;
686+
indamname,_("table"),indtable,
687+
strlen(indpred) ?" WHERE " :"",indpred);
688+
footers[1]=NULL;
690689
}
691690

692691
PQclear(result);
@@ -706,12 +705,8 @@ describeTableDetails(const char *name, bool desc)
706705
PGresult*result1=NULL,
707706
*result2=NULL,
708707
*result3=NULL,
709-
*result4=NULL,
710-
*result5=NULL,
711-
*result6=NULL;
708+
*result4=NULL;
712709
intindex_count=0,
713-
primary_count=0,
714-
unique_count=0,
715710
constr_count=0,
716711
rule_count=0,
717712
trigger_count=0;
@@ -720,10 +715,12 @@ describeTableDetails(const char *name, bool desc)
720715
/* count indexes */
721716
if (!error&&tableinfo.hasindex)
722717
{
723-
sprintf(buf,"SELECT c2.relname\n"
718+
sprintf(buf,"SELECT c2.relname, i.indisprimary, i.indisunique,\n"
719+
"SUBSTR(pg_get_indexdef(i.indexrelid),\n"
720+
"POSITION('USING ' IN pg_get_indexdef(i.indexrelid))+5)\n"
724721
"FROM pg_class c, pg_class c2, pg_index i\n"
725722
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
726-
"AND NOT i.indisunique ORDER BY c2.relname",
723+
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
727724
name);
728725
result1=PSQLexec(buf);
729726
if (!result1)
@@ -732,36 +729,6 @@ describeTableDetails(const char *name, bool desc)
732729
index_count=PQntuples(result1);
733730
}
734731

735-
/* count primary keys */
736-
if (!error&&tableinfo.hasindex)
737-
{
738-
sprintf(buf,"SELECT c2.relname\n"
739-
"FROM pg_class c, pg_class c2, pg_index i\n"
740-
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
741-
"AND i.indisprimary AND i.indisunique ORDER BY c2.relname",
742-
name);
743-
result5=PSQLexec(buf);
744-
if (!result5)
745-
error= true;
746-
else
747-
primary_count=PQntuples(result5);
748-
}
749-
750-
/* count unique constraints */
751-
if (!error&&tableinfo.hasindex)
752-
{
753-
sprintf(buf,"SELECT c2.relname\n"
754-
"FROM pg_class c, pg_class c2, pg_index i\n"
755-
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
756-
"AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname",
757-
name);
758-
result6=PSQLexec(buf);
759-
if (!result6)
760-
error= true;
761-
else
762-
unique_count=PQntuples(result6);
763-
}
764-
765732
/* count table (and column) constraints */
766733
if (!error&&tableinfo.checks)
767734
{
@@ -806,54 +773,32 @@ describeTableDetails(const char *name, bool desc)
806773
trigger_count=PQntuples(result4);
807774
}
808775

809-
footers=xmalloc((index_count+primary_count+unique_count+
810-
constr_count+rule_count+trigger_count+1)
776+
footers=xmalloc((index_count+constr_count+rule_count+trigger_count+1)
811777
*sizeof(*footers));
812778

813779
/* print indexes */
814780
for (i=0;i<index_count;i++)
815781
{
816782
char*s=_("Indexes");
817-
783+
818784
if (i==0)
819785
snprintf(buf,sizeof(buf),"%s: %s",s,PQgetvalue(result1,i,0));
820786
else
821787
snprintf(buf,sizeof(buf),"%*s %s", (int)strlen(s),"",PQgetvalue(result1,i,0));
822-
if (i<index_count-1)
823-
strcat(buf,",");
824788

825-
footers[count_footers++]=xstrdup(buf);
826-
}
789+
/* Label as primary key or unique (but not both) */
790+
strcat(buf,strcmp(PQgetvalue(result1,i,1),"t")==0 ?
791+
_(" primary key") :strcmp(PQgetvalue(result1,i,2),"t")==0 ?_(" unique") :"");
827792

828-
/* print primary keys */
829-
for (i=0;i<primary_count;i++)
830-
{
831-
char*s=_("Primary key");
793+
/* Everything after "USING" is echoed verbatim */
794+
strcat(buf,PQgetvalue(result1,i,3));
832795

833-
if (i==0)
834-
snprintf(buf,sizeof(buf),"%s: %s",s,PQgetvalue(result5,i,0));
835-
else
836-
snprintf(buf,sizeof(buf),"%*s %s", (int)strlen(s),"",PQgetvalue(result5,i,0));
837-
if (i<primary_count-1)
796+
if (i<index_count-1)
838797
strcat(buf,",");
839798

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

843-
/* print unique constraints */
844-
for (i=0;i<unique_count;i++)
845-
{
846-
char*s=_("Unique keys");
847-
848-
if (i==0)
849-
snprintf(buf,sizeof(buf),"%s: %s",s,PQgetvalue(result6,i,0));
850-
else
851-
snprintf(buf,sizeof(buf),"%*s %s", (int)strlen(s),"",PQgetvalue(result6,i,0));
852-
if (i<unique_count-1)
853-
strcat(buf,",");
854-
855-
footers[count_footers++]=xstrdup(buf);
856-
}
857802

858803
/* print constraints */
859804
for (i=0;i<constr_count;i++)
@@ -906,8 +851,6 @@ describeTableDetails(const char *name, bool desc)
906851
PQclear(result2);
907852
PQclear(result3);
908853
PQclear(result4);
909-
PQclear(result5);
910-
PQclear(result6);
911854
}
912855

913856
if (!error)
@@ -1031,9 +974,19 @@ listTables(const char *infotype, const char *name, bool desc)
1031974
snprintf(buf+strlen(buf),sizeof(buf)-strlen(buf),
1032975
",\n obj_description(c.oid, 'pg_class') as \"%s\"",
1033976
_("Description"));
1034-
strcat(buf,
1035-
"\nFROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid\n"
1036-
"WHERE c.relkind IN (");
977+
if (showIndexes) {
978+
snprintf(buf+strlen(buf),sizeof(buf)-strlen(buf),
979+
",\n c2.relname as \"%s\"",
980+
_("Table"));
981+
strcat(buf,"\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
982+
"WHERE c.relowner = u.usesysid\n"
983+
"AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n");
984+
}
985+
else {
986+
strcat(buf,"\nFROM pg_class c, pg_user u\n"
987+
"WHERE c.relowner = u.usesysid\n");
988+
}
989+
strcat(buf,"AND c.relkind IN (");
1037990
if (showTables)
1038991
strcat(buf,"'r',");
1039992
if (showViews)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp