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

Commit39b8843

Browse files
committed
Implement remaining fields of information_schema.sequences view
Add new function pg_sequence_parameters that returns a sequence's start,minimum, maximum, increment, and cycle values, and use that in the view.(bug #5662; design suggestion by Tom Lane)Also slightly adjust the view's column order and permissions after review ofSQL standard.
1 parente657b55 commit39b8843

File tree

8 files changed

+92
-14
lines changed

8 files changed

+92
-14
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 19 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4139,31 +4139,42 @@ ORDER BY c.ordinal_position;
41394139
</row>
41404140

41414141
<row>
4142-
<entry><literal>maximum_value</literal></entry>
4143-
<entry><type>cardinal_number</type></entry>
4144-
<entry>Not yet implemented</entry>
4142+
<entry><literal>start_value</literal></entry>
4143+
<entry><type>character_data</type></entry>
4144+
<entry>The start value of the sequence</entry>
41454145
</row>
41464146

41474147
<row>
41484148
<entry><literal>minimum_value</literal></entry>
4149-
<entry><type>cardinal_number</type></entry>
4150-
<entry>Not yet implemented</entry>
4149+
<entry><type>character_data</type></entry>
4150+
<entry>The minimum value of the sequence</entry>
4151+
</row>
4152+
4153+
<row>
4154+
<entry><literal>maximum_value</literal></entry>
4155+
<entry><type>character_data</type></entry>
4156+
<entry>The maximum value of the sequence</entry>
41514157
</row>
41524158

41534159
<row>
41544160
<entry><literal>increment</literal></entry>
4155-
<entry><type>cardinal_number</type></entry>
4156-
<entry>Not yet implemented</entry>
4161+
<entry><type>character_data</type></entry>
4162+
<entry>The increment of the sequence</entry>
41574163
</row>
41584164

41594165
<row>
41604166
<entry><literal>cycle_option</literal></entry>
41614167
<entry><type>yes_or_no</type></entry>
4162-
<entry>Not yet implemented</entry>
4168+
<entry><literal>YES</literal> if the sequence cycles, else <literal>NO</literal></entry>
41634169
</row>
41644170
</tbody>
41654171
</tgroup>
41664172
</table>
4173+
4174+
<para>
4175+
Note that in accordance with the SQL standard, the start, minimum,
4176+
maximum, and increment values are returned as character strings.
4177+
</para>
41674178
</sect1>
41684179

41694180
<sect1 id="infoschema-sql-features">

‎src/backend/catalog/information_schema.sql

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1430,16 +1430,18 @@ CREATE VIEW sequences AS
14301430
CAST(64AS cardinal_number)AS numeric_precision,
14311431
CAST(2AS cardinal_number)AS numeric_precision_radix,
14321432
CAST(0AS cardinal_number)AS numeric_scale,
1433-
CAST(nullAS cardinal_number)AS maximum_value,-- FIXME
1434-
CAST(nullAS cardinal_number)AS minimum_value,-- FIXME
1435-
CAST(nullAS cardinal_number)AS increment,-- FIXME
1436-
CAST(nullAS yes_or_no)AS cycle_option-- FIXME
1433+
-- XXX: The following could be improved if we had LATERAL.
1434+
CAST((pg_sequence_parameters(c.oid)).start_valueAS character_data)AS start_value,
1435+
CAST((pg_sequence_parameters(c.oid)).minimum_valueAS character_data)AS minimum_value,
1436+
CAST((pg_sequence_parameters(c.oid)).maximum_valueAS character_data)AS maximum_value,
1437+
CAST((pg_sequence_parameters(c.oid)).incrementAS character_data)AS increment,
1438+
CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN'YES' ELSE'NO' ENDAS yes_or_no)AS cycle_option
14371439
FROM pg_namespace nc, pg_class c
14381440
WHEREc.relnamespace=nc.oid
14391441
ANDc.relkind='S'
14401442
AND (NOT pg_is_other_temp_schema(nc.oid))
14411443
AND (pg_has_role(c.relowner,'USAGE')
1442-
ORhas_table_privilege(c.oid,'SELECT, UPDATE') );
1444+
ORhas_sequence_privilege(c.oid,'SELECT, UPDATE, USAGE') );
14431445

14441446
GRANTSELECTON sequences TO PUBLIC;
14451447

‎src/backend/commands/sequence.c

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
#include"commands/defrem.h"
2525
#include"commands/sequence.h"
2626
#include"commands/tablecmds.h"
27+
#include"funcapi.h"
2728
#include"miscadmin.h"
2829
#include"nodes/makefuncs.h"
2930
#include"storage/bufmgr.h"
@@ -1420,6 +1421,56 @@ process_owned_by(Relation seqrel, List *owned_by)
14201421
}
14211422

14221423

