GoogleSQL for Spanner supports the following sequence functions.
Function list
| Name | Summary |
|---|---|
GET_INTERNAL_SEQUENCE_STATE
|
Gets the current sequence internal counter before bit reversal. |
GET_NEXT_SEQUENCE_VALUE
|
Takes in a sequence identifier and returns the next value. This function is only allowed in read-write transactions. |
GET_INTERNAL_SEQUENCE_STATE
GET_INTERNAL_SEQUENCE_STATE
(
SEQUENCE
sequence_identifier
)
Description
Gets the current sequence internal counter before bit reversal. This function is
useful for import or export, and migrations. If GET_NEXT_SEQUENCE_VALUE
is
never called on the sequence, then this function returns NULL
.
Arguments
-
sequence_identifier: The ID for the sequence.
Return Data Type
INT64
Example
SELECT
GET_NEXT_SEQUENCE_VALUE
(
SEQUENCE
MySequence
)
AS
next_value
;
/*---------------------*
| next_value |
+---------------------+
| 5980780305148018688 |
*---------------------*/
SELECT
GET_INTERNAL_SEQUENCE_STATE
(
SEQUENCE
MySequence
)
AS
sequence_state
;
/*----------------*
| sequence_state |
+----------------+
| 399 |
*----------------*/
GET_NEXT_SEQUENCE_VALUE
GET_NEXT_SEQUENCE_VALUE
(
SEQUENCE
sequence_identifier
)
Description
Gets the next integer in a sequence.
Arguments
-
sequence_identifier: The ID for the sequence.
Return Data Type
INT64
Example
Create a table where its key column uses the sequence as a default value.
CREATE
TABLE
Singers
(
SingerId
INT64
DEFAULT
(
GET_NEXT_SEQUENCE_VALUE
(
SEQUENCE
MySequence
)),
a
STRING
(
MAX
),
)
PRIMARY
KEY
(
SingerId
);
Obtain a sequence value in a read-write transaction and use it in an INSERT statement.
SELECT
GET_NEXT_SEQUENCE_VALUE
(
SEQUENCE
MySequence
)
as
next_id
;
INSERT
INTO
Singers
(
SingerId
,
a
)
VALUES
(
next_id
,
1
);
Use the sequence functions independently in the GoogleSQL DML.
INSERT
INTO
Singers
(
SingerId
)
VALUES
(
GET_NEXT_SEQUENCE_VALUE
(
SEQUENCE
MySequence
);
