Sophie

Sophie

distrib > Mandriva > 2006.0 > x86_64 > by-pkgid > b8f4049de69feba5041d49ed4382e582 > files > 153

postgresql-docs-8.0.11-0.1.20060mdk.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Sequence Manipulation Functions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Functions and Operators"
HREF="functions.html"><LINK
REL="PREVIOUS"
TITLE="Network Address Functions and Operators"
HREF="functions-net.html"><LINK
REL="NEXT"
TITLE="Conditional Expressions"
HREF="functions-conditional.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-net.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 9. Functions and Operators</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-conditional.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="FUNCTIONS-SEQUENCE"
>9.12. Sequence Manipulation Functions</A
></H1
><A
NAME="AEN11876"
></A
><A
NAME="AEN11878"
></A
><A
NAME="AEN11880"
></A
><A
NAME="AEN11882"
></A
><P
>   This section describes <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>'s functions
   for operating on <I
CLASS="FIRSTTERM"
>sequence objects</I
>.
   Sequence objects (also called sequence generators or
   just sequences) are special single-row tables created with
   <TT
CLASS="COMMAND"
>CREATE SEQUENCE</TT
>.  A sequence object is usually used to
   generate unique identifiers for rows of a table.  The sequence functions,
   listed in <A
HREF="functions-sequence.html#FUNCTIONS-SEQUENCE-TABLE"
>Table 9-34</A
>,
   provide simple, multiuser-safe methods for obtaining successive
   sequence values from sequence objects.
  </P
