Automatic Key Field Value Generation

When editing a dataset it is often convenient to generate key field(s) values automatically instead of filling them manually. In the most common way application developer generates primary key value basing it on a previously created sequence. There are three ways of doing it.

First, application independent way - developer uses SERIAL data type, or manualy sets field default value like the following:

ALTER TABLE Department ALTER COLUMN DepNo SET DEFAULT nextval('seq_deptno'::regclass)

, or creates AFTER INSERT trigger that fills the field value. But there he faces the problem with getting inserted value back to dataset. This problem can be easily solved in PgDAC using RETURNING clause. In order for dataset to return a field value specified in RETURNING clause, set the TDADataSetOptions.ReturnParams property to True. For instance:

...
PgQuery.SQL.Text := 'SELECT DepNo, DepName, Location FROM Department';
PgQuery.SQLInsert.Text := 'INSERT INTO Department (DepNo, DepName, Location)' +
                           'VALUES(DepNo, DepName, Location) ' +
                           'RETURNING DepNo';
PgQuery.Options.ReturnParams := True;
...

The second way is custom key field value generation. Developer can fill key field value in the TCustomPgDataSet.BeforePost event handler. But in this case he should manually execute query and retrieve the sequence value. So this way may be useful only if some special value processing is needed.

The third way, using KeySequence, is the simplest. Developer only needs to specify two properties and key field values are generated automatically. There is no need to create trigger or perform custom BeforePost processing.

...
PgQuery.SQL.Text := 'SELECT DepNo, DepName, Location FROM Department';
PgQuery.KeyFields := 'DepNo';         // key field
PgQuery.KeySequence := 'seq_deptno'; // sequence that will generate values
...

Automatic Key Field Value Generation

See Also