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

Commit2043340

Browse files
committed
Expand documentation for sequence functions (nextval and friends).
Place it in the expected place in the User's Guide, rather thanhiding it in the command reference page for CREATE SEQUENCE.
1 parent59fdccc commit2043340

File tree

3 files changed

+203
-64
lines changed

3 files changed

+203
-64
lines changed

‎doc/src/sgml/func.sgml‎

Lines changed: 164 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.78 2001/10/12 02:08:34 ishii Exp $ -->
1+
<!--
2+
$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.79 2001/11/17 22:20:33 tgl Exp $
3+
Postgres documentation
4+
-->
25

36
<chapter id="functions">
47
<title>Functions and Operators</title>
@@ -3627,6 +3630,166 @@ SELECT TIMESTAMP 'now';
36273630
</sect1>
36283631

36293632

3633+
<sect1 id="functions-sequence">
3634+
<title>Sequence-Manipulation Functions</title>
3635+
3636+
<indexterm>
3637+
<primary>sequences</primary>
3638+
</indexterm>
3639+
<indexterm>
3640+
<primary>nextval</primary>
3641+
</indexterm>
3642+
<indexterm>
3643+
<primary>currval</primary>
3644+
</indexterm>
3645+
<indexterm>
3646+
<primary>setval</primary>
3647+
</indexterm>
3648+
3649+
<table>
3650+
<title>Sequence Functions</>
3651+
<tgroup cols="3">
3652+
<thead>
3653+
<row><entry>Function</> <entry>Returns</> <entry>Description</></row>
3654+
</thead>
3655+
3656+
<tbody>
3657+
<row>
3658+
<entry><function>nextval</function>(<type>text</type>)</entry>
3659+
<entry><type>bigint</type></entry>
3660+
<entry>Advance sequence and return new value</>
3661+
</row>
3662+
<row>
3663+
<entry><function>currval</function>(<type>text</type>)</entry>
3664+
<entry><type>bigint</type></entry>
3665+
<entry>Return value most recently obtained with <function>nextval</></entry>
3666+
</row>
3667+
<row>
3668+
<entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry>
3669+
<entry><type>bigint</type></entry>
3670+
<entry>Set sequence's current value</>
3671+
</row>
3672+
<row>
3673+
<entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</>)</entry>
3674+
<entry><type>bigint</type></entry>
3675+
<entry>Set sequence's current value and <literal>is_called</> flag</entry>
3676+
</row>
3677+
</tbody>
3678+
</tgroup>
3679+
</table>
3680+
3681+
<para>
3682+
This section describes <productname>Postgres</productname>'s functions
3683+
for operating on <firstterm>sequence objects</>.
3684+
Sequence objects (also called sequence generators or
3685+
just sequences) are special single-row tables created with
3686+
<command>CREATE SEQUENCE</>. A sequence object is usually used to
3687+
generate unique identifiers for rows of a table. The sequence functions
3688+
provide simple, multi-user-safe methods for obtaining successive
3689+
sequence values from sequence objects.
3690+
</para>
3691+
3692+
<para>
3693+
For largely historical reasons, the sequence to be operated on by
3694+
a sequence-function call is specified by a text-string argument.
3695+
To achieve some compatibility with the handling of ordinary SQL
3696+
names, the sequence functions convert their argument to lower case
3697+
unless the string is double-quoted. Thus
3698+
<programlisting>
3699+
nextval('foo') <lineannotation>operates on sequence </><literal>foo</>
3700+
nextval('FOO') <lineannotation>operates on sequence </><literal>foo</>
3701+
nextval('"Foo"') <lineannotation>operates on sequence </><literal>Foo</>
3702+
</programlisting>
3703+
Of course, the text argument can be the result of an expression,
3704+
not only a simple literal, which is occasionally useful.
3705+
</para>
3706+
3707+
<para>
3708+
The available sequence functions are:
3709+
3710+
<variablelist>
3711+
<varlistentry>
3712+
<term><function>nextval</></term>
3713+
<listitem>
3714+
<para>
3715+
Advance the sequence object to its next value and return that
3716+
value. This is done atomically: even if multiple server processes
3717+
execute <function>nextval</> concurrently, each will safely receive
3718+
a distinct sequence value.
3719+
</para>
3720+
</listitem>
3721+
</varlistentry>
3722+
3723+
<varlistentry>
3724+
<term><function>currval</></term>
3725+
<listitem>
3726+
<para>
3727+
Return the value most recently obtained by <function>nextval</>
3728+
for this sequence in the current server process. (An error is
3729+
reported if <function>nextval</> has never been called for this
3730+
sequence in this process.) Notice that because this is returning
3731+
a process-local value, it gives a predictable answer even if other
3732+
server processes are executing <function>nextval</> meanwhile.
3733+
</para>
3734+
</listitem>
3735+
</varlistentry>
3736+
3737+
<varlistentry>
3738+
<term><function>setval</></term>
3739+
<listitem>
3740+
<para>
3741+
Reset the sequence object's counter value. The two-parameter
3742+
form sets the sequence's <literal>last_value</> field to the specified
3743+
value and sets its <literal>is_called</> field to <literal>true</>,
3744+
meaning that the next <function>nextval</> will advance the sequence
3745+
before returning a value. In the three-parameter form,
3746+
<literal>is_called</> may be set either <literal>true</> or
3747+
<literal>false</>. If it's set to <literal>false</>,
3748+
the next <function>nextval</> will return exactly the specified
3749+
value, and sequence advancement commences with the following
3750+
<function>nextval</>. For example,
3751+
</para>
3752+
3753+
<informalexample>
3754+
<screen>
3755+
SELECT setval('foo', 42); <lineannotation>Next nextval() will return 43</>
3756+
SELECT setval('foo', 42, true); <lineannotation>Same as above</>
3757+
SELECT setval('foo', 42, false); <lineannotation>Next nextval() will return 42</>
3758+
</screen>
3759+
</informalexample>
3760+
3761+
<para>
3762+
The result returned by <function>setval</> is just the value of its
3763+
second argument.
3764+
</para>
3765+
</listitem>
3766+
</varlistentry>
3767+
</variablelist>
3768+
</para>
3769+
3770+
<important>
3771+
<para>
3772+
To avoid blocking of concurrent transactions that obtain numbers from the
3773+
same sequence, a <function>nextval</> operation is never rolled back;
3774+
that is, once a value has been fetched it is considered used, even if the
3775+
transaction that did the <function>nextval</> later aborts. This means
3776+
that aborted transactions may leave unused <quote>holes</quote> in the
3777+
sequence of assigned values. <function>setval</> operations are never
3778+
rolled back, either.
3779+
</para>
3780+
</important>
3781+
3782+
<para>
3783+
If a sequence object has been created with default parameters,
3784+
<function>nextval()</> calls on it will return successive values
3785+
beginning with one. Other behaviors can be obtained by using
3786+
special parameters in the <command>CREATE SEQUENCE</> command;
3787+
see its command reference page for more information.
3788+
</para>
3789+
3790+
</sect1>
3791+
3792+
36303793
<sect1 id="functions-conditional">
36313794
<title>Conditional Expressions</title>
36323795

