CURRVAL

Returns the last value across all nodes that was set by NEXTVALon this sequence in the current session. If NEXTVAL was never called on this sequence since its creation, Vertica returns an error.

Behavior Type

Volatile

Syntax

CURRVAL('[schema.]sequence-name')

Parameters

schema	Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject
sequence‑name	The target sequence

Privileges

SELECT privilege on sequence
USAGE privilege on sequence schema

Examples

The following example creates an ascending sequence called sequential, starting at 101:

=> CREATE SEQUENCE seq2 START 101;

You can call CURRVAL only after you initiate the sequence with NEXTVAL; otherwise, Vertica returns an error:

=> SELECT CURRVAL('seq2');
ERROR:  Sequence seq2 has not been accessed in the session

Use NEXTVAL to generate the first number for this sequence:

=> SELECT NEXTVAL('seq2');
 nextval
---------
     101
(1 row)

Now you can use CURRVAL to return the current number from this sequence:

=> SELECT CURRVAL('seq2');
 currval
---------
     101
(1 row)

The following command shows how to use CURRVAL in a SELECT statement:

=> CREATE TABLE customer3 (
  lname VARCHAR(25), 
  fname VARCHAR(25),
  membership_card INTEGER, 
  ID INTEGER
);
INSERT INTO customer3 VALUES ('Brown' ,'Sabra', 072753, CURRVAL('my_seq'));
SELECT CURRVAL('seq2'), lname FROM customer3;
 CURRVAL | lname
---------+-------
     101 | Brown
(1 row)

The following example illustrates how the NEXTVAL is always evaluated first (and here, increments the my_seq sequence from its previous value), even when CURRVAL precedes NEXTVAL:

=> SELECT CURRVAL('my_seq'), NEXTVAL('my_seq');
 currval | nextval
---------+---------
     102 |     102
(1 row)