diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 165 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_sequence.sgml | 96 | ||||
| -rw-r--r-- | doc/src/sgml/ref/grant.sgml | 6 |
3 files changed, 203 insertions, 64 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 3cbd8cdf6b5..b03a8b28ee3 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,7 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.78 2001/10/12 02:08:34 ishii Exp $ --> +<!-- +$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.79 2001/11/17 22:20:33 tgl Exp $ +Postgres documentation +--> <chapter id="functions"> <title>Functions and Operators</title> @@ -3627,6 +3630,166 @@ SELECT TIMESTAMP 'now'; </sect1> + <sect1 id="functions-sequence"> + <title>Sequence-Manipulation Functions</title> + + <indexterm> + <primary>sequences</primary> + </indexterm> + <indexterm> + <primary>nextval</primary> + </indexterm> + <indexterm> + <primary>currval</primary> + </indexterm> + <indexterm> + <primary>setval</primary> + </indexterm> + + <table> + <title>Sequence Functions</> + <tgroup cols="3"> + <thead> + <row><entry>Function</> <entry>Returns</> <entry>Description</></row> + </thead> + + <tbody> + <row> + <entry><function>nextval</function>(<type>text</type>)</entry> + <entry><type>bigint</type></entry> + <entry>Advance sequence and return new value</> + </row> + <row> + <entry><function>currval</function>(<type>text</type>)</entry> + <entry><type>bigint</type></entry> + <entry>Return value most recently obtained with <function>nextval</></entry> + </row> + <row> + <entry><function>setval</function>(<type>text</type>,<type>bigint</type>)</entry> + <entry><type>bigint</type></entry> + <entry>Set sequence's current value</> + </row> + <row> + <entry><function>setval</function>(<type>text</type>,<type>bigint</type>,<type>boolean</>)</entry> + <entry><type>bigint</type></entry> + <entry>Set sequence's current value and <literal>is_called</> flag</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + This section describes <productname>Postgres</productname>'s functions + for operating on <firstterm>sequence objects</>. + Sequence objects (also called sequence generators or + just sequences) are special single-row tables created with + <command>CREATE SEQUENCE</>. A sequence object is usually used to + generate unique identifiers for rows of a table. The sequence functions + provide simple, multi-user-safe methods for obtaining successive + sequence values from sequence objects. + </para> + + <para> + For largely historical reasons, the sequence to be operated on by + a sequence-function call is specified by a text-string argument. + To achieve some compatibility with the handling of ordinary SQL + names, the sequence functions convert their argument to lower case + unless the string is double-quoted. Thus +<programlisting> +nextval('foo') <lineannotation>operates on sequence </><literal>foo</> +nextval('FOO') <lineannotation>operates on sequence </><literal>foo</> +nextval('"Foo"') <lineannotation>operates on sequence </><literal>Foo</> +</programlisting> + Of course, the text argument can be the result of an expression, + not only a simple literal, which is occasionally useful. + </para> + + <para> + The available sequence functions are: + + <variablelist> + <varlistentry> + <term><function>nextval</></term> + <listitem> + <para> + Advance the sequence object to its next value and return that + value. This is done atomically: even if multiple server processes + execute <function>nextval</> concurrently, each will safely receive + a distinct sequence value. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>currval</></term> + <listitem> + <para> + Return the value most recently obtained by <function>nextval</> + for this sequence in the current server process. (An error is + reported if <function>nextval</> has never been called for this + sequence in this process.) Notice that because this is returning + a process-local value, it gives a predictable answer even if other + server processes are executing <function>nextval</> meanwhile. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><function>setval</></term> + <listitem> + <para> + Reset the sequence object's counter value. The two-parameter + form sets the sequence's <literal>last_value</> field to the specified + value and sets its <literal>is_called</> field to <literal>true</>, + meaning that the next <function>nextval</> will advance the sequence + before returning a value. In the three-parameter form, + <literal>is_called</> may be set either <literal>true</> or + <literal>false</>. If it's set to <literal>false</>, + the next <function>nextval</> will return exactly the specified + value, and sequence advancement commences with the following + <function>nextval</>. For example, + </para> + + <informalexample> +<screen> +SELECT setval('foo', 42); <lineannotation>Next nextval() will return 43</> +SELECT setval('foo', 42, true); <lineannotation>Same as above</> +SELECT setval('foo', 42, false); <lineannotation>Next nextval() will return 42</> +</screen> + </informalexample> + + <para> + The result returned by <function>setval</> is just the value of its + second argument. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <important> + <para> + To avoid blocking of concurrent transactions that obtain numbers from the + same sequence, a <function>nextval</> operation is never rolled back; + that is, once a value has been fetched it is considered used, even if the + transaction that did the <function>nextval</> later aborts. This means + that aborted transactions may leave unused <quote>holes</quote> in the + sequence of assigned values. <function>setval</> operations are never + rolled back, either. + </para> + </important> + + <para> + If a sequence object has been created with default parameters, + <function>nextval()</> calls on it will return successive values + beginning with one. Other behaviors can be obtained by using + special parameters in the <command>CREATE SEQUENCE</> command; + see its command reference page for more information. + </para> + + </sect1> + + <sect1 id="functions-conditional"> <title>Conditional Expressions</title> diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 953ca916849..334d45dd8b9 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.20 2001/09/13 15:55:24 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.21 2001/11/17 22:20:34 tgl Exp $ Postgres documentation --> @@ -15,7 +15,7 @@ Postgres documentation CREATE SEQUENCE </refname> <refpurpose> - define a new sequence + define a new sequence generator </refpurpose> </refnamediv> <refsynopsisdiv> @@ -42,8 +42,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep <term>TEMPORARY or TEMP</term> <listitem> <para> - If specified, the sequence is created only for this session, and is - automatically dropped on session exit. + If specified, the sequence object is created only for this session, + and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists. </para> @@ -141,6 +141,8 @@ CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</rep <replaceable class="parameter">minvalue</replaceable> or <replaceable class="parameter">maxvalue</replaceable>, respectively. + Without CYCLE, after the limit is reached <function>nextval</> calls + will return an error. </para> </listitem> </varlistentry> @@ -222,81 +224,57 @@ ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceabl </title> <para> <command>CREATE SEQUENCE</command> will enter a new sequence number generator - into the current data base. This involves creating and initializing a + into the current database. This involves creating and initializing a new single-row table with the name <replaceable class="parameter">seqname</replaceable>. The generator will be owned by the user issuing the command. </para> <para> - After a sequence is created, you may use the function - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - to get a new number from the sequence. - The function - <function>currval('<replaceable class="parameter">seqname</replaceable>')</function> - may be used to determine the number returned by the last call to - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - for the specified sequence in the current session. - The function - <function>setval('<replaceable class="parameter">seqname</replaceable>', - <replaceable class="parameter">newvalue</replaceable>)</function> - may be used to set the current value of the specified sequence. - The next call to - <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function> - will return the given value plus the sequence increment. + After a sequence is created, you use the functions + <function>nextval</function>, + <function>currval</function> and + <function>setval</function> + to operate on the sequence. These functions are documented in + the <citetitle>User's Guide</citetitle>. </para> <para> - Use a query like + Although you cannot update a sequence directly, you can use a query like <programlisting> SELECT * FROM <replaceable>seqname</replaceable>; </programlisting> - to examine the parameters of a sequence. - - As an alternative to fetching the - parameters from the original definition as above, you can use - - <programlisting> -SELECT last_value FROM <replaceable>seqname</replaceable>; - </programlisting> - - to obtain the last value allocated by any backend. - </para> - - <para> - To avoid blocking of concurrent transactions - that obtain numbers from the same sequence, a nextval operation - is never rolled back; that is, once a value has been fetched it is - considered used, even if the transaction that did the nextval later - aborts. This means that aborted transactions may leave unused <quote>holes</quote> - in the sequence of assigned values. setval operations are never - rolled back, either. + to examine the parameters and current state of a sequence. In particular, + the <literal>last_value</> field of the sequence shows the last value + allocated by any backend process. (Of course, this value may be obsolete + by the time it's printed, if other processes are actively doing + <function>nextval</> calls.) </para> <caution> <para> - Unexpected results may be obtained if a cache setting greater than one + Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one is used for a sequence object that will be used concurrently by multiple backends. Each backend will allocate and cache successive sequence values during one access to the sequence object and increase the sequence - object's last_value accordingly. Then, the next cache-1 uses of nextval + object's <literal>last_value</> accordingly. Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</> within that backend simply return the preallocated values without touching - the shared object. So, numbers allocated but not used in the current session - will be lost. Furthermore, although multiple backends are guaranteed to + the shared object. So, any numbers allocated but not used within a session + will be lost when that session ends. Furthermore, although multiple backends are guaranteed to allocate distinct sequence values, the values may be generated out of - sequence when all the backends are considered. (For example, with a cache + sequence when all the backends are considered. (For example, with a <replaceable class="parameter">cache</replaceable> setting of 10, backend A might reserve values 1..10 and return nextval=1, then backend B might reserve values 11..20 and return nextval=11 before backend - A has generated nextval=2.) Thus, with a cache setting of one it is safe - to assume that nextval values are generated sequentially; with a cache - setting greater than one you should only assume that the nextval values + A has generated nextval=2.) Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe + to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable> + setting greater than one you should only assume that the <function>nextval</> values are all distinct, not that they are generated purely sequentially. - Also, last_value will reflect the latest value reserved by any backend, - whether or not it has yet been returned by nextval. - Another consideration is that a setval executed on such a sequence + Also, <literal>last_value</> will reflect the latest value reserved by any backend, + whether or not it has yet been returned by <function>nextval</>. + Another consideration is that a <function>setval</> executed on such a sequence will not be noticed by other backends until they have used up any preallocated values they have cached. </para> @@ -313,7 +291,8 @@ SELECT last_value FROM <replaceable>seqname</replaceable>; Use <command>DROP SEQUENCE</command> to remove a sequence. </para> <para> - Each backend uses its own cache to store preallocated numbers. + When <replaceable class="parameter">cache</replaceable> is greater than + one, each backend uses its own cache to store preallocated numbers. Numbers that are cached but not used in the current session will be lost, resulting in <quote>holes</quote> in the sequence. </para> @@ -333,7 +312,7 @@ CREATE SEQUENCE serial START 101; <para> Select the next number from this sequence: <programlisting> -SELECT NEXTVAL ('serial'); +SELECT nextval('serial'); nextval ------- @@ -343,19 +322,16 @@ nextval <para> Use this sequence in an INSERT: <programlisting> -INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing'); +INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); </programlisting> </para> <para> - Set the sequence value after a COPY FROM: + Update the sequence value after a COPY FROM: <programlisting> -CREATE FUNCTION distributors_id_max() RETURNS INT4 - AS 'SELECT max(id) FROM distributors' - LANGUAGE 'sql'; BEGIN; COPY distributors FROM 'input_file'; - SELECT setval('serial', distributors_id_max()); + SELECT setval('serial', max(id)) FROM distributors; END; </programlisting> </para> diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 573f56756c5..1cd7de8c9bc 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.13 2001/11/11 19:24:28 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.14 2001/11/17 22:20:34 tgl Exp $ Postgres documentation --> @@ -74,8 +74,8 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER } [,.. specified table. <literal>SELECT ... FOR UPDATE</literal> also requires this privilege (besides the <literal>SELECT</literal> privilege). For sequences, this - privilege allows the use of <function>currval</function> and - <function>nextval</function>. + privilege allows the use of <function>nextval</function>, + <function>currval</function> and <function>setval</function>. </para> </listitem> </varlistentry> |