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

Commit90371a2

Browse files
committed
Improve psql's \d command to show whether index columns are key columns.
This is essential information when looking at an index that has"included" columns. Per discussion, follow the style used in \dCand some other places: column header is "Key?" and values are "yes"or "no" (all translatable).While at it, revise describeOneTableDetails to be a bit more maintainable:avoid hard-wired column numbers and multiple repetitions of what needsto be identical test logic. This also results in the emitted catalogquery corresponding more closely to what we print, which should be abenefit to users of ECHO_HIDDEN mode, and perhaps a bit faster too(the old logic sometimes asked for values it would not print, evenones that are fairly expensive to get).Discussion:https://postgr.es/m/21724.1531943735@sss.pgh.pa.us
1 parent028e3da commit90371a2

File tree

5 files changed

+224
-195
lines changed

5 files changed

+224
-195
lines changed

‎src/bin/psql/describe.c

Lines changed: 149 additions & 120 deletions
Original file line numberDiff line numberDiff line change
@@ -1410,6 +1410,7 @@ describeOneTableDetails(const char *schemaname,
14101410
constchar*oid,
14111411
boolverbose)
14121412
{
1413+
boolretval= false;
14131414
PQExpBufferDatabuf;
14141415
PGresult*res=NULL;
14151416
printTableOptmyopt=pset.popt.topt;
@@ -1421,7 +1422,19 @@ describeOneTableDetails(const char *schemaname,
14211422
PQExpBufferDatatitle;
14221423
PQExpBufferDatatmpbuf;
14231424
intcols;
1424-
intnumrows=0;
1425+
intattname_col=-1,/* column indexes in "res" */
1426+
atttype_col=-1,
1427+
attrdef_col=-1,
1428+
attnotnull_col=-1,
1429+
attcoll_col=-1,
1430+
attidentity_col=-1,
1431+
isindexkey_col=-1,
1432+
indexdef_col=-1,
1433+
fdwopts_col=-1,
1434+
attstorage_col=-1,
1435+
attstattarget_col=-1,
1436+
attdescr_col=-1;
1437+
intnumrows;
14251438
struct
14261439
{
14271440
int16checks;
@@ -1439,9 +1452,6 @@ describeOneTableDetails(const char *schemaname,
14391452
charrelreplident;
14401453
}tableinfo;
14411454
boolshow_column_details= false;
1442-
boolretval;
1443-
1444-
retval= false;
14451455

14461456
myopt.default_footer= false;
14471457
/* This output looks confusing in expanded mode. */
@@ -1720,42 +1730,88 @@ describeOneTableDetails(const char *schemaname,
17201730
gotoerror_return;/* not an error, just return early */
17211731
}
17221732

1733+
/* Identify whether we should print collation, nullable, default vals */
1734+
if (tableinfo.relkind==RELKIND_RELATION||
1735+
tableinfo.relkind==RELKIND_VIEW||
1736+
tableinfo.relkind==RELKIND_MATVIEW||
1737+
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1738+
tableinfo.relkind==RELKIND_COMPOSITE_TYPE||
1739+
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1740+
show_column_details= true;
1741+
17231742
/*
1724-
* Get column info
1743+
* Getper-column info
17251744
*
1726-
* You need to modify value of "firstvcol" which will be defined below if
1727-
* you are adding column(s) preceding to verbose-only columns.
1745+
* Since the set of query columns we need varies depending on relkind and
1746+
* server version, we compute all the column numbers on-the-fly. Column
1747+
* number variables for columns not fetched are left as -1; this avoids
1748+
* duplicative test logic below.
17281749
*/
1729-
printfPQExpBuffer(&buf,"SELECT a.attname,");
1730-
appendPQExpBufferStr(&buf,"\n pg_catalog.format_type(a.atttypid, a.atttypmod),"
1731-
"\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1732-
"\n FROM pg_catalog.pg_attrdef d"
1733-
"\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
1734-
"\n a.attnotnull, a.attnum,");
1735-
if (pset.sversion >=90100)
1736-
appendPQExpBufferStr(&buf,"\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1737-
" WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1738-
else
1739-
appendPQExpBufferStr(&buf,"\n NULL AS attcollation");
1740-
if (pset.sversion >=100000)
1741-
appendPQExpBufferStr(&buf,",\n a.attidentity");
1742-
else
1743-
appendPQExpBufferStr(&buf,",\n ''::pg_catalog.char AS attidentity");
1750+
cols=0;
1751+
printfPQExpBuffer(&buf,"SELECT a.attname");
1752+
attname_col=cols++;
1753+
appendPQExpBufferStr(&buf,",\n pg_catalog.format_type(a.atttypid, a.atttypmod)");
1754+
atttype_col=cols++;
1755+
1756+
if (show_column_details)
1757+
{
1758+
appendPQExpBufferStr(&buf,
1759+
",\n (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
1760+
"\n FROM pg_catalog.pg_attrdef d"
1761+
"\n WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)"
1762+
",\n a.attnotnull");
1763+
attrdef_col=cols++;
1764+
attnotnull_col=cols++;
1765+
if (pset.sversion >=90100)
1766+
appendPQExpBufferStr(&buf,",\n (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t\n"
1767+
" WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
1768+
else
1769+
appendPQExpBufferStr(&buf,",\n NULL AS attcollation");
1770+
attcoll_col=cols++;
1771+
if (pset.sversion >=100000)
1772+
appendPQExpBufferStr(&buf,",\n a.attidentity");
1773+
else
1774+
appendPQExpBufferStr(&buf,",\n ''::pg_catalog.char AS attidentity");
1775+
attidentity_col=cols++;
1776+
}
17441777
if (tableinfo.relkind==RELKIND_INDEX||
17451778
tableinfo.relkind==RELKIND_PARTITIONED_INDEX)
1779+
{
1780+
if (pset.sversion >=110000)
1781+
{
1782+
appendPQExpBuffer(&buf,",\n CASE WHEN a.attnum <= (SELECT i.indnkeyatts FROM pg_catalog.pg_index i WHERE i.indexrelid = '%s') THEN '%s' ELSE '%s' END AS is_key",
1783+
oid,
1784+
gettext_noop("yes"),
1785+
gettext_noop("no"));
1786+
isindexkey_col=cols++;
1787+
}
17461788
appendPQExpBufferStr(&buf,",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
1747-
else
1748-
appendPQExpBufferStr(&buf,",\n NULL AS indexdef");
1789+
indexdef_col=cols++;
1790+
}
1791+
/* FDW options for foreign table column, only for 9.2 or later */
17491792
if (tableinfo.relkind==RELKIND_FOREIGN_TABLE&&pset.sversion >=90200)
1793+
{
17501794
appendPQExpBufferStr(&buf,",\n CASE WHEN attfdwoptions IS NULL THEN '' ELSE "
17511795
" '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM "
17521796
" pg_catalog.pg_options_to_table(attfdwoptions)), ', ') || ')' END AS attfdwoptions");
1753-
else
1754-
appendPQExpBufferStr(&buf,",\n NULL AS attfdwoptions");
1797+
fdwopts_col=cols++;
1798+
}
17551799
if (verbose)
17561800
{
17571801
appendPQExpBufferStr(&buf,",\n a.attstorage");
1758-
appendPQExpBufferStr(&buf,",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1802+
attstorage_col=cols++;
1803+
1804+
/* stats target, if relevant to relkind */
1805+
if (tableinfo.relkind==RELKIND_RELATION||
1806+
tableinfo.relkind==RELKIND_INDEX||
1807+
tableinfo.relkind==RELKIND_PARTITIONED_INDEX||
1808+
tableinfo.relkind==RELKIND_MATVIEW||
1809+
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1810+
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1811+
{
1812+
appendPQExpBufferStr(&buf,",\n CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget");
1813+
attstattarget_col=cols++;
1814+
}
17591815

17601816
/*
17611817
* In 9.0+, we have column comments for: relations, views, composite
@@ -1767,7 +1823,10 @@ describeOneTableDetails(const char *schemaname,
17671823
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
17681824
tableinfo.relkind==RELKIND_COMPOSITE_TYPE||
17691825
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1770-
appendPQExpBufferStr(&buf,", pg_catalog.col_description(a.attrelid, a.attnum)");
1826+
{
1827+
appendPQExpBufferStr(&buf,",\n pg_catalog.col_description(a.attrelid, a.attnum)");
1828+
attdescr_col=cols++;
1829+
}
17711830
}
17721831

17731832
appendPQExpBufferStr(&buf,"\nFROM pg_catalog.pg_attribute a");
@@ -1843,100 +1902,63 @@ describeOneTableDetails(const char *schemaname,
18431902
break;
18441903
}
18451904

1846-
/* Set the number of columns, and their names */
1847-
headers[0]=gettext_noop("Column");
1848-
headers[1]=gettext_noop("Type");
1849-
cols=2;
1850-
1851-
if (tableinfo.relkind==RELKIND_RELATION||
1852-
tableinfo.relkind==RELKIND_VIEW||
1853-
tableinfo.relkind==RELKIND_MATVIEW||
1854-
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1855-
tableinfo.relkind==RELKIND_COMPOSITE_TYPE||
1856-
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1905+
/* Fill headers[] with the names of the columns we will output */
1906+
cols=0;
1907+
headers[cols++]=gettext_noop("Column");
1908+
headers[cols++]=gettext_noop("Type");
1909+
if (show_column_details)
18571910
{
18581911
headers[cols++]=gettext_noop("Collation");
18591912
headers[cols++]=gettext_noop("Nullable");
18601913
headers[cols++]=gettext_noop("Default");
1861-
show_column_details= true;
18621914
}
1863-
1864-
if (tableinfo.relkind==RELKIND_INDEX||
1865-
tableinfo.relkind==RELKIND_PARTITIONED_INDEX)
1915+
if (isindexkey_col >=0)
1916+
headers[cols++]=gettext_noop("Key?");
1917+
if (indexdef_col >=0)
18661918
headers[cols++]=gettext_noop("Definition");
1867-
1868-
if (tableinfo.relkind==RELKIND_FOREIGN_TABLE&&pset.sversion >=90200)
1919+
if (fdwopts_col >=0)
18691920
headers[cols++]=gettext_noop("FDW options");
1870-
1871-
if (verbose)
1872-
{
1921+
if (attstorage_col >=0)
18731922
headers[cols++]=gettext_noop("Storage");
1874-
if (tableinfo.relkind==RELKIND_RELATION||
1875-
tableinfo.relkind==RELKIND_INDEX||
1876-
tableinfo.relkind==RELKIND_PARTITIONED_INDEX||
1877-
tableinfo.relkind==RELKIND_MATVIEW||
1878-
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1879-
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1880-
headers[cols++]=gettext_noop("Stats target");
1881-
/* Column comments, if the relkind supports this feature. */
1882-
if (tableinfo.relkind==RELKIND_RELATION||
1883-
tableinfo.relkind==RELKIND_VIEW||
1884-
tableinfo.relkind==RELKIND_MATVIEW||
1885-
tableinfo.relkind==RELKIND_COMPOSITE_TYPE||
1886-
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1887-
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1888-
headers[cols++]=gettext_noop("Description");
1889-
}
1923+
if (attstattarget_col >=0)
1924+
headers[cols++]=gettext_noop("Stats target");
1925+
if (attdescr_col >=0)
1926+
headers[cols++]=gettext_noop("Description");
1927+
1928+
Assert(cols <=lengthof(headers));
18901929

18911930
printTableInit(&cont,&myopt,title.data,cols,numrows);
18921931
printTableInitialized= true;
18931932

18941933
for (i=0;i<cols;i++)
18951934
printTableAddHeader(&cont,headers[i], true,'l');
18961935

1897-
/* Get view_def if table is a view or materialized view */
1898-
if ((tableinfo.relkind==RELKIND_VIEW||
1899-
tableinfo.relkind==RELKIND_MATVIEW)&&verbose)
1900-
{
1901-
PGresult*result;
1902-
1903-
printfPQExpBuffer(&buf,
1904-
"SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
1905-
oid);
1906-
result=PSQLexec(buf.data);
1907-
if (!result)
1908-
gotoerror_return;
1909-
1910-
if (PQntuples(result)>0)
1911-
view_def=pg_strdup(PQgetvalue(result,0,0));
1912-
1913-
PQclear(result);
1914-
}
1915-
19161936
/* Generate table cells to be printed */
19171937
for (i=0;i<numrows;i++)
19181938
{
19191939
/* Column */
1920-
printTableAddCell(&cont,PQgetvalue(res,i,0), false, false);
1940+
printTableAddCell(&cont,PQgetvalue(res,i,attname_col), false, false);
19211941

19221942
/* Type */
1923-
printTableAddCell(&cont,PQgetvalue(res,i,1), false, false);
1943+
printTableAddCell(&cont,PQgetvalue(res,i,atttype_col), false, false);
19241944

19251945
/* Collation, Nullable, Default */
19261946
if (show_column_details)
19271947
{
19281948
char*identity;
19291949
char*default_str="";
19301950

1931-
printTableAddCell(&cont,PQgetvalue(res,i,5), false, false);
1951+
printTableAddCell(&cont,PQgetvalue(res,i,attcoll_col), false, false);
19321952

1933-
printTableAddCell(&cont,strcmp(PQgetvalue(res,i,3),"t")==0 ?"not null" :"", false, false);
1953+
printTableAddCell(&cont,
1954+
strcmp(PQgetvalue(res,i,attnotnull_col),"t")==0 ?"not null" :"",
1955+
false, false);
19341956

1935-
identity=PQgetvalue(res,i,6);
1957+
identity=PQgetvalue(res,i,attidentity_col);
19361958

19371959
if (!identity[0])
19381960
/* (note: above we cut off the 'default' string at 128) */
1939-
default_str=PQgetvalue(res,i,2);
1961+
default_str=PQgetvalue(res,i,attrdef_col);
19401962
elseif (identity[0]==ATTRIBUTE_IDENTITY_ALWAYS)
19411963
default_str="generated always as identity";
19421964
elseif (identity[0]==ATTRIBUTE_IDENTITY_BY_DEFAULT)
@@ -1945,20 +1967,20 @@ describeOneTableDetails(const char *schemaname,
19451967
printTableAddCell(&cont,default_str, false, false);
19461968
}
19471969

1948-
/* Expression for index column */
1949-
if (tableinfo.relkind==RELKIND_INDEX||
1950-
tableinfo.relkind==RELKIND_PARTITIONED_INDEX)
1951-
printTableAddCell(&cont,PQgetvalue(res,i,7), false, false);
1970+
/* Info for index columns */
1971+
if (isindexkey_col >=0)
1972+
printTableAddCell(&cont,PQgetvalue(res,i,isindexkey_col), true, false);
1973+
if (indexdef_col >=0)
1974+
printTableAddCell(&cont,PQgetvalue(res,i,indexdef_col), false, false);
19521975

1953-
/* FDW options for foreign tablecolumn, only for 9.2 or later */
1954-
if (tableinfo.relkind==RELKIND_FOREIGN_TABLE&&pset.sversion>=90200)
1955-
printTableAddCell(&cont,PQgetvalue(res,i,8), false, false);
1976+
/* FDW options for foreign tablecolumns */
1977+
if (fdwopts_col>=0)
1978+
printTableAddCell(&cont,PQgetvalue(res,i,fdwopts_col), false, false);
19561979

19571980
/* Storage and Description */
1958-
if (verbose)
1981+
if (attstorage_col >=0)
19591982
{
1960-
intfirstvcol=9;
1961-
char*storage=PQgetvalue(res,i,firstvcol);
1983+
char*storage=PQgetvalue(res,i,attstorage_col);
19621984

19631985
/* these strings are literal in our syntax, so not translated. */
19641986
printTableAddCell(&cont, (storage[0]=='p' ?"plain" :
@@ -1967,29 +1989,17 @@ describeOneTableDetails(const char *schemaname,
19671989
(storage[0]=='e' ?"external" :
19681990
"???")))),
19691991
false, false);
1992+
}
19701993

1971-
/* Statistics target, if the relkind supports this feature */
1972-
if (tableinfo.relkind==RELKIND_RELATION||
1973-
tableinfo.relkind==RELKIND_INDEX||
1974-
tableinfo.relkind==RELKIND_PARTITIONED_INDEX||
1975-
tableinfo.relkind==RELKIND_MATVIEW||
1976-
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1977-
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1978-
{
1979-
printTableAddCell(&cont,PQgetvalue(res,i,firstvcol+1),
1980-
false, false);
1981-
}
1994+
/* Statistics target, if the relkind supports this feature */
1995+
if (attstattarget_col >=0)
1996+
printTableAddCell(&cont,PQgetvalue(res,i,attstattarget_col),
1997+
false, false);
19821998

1983-
/* Column comments, if the relkind supports this feature. */
1984-
if (tableinfo.relkind==RELKIND_RELATION||
1985-
tableinfo.relkind==RELKIND_VIEW||
1986-
tableinfo.relkind==RELKIND_MATVIEW||
1987-
tableinfo.relkind==RELKIND_COMPOSITE_TYPE||
1988-
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
1989-
tableinfo.relkind==RELKIND_PARTITIONED_TABLE)
1990-
printTableAddCell(&cont,PQgetvalue(res,i,firstvcol+2),
1991-
false, false);
1992-
}
1999+
/* Column comments, if the relkind supports this feature */
2000+
if (attdescr_col >=0)
2001+
printTableAddCell(&cont,PQgetvalue(res,i,attdescr_col),
2002+
false, false);
19932003
}
19942004

19952005
/* Make footers */
@@ -2654,6 +2664,25 @@ describeOneTableDetails(const char *schemaname,
26542664
}
26552665
}
26562666

2667+
/* Get view_def if table is a view or materialized view */
2668+
if ((tableinfo.relkind==RELKIND_VIEW||
2669+
tableinfo.relkind==RELKIND_MATVIEW)&&verbose)
2670+
{
2671+
PGresult*result;
2672+
2673+
printfPQExpBuffer(&buf,
2674+
"SELECT pg_catalog.pg_get_viewdef('%s'::pg_catalog.oid, true);",
2675+
oid);
2676+
result=PSQLexec(buf.data);
2677+
if (!result)
2678+
gotoerror_return;
2679+
2680+
if (PQntuples(result)>0)
2681+
view_def=pg_strdup(PQgetvalue(result,0,0));
2682+
2683+
PQclear(result);
2684+
}
2685+
26572686
if (view_def)
26582687
{
26592688
PGresult*result=NULL;

0 commit comments

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