1424+
/*
1425+
* Return sequence parameters, for use by information schema
1426+
*/
1427+
Datum
1428+
pg_sequence_parameters(PG_FUNCTION_ARGS)
1429+
{
1430+
Oidrelid=PG_GETARG_OID(0);
1431+
TupleDesctupdesc;
1432+
Datumvalues[5];
1433+
boolisnull[5];
1434+
SeqTableelm;
1435+
Relationseqrel;
1436+
Bufferbuf;
1437+
Form_pg_sequenceseq;
1438+
1439+
/* open and AccessShareLock sequence */
1440+
init_sequence(relid,&elm,&seqrel);
1441+
1442+
if (pg_class_aclcheck(relid,GetUserId(),ACL_SELECT |ACL_UPDATE |ACL_USAGE)!=ACLCHECK_OK)
1443+
ereport(ERROR,
1444+
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1445+
errmsg("permission denied for sequence %s",
1446+
RelationGetRelationName(seqrel))));
1447+
1448+
tupdesc=CreateTemplateTupleDesc(5, false);
1449+
TupleDescInitEntry(tupdesc, (AttrNumber)1,"start_value",INT8OID,-1,0);
1450+
TupleDescInitEntry(tupdesc, (AttrNumber)2,"minimum_value",INT8OID,-1,0);
1451+
TupleDescInitEntry(tupdesc, (AttrNumber)3,"maximum_value",INT8OID,-1,0);
1452+
TupleDescInitEntry(tupdesc, (AttrNumber)4,"increment",INT8OID,-1,0);
1453+
TupleDescInitEntry(tupdesc, (AttrNumber)5,"cycle_option",BOOLOID,-1,0);
1454+
1455+
BlessTupleDesc(tupdesc);
1456+
1457+
memset(isnull,0,sizeof(isnull));
1458+
1459+
seq=read_info(elm,seqrel,&buf);
1460+
1461+
values[0]=Int64GetDatum(seq->start_value);
1462+
values[1]=Int64GetDatum(seq->min_value);
1463+
values[2]=Int64GetDatum(seq->max_value);
1464+
values[3]=Int64GetDatum(seq->increment_by);
1465+
values[4]=BoolGetDatum(seq->is_cycled);
1466+
1467+
UnlockReleaseBuffer(buf);
1468+
relation_close(seqrel,NoLock);
1469+
1470+
returnHeapTupleGetDatum(heap_form_tuple(tupdesc,values,isnull));
1471+
}
1472+
1473+
14231474
void
14241475
seq_redo(XLogRecPtrlsn,XLogRecord*record)
14251476
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201101011
56+
#defineCATALOG_VERSION_NO201101021
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2120,6 +2120,8 @@ DATA(insert OID = 1576 ( setvalPGNSP PGUID 12 1 0 0 f f f t f v 2 0 20 "2205
21202120
DESCR("set sequence value");
21212121
DATA(insertOID=1765 (setvalPGNSPPGUID12100ffftfv3020"2205 20 16"_null__null__null__null_setval3_oid_null__null__null_ ));
21222122
DESCR("set sequence value and iscalled status");
2123+
DATA(insertOID=3078 (pg_sequence_parametersPGNSPPGUID12100ffftfs102249"26""{23,20,20,20,20,16}""{i,o,o,o,o,o}""{sequence_oid,start_value,minimum_value,maximum_value,increment,cycle_option}"_null_pg_sequence_parameters_null__null__null_));
2124+
DESCR("sequence parameters, for use by information schema");
21232125

21242126
DATA(insertOID=1579 (varbit_inPGNSPPGUID12100ffftfi301562"2275 26 23"_null__null__null__null_varbit_in_null__null__null_ ));
21252127
DESCR("I/O");

‎src/include/commands/sequence.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,8 @@ extern Datum setval_oid(PG_FUNCTION_ARGS);
6969
externDatumsetval3_oid(PG_FUNCTION_ARGS);
7070
externDatumlastval(PG_FUNCTION_ARGS);
7171

72+
externDatumpg_sequence_parameters(PG_FUNCTION_ARGS);
73+
7274
externvoidDefineSequence(CreateSeqStmt*stmt);
7375
externvoidAlterSequence(AlterSeqStmt*stmt);
7476
externvoidResetSequence(Oidseq_relid);

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -220,6 +220,13 @@ SELECT nextval('sequence_test2');
220220
5
221221
(1 row)
222222

223+
-- Information schema
224+
SELECT * FROM information_schema.sequences WHERE sequence_name IN ('sequence_test2');
225+
sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option
226+
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
227+
regression | public | sequence_test2 | bigint | 64 | 2 | 0 | 32 | 5 | 36 | 4 | YES
228+
(1 row)
229+
223230
-- Test comments
224231
COMMENT ON SEQUENCE asdf IS 'won''t work';
225232
ERROR: relation "asdf" does not exist

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,9 @@ SELECT nextval('sequence_test2');
8585
SELECT nextval('sequence_test2');
8686
SELECT nextval('sequence_test2');
8787

88+
-- Information schema
89+
SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN ('sequence_test2');
90+
8891
-- Test comments
8992
COMMENT ON SEQUENCE asdf IS'won''t work';
9093
COMMENT ON SEQUENCE sequence_test2 IS'will work';

0 commit comments

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