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

Commit2a14b96

Browse files
committed
psql: Update \d sequence display
For \d sequencename, the psql code just did SELECT * FROM sequencenameto get the information to display, but this does not contain muchinteresting information anymore in PostgreSQL 10, because the metadatahas been moved to a separate system catalog.This patch creates a newly designed sequence display that is not merelyan extension of the general relation/table display as it was previously.Example:PostgreSQL 9.6:=> \d foobar Sequence "public.foobar" Column | Type | Value---------------+---------+--------------------- sequence_name | name | foobar last_value | bigint | 1 start_value | bigint | 1 increment_by | bigint | 1 max_value | bigint | 9223372036854775807 min_value | bigint | 1 cache_value | bigint | 1 log_cnt | bigint | 0 is_cycled | boolean | f is_called | boolean | fPostgreSQL 10 before this change:=> \d foobar Sequence "public.foobar" Column | Type | Value------------+---------+------- last_value | bigint | 1 log_cnt | bigint | 0 is_called | boolean | fNew:=> \d foobar Sequence "public.foobar" Type | Start | Minimum | Maximum | Increment | Cycles? | Cache--------+-------+---------+---------------------+-----------+---------+------- bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1Reviewed-by: Fabien COELHO <coelho@cri.ensmp.fr>
1 parent136ab7c commit2a14b96

File tree

5 files changed

+135
-80
lines changed

5 files changed

+135
-80
lines changed

‎src/bin/psql/describe.c