‎doc/src/sgml/ref/create_sequence.sgml‎

Lines changed: 36 additions & 60 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.20 2001/09/13 15:55:24 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.21 2001/11/17 22:20:34 tgl Exp $
33
Postgres documentation
44
-->
55

@@ -15,7 +15,7 @@ Postgres documentation
1515
CREATE SEQUENCE
1616
</refname>
1717
<refpurpose>
18-
define a new sequence
18+
define a new sequence generator
1919
</refpurpose>
2020
</refnamediv>
2121
<refsynopsisdiv>
@@ -42,8 +42,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
4242
<term>TEMPORARY or TEMP</term>
4343
<listitem>
4444
<para>
45-
If specified, the sequence is created only for this session, and is
46-
automatically dropped on session exit.
45+
If specified, the sequenceobjectis created only for this session,
46+
and isautomatically dropped on session exit.
4747
Existing permanent sequences with the same name are not visible
4848
(in this session) while the temporary sequence exists.
4949
</para>
@@ -141,6 +141,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep
141141
<replaceable class="parameter">minvalue</replaceable> or
142142
<replaceable class="parameter">maxvalue</replaceable>,
143143
respectively.
144+
Without CYCLE, after the limit is reached <function>nextval</> calls
145+
will return an error.
144146
</para>
145147
</listitem>
146148
</varlistentry>
@@ -222,81 +224,57 @@ ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceabl
222224
</title>
223225
<para>
224226
<command>CREATE SEQUENCE</command> will enter a new sequence number generator
225-
into the currentdata base. This involves creating and initializing a
227+
into the currentdatabase. This involves creating and initializing a
226228
new single-row
227229
table with the name <replaceable class="parameter">seqname</replaceable>.
228230
The generator will be owned by the user issuing the command.
229231
</para>
230232

