PostgreSQL

Скачать в pdf «PostgreSQL»


Figure 7.3 shows an example of sequence creation and sequence function usage. The first command creates the sequence, then various sequence functions are called. Note that the selects do not include a from clause. Sequence function calls are not directly tied to any table. In the figure:


•    nextval() returns ever-increasing values.


•    currval() returns the previous sequence value without incrementing.


•    setval() sets the sequence counter to a new value.


Currval() returns the sequence number assigned by a prior nextval() call in the current session. It is not affected by the nextval() calls of other users, which allows reliable retrieval of nextval() assigned values in later queries.

7.5    Using Sequences to Number Rows


Configuring a sequence to uniquely number rows involves several steps:


test=> CREATE SEQUENCE funetest_seq; CREATE


test=> SELECT nextval(‘funetest_seq’); nextval


1


(1 row)


test=> SELECT nextval(‘functest_seq’); nextval


2


(1 row)


test=> SELECT currval(‘functest_seq’); currval


2


(1 row)


test=> SELECT setval(‘functest_seq’, 100); setval


100 (1 row)


test=> SELECT nextval(‘functest_seq’); nextval


101


(1 row)


test=> CREATE SEQUENCE customer_seq;


CREATE


test=> CREATE TABLE customer (


test(>    customer_id INTEGER DEFAULT nextval(‘customer_seq’},


test(>    name CHAR(30)


test(> );


CREATE


test=> INSERT INTO customer VALUES (nextval(‘customer_seq’), ‘Bread Makers’); INSERT 19004 1


test=> INSERT INTO customer (name) VALUES (‘Wax Carvers’);


INSERT 19005 1


test=> INSERT INTO customer (name) VALUES (‘Pipe Fitters’);

Скачать в pdf «PostgreSQL»