Lines changed: 109 additions & 80 deletions
Original file line numberDiff line numberDiff line change
@@ -1380,8 +1380,6 @@ describeOneTableDetails(const char *schemaname,
13801380
inti;
13811381
char*view_def=NULL;
13821382
char*headers[11];
1383-
char**seq_values=NULL;
1384-
char**ptr;
13851383
PQExpBufferDatatitle;
13861384
PQExpBufferDatatmpbuf;
13871385
intcols;
@@ -1563,27 +1561,125 @@ describeOneTableDetails(const char *schemaname,
15631561
res=NULL;
15641562

15651563
/*
1566-
* If it's a sequence, fetch its values and store into an array that will
1567-
* be used later.
1564+
* If it's a sequence, deal with it here separately.
15681565
*/
15691566
if (tableinfo.relkind==RELKIND_SEQUENCE)
15701567
{
1571-
printfPQExpBuffer(&buf,"SELECT * FROM %s",fmtId(schemaname));
1572-
/* must be separate because fmtId isn't reentrant */
1573-
appendPQExpBuffer(&buf,".%s;",fmtId(relationname));
1568+
PGresult*result=NULL;
1569+
printQueryOptmyopt=pset.popt;
1570+
char*footers[2]= {NULL,NULL};
1571+
1572+
if (pset.sversion >=100000)
1573+
{
1574+
printfPQExpBuffer(&buf,
1575+
"SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
1576+
" seqstart AS \"%s\",\n"
1577+
" seqmin AS \"%s\",\n"
1578+
" seqmax AS \"%s\",\n"
1579+
" seqincrement AS \"%s\",\n"
1580+
" CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n"
1581+
" seqcache AS \"%s\"\n",
1582+
gettext_noop("Type"),
1583+
gettext_noop("Start"),
1584+
gettext_noop("Minimum"),
1585+
gettext_noop("Maximum"),
1586+
gettext_noop("Increment"),
1587+
gettext_noop("yes"),
1588+
gettext_noop("no"),
1589+
gettext_noop("Cycles?"),
1590+
gettext_noop("Cache"));
1591+
appendPQExpBuffer(&buf,
1592+
"FROM pg_catalog.pg_sequence\n"
1593+
"WHERE seqrelid = '%s';",
1594+
oid);
1595+
}
1596+
else
1597+
{
1598+
printfPQExpBuffer(&buf,
1599+
"SELECT pg_catalog.format_type('bigint'::regtype, NULL) AS \"%s\",\n"
1600+
" start_value AS \"%s\",\n"
1601+
" min_value AS \"%s\",\n"
1602+
" max_value AS \"%s\",\n"
1603+
" increment_by AS \"%s\",\n"
1604+
" CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n"
1605+
" cache_value AS \"%s\"\n",
1606+
gettext_noop("Type"),
1607+
gettext_noop("Start"),
1608+
gettext_noop("Minimum"),
1609+
gettext_noop("Maximum"),
1610+
gettext_noop("Increment"),
1611+
gettext_noop("yes"),
1612+
gettext_noop("no"),
1613+
gettext_noop("Cycles?"),
1614+
gettext_noop("Cache"));
1615+
appendPQExpBuffer(&buf,"FROM %s",fmtId(schemaname));
1616+
/* must be separate because fmtId isn't reentrant */
1617+
appendPQExpBuffer(&buf,".%s;",fmtId(relationname));
1618+
}
15741619

15751620
res=PSQLexec(buf.data);
15761621
if (!res)
15771622
gotoerror_return;
15781623

1579-
seq_values=pg_malloc((PQnfields(res)+1)*sizeof(*seq_values));
1624+
/* Footer information about a sequence */
1625+
1626+
/* Get the column that owns this sequence */
1627+
printfPQExpBuffer(&buf,"SELECT pg_catalog.quote_ident(nspname) || '.' ||"
1628+
"\n pg_catalog.quote_ident(relname) || '.' ||"
1629+
"\n pg_catalog.quote_ident(attname),"
1630+
"\n d.deptype"
1631+
"\nFROM pg_catalog.pg_class c"
1632+
"\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
1633+
"\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
1634+
"\nINNER JOIN pg_catalog.pg_attribute a ON ("
1635+
"\n a.attrelid=c.oid AND"
1636+
"\n a.attnum=d.refobjsubid)"
1637+
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
1638+
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
1639+
"\n AND d.objid='%s'"
1640+
"\n AND d.deptype IN ('a', 'i')",
1641+
oid);
1642+
1643+
result=PSQLexec(buf.data);
1644+
1645+
/*
1646+
* If we get no rows back, don't show anything (obviously). We should
1647+
* never get more than one row back, but if we do, just ignore it and
1648+
* don't print anything.
1649+
*/
1650+
if (!result)
1651+
gotoerror_return;
1652+
elseif (PQntuples(result)==1)
1653+
{
1654+
switch (PQgetvalue(result,0,1)[0])
1655+
{
1656+
case'a':
1657+
footers[0]=psprintf(_("Owned by: %s"),
1658+
PQgetvalue(result,0,0));
1659+
break;
1660+
case'i':
1661+
footers[0]=psprintf(_("Sequence for identity column: %s"),
1662+
PQgetvalue(result,0,0));
1663+
break;
1664+
}
1665+
}
1666+
PQclear(result);
1667+
1668+
printfPQExpBuffer(&title,_("Sequence \"%s.%s\""),
1669+
schemaname,relationname);
15801670

1581-
for (i=0;i<PQnfields(res);i++)
1582-
seq_values[i]=pg_strdup(PQgetvalue(res,0,i));
1583-
seq_values[i]=NULL;
1671+
myopt.footers=footers;
1672+
myopt.topt.default_footer= false;
1673+
myopt.title=title.data;
1674+
myopt.translate_header= true;
15841675

1585-
PQclear(res);
1586-
res=NULL;
1676+
printQuery(res,&myopt,pset.queryFout, false,pset.logfile);
1677+
1678+
if (footers[0])
1679+
free(footers[0]);
1680+
1681+
retval= true;
1682+
gotoerror_return;/* not an error, just return early */
15871683
}
15881684

15891685
/*
@@ -1667,10 +1763,6 @@ describeOneTableDetails(const char *schemaname,
16671763
printfPQExpBuffer(&title,_("Materialized view \"%s.%s\""),
16681764
schemaname,relationname);
16691765
break;
1670-
caseRELKIND_SEQUENCE:
1671-
printfPQExpBuffer(&title,_("Sequence \"%s.%s\""),
1672-
schemaname,relationname);
1673-
break;
16741766
caseRELKIND_INDEX:
16751767
if (tableinfo.relpersistence=='u')
16761768
printfPQExpBuffer(&title,_("Unlogged index \"%s.%s\""),
@@ -1729,9 +1821,6 @@ describeOneTableDetails(const char *schemaname,
17291821
show_column_details= true;
17301822
}
17311823

1732-
if (tableinfo.relkind==RELKIND_SEQUENCE)
1733-
headers[cols++]=gettext_noop("Value");
1734-
17351824
if (tableinfo.relkind==RELKIND_INDEX)
17361825
headers[cols++]=gettext_noop("Definition");
17371826

@@ -1814,10 +1903,6 @@ describeOneTableDetails(const char *schemaname,
18141903
printTableAddCell(&cont,default_str, false, false);
18151904
}
18161905

1817-
/* Value: for sequences only */
1818-
if (tableinfo.relkind==RELKIND_SEQUENCE)
1819-
printTableAddCell(&cont,seq_values[i], false, false);
1820-
18211906
/* Expression for index column */
18221907
if (tableinfo.relkind==RELKIND_INDEX)
18231908
printTableAddCell(&cont,PQgetvalue(res,i,7), false, false);
@@ -2030,55 +2115,6 @@ describeOneTableDetails(const char *schemaname,
20302115

20312116
PQclear(result);
20322117
}
2033-
elseif (tableinfo.relkind==RELKIND_SEQUENCE)
2034-
{
2035-
/* Footer information about a sequence */
2036-
PGresult*result=NULL;
2037-
2038-
/* Get the column that owns this sequence */
2039-
printfPQExpBuffer(&buf,"SELECT pg_catalog.quote_ident(nspname) || '.' ||"
2040-
"\n pg_catalog.quote_ident(relname) || '.' ||"
2041-
"\n pg_catalog.quote_ident(attname),"
2042-
"\n d.deptype"
2043-
"\nFROM pg_catalog.pg_class c"
2044-
"\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
2045-
"\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
2046-
"\nINNER JOIN pg_catalog.pg_attribute a ON ("
2047-
"\n a.attrelid=c.oid AND"
2048-
"\n a.attnum=d.refobjsubid)"
2049-
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
2050-
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
2051-
"\n AND d.objid='%s'"
2052-
"\n AND d.deptype IN ('a', 'i')",
2053-
oid);
2054-
2055-
result=PSQLexec(buf.data);
2056-
if (!result)
2057-
gotoerror_return;
2058-
elseif (PQntuples(result)==1)
2059-
{
2060-
switch (PQgetvalue(result,0,1)[0])
2061-
{
2062-
case'a':
2063-
printfPQExpBuffer(&buf,_("Owned by: %s"),
2064-
PQgetvalue(result,0,0));
2065-
printTableAddFooter(&cont,buf.data);
2066-
break;
2067-
case'i':
2068-
printfPQExpBuffer(&buf,_("Sequence for identity column: %s"),
2069-
PQgetvalue(result,0,0));
2070-
printTableAddFooter(&cont,buf.data);
2071-
break;
2072-
}
2073-
}
2074-
2075-
/*
2076-
* If we get no rows back, don't show anything (obviously). We should
2077-
* never get more than one row back, but if we do, just ignore it and
2078-
* don't print anything.
2079-
*/
2080-
PQclear(result);
2081-
}
20822118
elseif (tableinfo.relkind==RELKIND_RELATION||
20832119
tableinfo.relkind==RELKIND_MATVIEW||
20842120
tableinfo.relkind==RELKIND_FOREIGN_TABLE||
@@ -2963,13 +2999,6 @@ describeOneTableDetails(const char *schemaname,
29632999
termPQExpBuffer(&title);
29643000
termPQExpBuffer(&tmpbuf);
29653001

2966-
if (seq_values)
2967-
{
2968-
for (ptr=seq_values;*ptr;ptr++)
2969-
free(*ptr);
2970-
free(seq_values);
2971-
}
2972-
29733002
if (view_def)
29743003
free(view_def);
29753004

‎src/test/regress/expected/identity.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,13 @@ SELECT pg_get_serial_sequence('itest1', 'a');
3232
public.itest1_a_seq
3333
(1 row)
3434

35+
\d itest1_a_seq
36+
Sequence "public.itest1_a_seq"
37+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
38+
---------+-------+---------+------------+-----------+---------+-------
39+
integer | 1 | 1 | 2147483647 | 1 | no | 1
40+
Sequence for identity column: public.itest1.a
41+
3542
CREATE TABLE itest4 (a int, b text);
3643
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL
3744
ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added

‎src/test/regress/expected/sequence.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -535,6 +535,19 @@ SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);
535535
-1 | -9223372036854775808 | -1 | -1 | f | 1 | 20
536536
(1 row)
537537

538+
\d sequence_test4
539+
Sequence "public.sequence_test4"
540+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
541+
--------+-------+----------------------+---------+-----------+---------+-------
542+
bigint | -1 | -9223372036854775808 | -1 | -1 | no | 1
543+
544+
\d serialtest2_f2_seq
545+
Sequence "public.serialtest2_f2_seq"
546+
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
547+
---------+-------+---------+------------+-----------+---------+-------
548+
integer | 1 | 1 | 2147483647 | 1 | no | 1
549+
Owned by: public.serialtest2.f2
550+
538551
-- Test comments
539552
COMMENT ON SEQUENCE asdf IS 'won''t work';
540553
ERROR: relation "asdf" does not exist

‎src/test/regress/sql/identity.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,8 @@ SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE
1414

1515
SELECT pg_get_serial_sequence('itest1','a');
1616

17+
\d itest1_a_seq
18+
1719
CREATETABLEitest4 (aint, btext);
1820
ALTERTABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYSAS IDENTITY;-- error, requires NOT NULL
1921
ALTERTABLE itest4 ALTER COLUMN aSETNOT NULL;

‎src/test/regress/sql/sequence.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -246,6 +246,10 @@ WHERE sequencename ~ ANY(ARRAY['sequence_test', 'serialtest'])
246246
SELECT*FROM pg_sequence_parameters('sequence_test4'::regclass);
247247

248248

249+
\d sequence_test4
250+
\d serialtest2_f2_seq
251+
252+
249253
-- Test comments
250254
COMMENT ON SEQUENCE asdf IS'won''t work';
251255
COMMENT ON SEQUENCE sequence_test2 IS'will work';

0 commit comments

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