231233
<para>
232-
After a sequence is created, you may use the function
233-
<function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
234-
to get a new number from the sequence.
235-
The function
236-
<function>currval('<replaceable class="parameter">seqname</replaceable>')</function>
237-
may be used to determine the number returned by the last call to
238-
<function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
239-
for the specified sequence in the current session.
240-
The function
241-
<function>setval('<replaceable class="parameter">seqname</replaceable>',
242-
<replaceable class="parameter">newvalue</replaceable>)</function>
243-
may be used to set the current value of the specified sequence.
244-
The next call to
245-
<function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
246-
will return the given value plus the sequence increment.
234+
After a sequence is created, you use the functions
235+
<function>nextval</function>,
236+
<function>currval</function> and
237+
<function>setval</function>
238+
to operate on the sequence. These functions are documented in
239+
the <citetitle>User's Guide</citetitle>.
247240
</para>
248241

249242
<para>
250-
Use a query like
243+
Although you cannot update a sequence directly, you can use a query like
251244

252245
<programlisting>
253246
SELECT * FROM <replaceable>seqname</replaceable>;
254247
</programlisting>
255248

256-
to examine the parameters of a sequence.
257-
258-
As an alternative to fetching the
259-
parameters from the original definition as above, you can use
260-
261-
<programlisting>
262-
SELECT last_value FROM <replaceable>seqname</replaceable>;
263-
</programlisting>
264-
265-
to obtain the last value allocated by any backend.
266-
</para>
267-
268-
<para>
269-
To avoid blocking of concurrent transactions
270-
that obtain numbers from the same sequence, a nextval operation
271-
is never rolled back; that is, once a value has been fetched it is
272-
considered used, even if the transaction that did the nextval later
273-
aborts. This means that aborted transactions may leave unused <quote>holes</quote>
274-
in the sequence of assigned values. setval operations are never
275-
rolled back, either.
249+
to examine the parameters and current state of a sequence. In particular,
250+
the <literal>last_value</> field of the sequence shows the last value
251+
allocated by any backend process. (Of course, this value may be obsolete
252+
by the time it's printed, if other processes are actively doing
253+
<function>nextval</> calls.)
276254
</para>
277255

