Using the 'serial8' and 'bigserial' options

The 'bigserial' and 'serial8' options respectively return a single integer that specifies the last SERIAL8 or BIGSERIAL value that was inserted into a table. To ensure valid results, use this option immediately following an INSERT statement that inserts a SERIAL8 or BIGSERIAL value.

Tip: To obtain the value of the last SERIAL value that is inserted into a table, use the 'sqlca.sqlerrd1' option of DBINFO( ). For more information, see Using the 'sqlca.sqlerrd1' Option.

The following example uses the 'serial8' option:

EXEC SQL CREATE TABLE fst_tab 
   (ordernum SERIAL8, partnum INT);
EXEC SQL CREATE TABLE sec_tab (ordernum SERIAL8);

EXEC SQL INSERT INTO fst_tab VALUES (0,1);
EXEC SQL INSERT INTO fst_tab VALUES (0,4);
EXEC SQL INSERT INTO fst_tab VALUES (0,6);

EXEC SQL INSERT INTO sec_tab 
   SELECT dbinfo('serial8')
   FROM fst_tab WHERE partnum = 6;

This example inserts a row that contains a primary-key SERIAL8 value into the fst_tab table and then uses the DBINFO function to insert the same SERIAL8 value into the sec_tab table. The value that the DBINFO function returns is the SERIAL8 value of the last row that is inserted into fst_tab. The subquery in the last line contains a WHERE clause so that a single value is returned.

The SQLCA structure does not record serial values that are inserted by triggers. You cannot call the DBINFO function with the 'bigserial' option to return the most recent BIGSERIAL value that was inserted directly by the triggered action of a trigger on a table (nor of an INSTEAD OF trigger on a view). For the same reason, the DBINFO ('serial8') function cannot return a SERIAL8 value that was inserted by a trigger on a table, nor by an INSTEAD OF trigger on a view.