><DIV
CLASS="TABLE"
><A
NAME="FUNCTIONS-SEQUENCE-TABLE"
></A
><P
><B
>Table 9-34. Sequence Functions</B
></P
><TABLE
BORDER="1"
CLASS="CALSTABLE"
><COL><COL><COL><THEAD
><TR
><TH
>Function</TH
><TH
>Return Type</TH
><TH
>Description</TH
></TR
></THEAD
><TBODY
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>nextval</CODE
>(<TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
>Advance sequence and return new value</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>currval</CODE
>(<TT
CLASS="TYPE"
>text</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
>Return value most recently obtained with <CODE
CLASS="FUNCTION"
>nextval</CODE
></TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>setval</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>bigint</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
>Set sequence's current value</TD
></TR
><TR
><TD
><TT
CLASS="LITERAL"
><CODE
CLASS="FUNCTION"
>setval</CODE
>(<TT
CLASS="TYPE"
>text</TT
>, <TT
CLASS="TYPE"
>bigint</TT
>, <TT
CLASS="TYPE"
>boolean</TT
>)</TT
></TD
><TD
><TT
CLASS="TYPE"
>bigint</TT
></TD
><TD
>Set sequence's current value and <TT
CLASS="LITERAL"
>is_called</TT
> flag</TD
></TR
></TBODY
></TABLE
></DIV
><P
>   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
   <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> names, the sequence functions convert their
   argument to lowercase unless the string is double-quoted.  Thus
</P><PRE
CLASS="PROGRAMLISTING"
>nextval('foo')      <I
CLASS="LINEANNOTATION"
>operates on sequence <TT
CLASS="LITERAL"
>foo</TT
></I
>
nextval('FOO')      <I
CLASS="LINEANNOTATION"
>operates on sequence <TT
CLASS="LITERAL"
>foo</TT
></I
>
nextval('"Foo"')    <I
CLASS="LINEANNOTATION"
>operates on sequence <TT
CLASS="LITERAL"
>Foo</TT
></I
></PRE
><P>
   The sequence name can be schema-qualified if necessary:
</P><PRE
CLASS="PROGRAMLISTING"
>nextval('myschema.foo')     <I
CLASS="LINEANNOTATION"
>operates on <TT
CLASS="LITERAL"
>myschema.foo</TT
></I
>
nextval('"myschema".foo')   <I
CLASS="LINEANNOTATION"
>same as above</I
>
nextval('foo')              <I
CLASS="LINEANNOTATION"
>searches search path for <TT
CLASS="LITERAL"
>foo</TT
></I
></PRE
><P>
   Of course, the text argument can be the result of an expression,
   not only a simple literal, which is occasionally useful.
  </P
><P
>   The available sequence functions are:

    <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><CODE
CLASS="FUNCTION"
>nextval</CODE
></DT
><DD
><P
>        Advance the sequence object to its next value and return that
        value.  This is done atomically: even if multiple sessions
        execute <CODE
CLASS="FUNCTION"
>nextval</CODE
> concurrently, each will safely receive
        a distinct sequence value.
       </P
></DD
><DT
><CODE
CLASS="FUNCTION"
>currval</CODE
></DT
><DD
><P
>        Return the value most recently obtained by <CODE
CLASS="FUNCTION"
>nextval</CODE
>
        for this sequence in the current session.  (An error is
        reported if <CODE
CLASS="FUNCTION"
>nextval</CODE
> has never been called for this
        sequence in this session.)  Notice that because this is returning
        a session-local value, it gives a predictable answer whether or not
        other sessions have executed <CODE
CLASS="FUNCTION"
>nextval</CODE
> since the
        current session did.
       </P
></DD
><DT
><CODE
CLASS="FUNCTION"
>setval</CODE
></DT
><DD
><P
>        Reset the sequence object's counter value.  The two-parameter
        form sets the sequence's <TT
CLASS="LITERAL"
>last_value</TT
> field to the specified
        value and sets its <TT
CLASS="LITERAL"
>is_called</TT
> field to <TT
CLASS="LITERAL"
>true</TT
>,
        meaning that the next <CODE
CLASS="FUNCTION"
>nextval</CODE
> will advance the sequence
        before returning a value.  In the three-parameter form,
        <TT
CLASS="LITERAL"
>is_called</TT
> may be set either <TT
CLASS="LITERAL"
>true</TT
> or
        <TT
CLASS="LITERAL"
>false</TT
>.  If it's set to <TT
CLASS="LITERAL"
>false</TT
>,
        the next <CODE
CLASS="FUNCTION"
>nextval</CODE
> will return exactly the specified
        value, and sequence advancement commences with the following
        <CODE
CLASS="FUNCTION"
>nextval</CODE
>.  For example,

</P><PRE
CLASS="SCREEN"
>SELECT setval('foo', 42);           <I
CLASS="LINEANNOTATION"
>Next <CODE
CLASS="FUNCTION"
>nextval</CODE
> will return 43</I
>
SELECT setval('foo', 42, true);     <I
CLASS="LINEANNOTATION"
>Same as above</I
>
SELECT setval('foo', 42, false);    <I
CLASS="LINEANNOTATION"
>Next <CODE
CLASS="FUNCTION"
>nextval</CODE
> will return 42</I
></PRE
><P>

        The result returned by <CODE
CLASS="FUNCTION"
>setval</CODE
> is just the value of its
        second argument.
       </P
></DD
></DL
></DIV
><P>
  </P
><DIV
CLASS="IMPORTANT"
><BLOCKQUOTE
CLASS="IMPORTANT"
><P
><B
>Important: </B
>    To avoid blocking of concurrent transactions that obtain numbers from the
    same sequence, a <CODE
CLASS="FUNCTION"
>nextval</CODE
> operation is never rolled back;
    that is, once a value has been fetched it is considered used, even if the
    transaction that did the <CODE
CLASS="FUNCTION"
>nextval</CODE
> later aborts.  This means
    that aborted transactions may leave unused <SPAN
CLASS="QUOTE"
>"holes"</SPAN
> in the
    sequence of assigned values.  <CODE
CLASS="FUNCTION"
>setval</CODE
> operations are never
    rolled back, either.
   </P
></BLOCKQUOTE
></DIV
><P
>   If a sequence object has been created with default parameters,
   <CODE
CLASS="FUNCTION"
>nextval</CODE
> calls on it will return successive values
   beginning with 1.  Other behaviors can be obtained by using
   special parameters in the <A
HREF="sql-createsequence.html"
>CREATE SEQUENCE</A
> command;
   see its command reference page for more information.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions-net.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="functions-conditional.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Network Address Functions and Operators</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="functions.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Conditional Expressions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional