This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Sequence functions

The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

1 - CURRVAL

Returns the last value across all nodes that was set by NEXTVAL on this sequence in the current session.

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

Syntax

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

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

sequence-name
The target sequence

Privileges

  • SELECT privilege on sequence

  • USAGE privilege on sequence schema

Restrictions

You cannot invoke CURRVAL in a SELECT statement, in the following contexts:

  • WHERE clause

  • GROUP BY clause

  • ORDER BY clause

  • DISTINCT clause

  • UNION

  • Subquery

You also cannot invoke CURRVAL to act on a sequence in:

  • UPDATE or DELETE subqueries

  • Views

Examples

See Creating and using named sequences.

See also

NEXTVAL

2 - NEXTVAL

Returns the next value in a sequence.

Returns the next value in a sequence. Call NEXTVAL after creating a sequence to initialize the sequence with its default value. Thereafter, call NEXTVAL to increment the sequence value for ascending sequences, or decrement its value for descending sequences.

Syntax

NEXTVAL ('[[database.]schema.]sequence')

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

sequence
Identifies the target sequence.

Privileges

  • SELECT privilege on sequence

  • USAGE privilege on sequence schema

Restrictions

You cannot invoke NEXTVAL in a SELECT statement, in the following contexts:

  • WHERE clause

  • GROUP BY clause

  • ORDER BY clause

  • DISTINCT clause

  • UNION

  • Subquery

You also cannot invoke NEXTVAL to act on a sequence in:

  • UPDATE or DELETE subqueries

  • Views

You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT clause:

=> SELECT t.col1, shift_allocation_seq.NEXTVAL FROM (
     SELECT DISTINCT col1 FROM av_temp1) t;

Examples

See Creating and using named sequences.

See also

CURRVAL