278256
<caution>
279257
<para>
280-
Unexpected results may be obtained if a cache setting greater than one
258+
Unexpected results may be obtained if a<replaceable class="parameter">cache</replaceable> setting greater than one
281259
is used for a sequence object that will be used concurrently by multiple
282260
backends. Each backend will allocate and cache successive sequence values
283261
during one access to the sequence object and increase the sequence
284-
object's last_value accordingly. Then, the next cache-1 uses of nextval
262+
object's<literal>last_value</> accordingly. Then, the next<replaceable class="parameter">cache</replaceable>-1 uses of<function>nextval</>
285263
within that backend simply return the preallocated values without touching
286-
the shared object. So, numbers allocated but not usedin the current session
287-
will be lost. Furthermore, although multiple backends are guaranteed to
264+
the shared object. So,anynumbers allocated but not usedwithin a session
265+
will be lost when that session ends. Furthermore, although multiple backends are guaranteed to
288266
allocate distinct sequence values, the values may be generated out of
289-
sequence when all the backends are considered. (For example, with a cache
267+
sequence when all the backends are considered. (For example, with a<replaceable class="parameter">cache</replaceable>
290268
setting of 10, backend A might reserve values 1..10 and return nextval=1,
291269
then
292270
backend B might reserve values 11..20 and return nextval=11 before backend
293-
A has generated nextval=2.) Thus, with a cache setting of one it is safe
294-
to assume that nextval values are generated sequentially; with a cache
295-
setting greater than one you should only assume that the nextval values
271+
A has generated nextval=2.) Thus, with a<replaceable class="parameter">cache</replaceable> setting of one it is safe
272+
to assume that<function>nextval</> values are generated sequentially; with a<replaceable class="parameter">cache</replaceable>
273+
setting greater than one you should only assume that the<function>nextval</> values
296274
are all distinct, not that they are generated purely sequentially.
297-
Also, last_value will reflect the latest value reserved by any backend,
298-
whether or not it has yet been returned by nextval.
299-
Another consideration is that a setval executed on such a sequence
275+
Also,<literal>last_value</> will reflect the latest value reserved by any backend,
276+
whether or not it has yet been returned by<function>nextval</>.
277+
Another consideration is that a<function>setval</> executed on such a sequence
300278
will not be noticed by other backends until they have used up any
301279
preallocated values they have cached.
302280
</para>
@@ -313,7 +291,8 @@ SELECT last_value FROM <replaceable>seqname</replaceable>;
313291
Use <command>DROP SEQUENCE</command> to remove a sequence.
314292
</para>
315293
<para>
316-
Each backend uses its own cache to store preallocated numbers.
294+
When <replaceable class="parameter">cache</replaceable> is greater than
295+
one, each backend uses its own cache to store preallocated numbers.
317296
Numbers that are cached but not used in the current session will be
318297
lost, resulting in <quote>holes</quote> in the sequence.
319298
</para>
@@ -333,7 +312,7 @@ CREATE SEQUENCE serial START 101;
333312
<para>
334313
Select the next number from this sequence:
335314
<programlisting>
336-
SELECTNEXTVAL('serial');
315+
SELECTnextval('serial');
337316

338317
nextval
339318
-------
@@ -343,19 +322,16 @@ nextval
343322
<para>
344323
Use this sequence in an INSERT:
345324
<programlisting>
346-
INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
325+
INSERT INTO distributors VALUES (nextval('serial'),'nothing');
347326
</programlisting>
348327
</para>
349328

350329
<para>
351-
Set the sequence value after a COPY FROM:
330+
Update the sequence value after a COPY FROM:
352331
<programlisting>
353-
CREATE FUNCTION distributors_id_max() RETURNS INT4
354-
AS 'SELECT max(id) FROM distributors'
355-
LANGUAGE 'sql';
356332
BEGIN;
357333
COPY distributors FROM 'input_file';
358-
SELECT setval('serial',distributors_id_max());
334+
SELECT setval('serial',max(id)) FROM distributors;
359335
END;
360336
</programlisting>
361337
</para>

‎doc/src/sgml/ref/grant.sgml‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.13 2001/11/11 19:24:28 momjian Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.14 2001/11/17 22:20:34 tgl Exp $
33
Postgres documentation
44
-->
55

@@ -74,8 +74,8 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,..
7474
specified table. <literal>SELECT ... FOR UPDATE</literal>
7575
also requires this privilege (besides the
7676
<literal>SELECT</literal> privilege). For sequences, this
77-
privilege allows the use of <function>currval</function> and
78-
<function>nextval</function>.
77+
privilege allows the use of <function>nextval</function>,
78+
<function>currval</function> and <function>setval</function>.
7979
</para>
8080
</listitem>
8181
</varlistentry>

0 commit comments

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