compileSequences method

void compileSequences(StringBuffer buf)

The statements after the ALTER TABLE that give each column changed to a serial type, or to autoIncrement, a default from a sequence of its own, if it has none, as serial does: the sequence is created, owned by the column, and starts after its greatest value.

A sequence the column has, of a smaller integer than _sequences gives it, such as of a serial changed to bigserial, is changed to that integer, so that it does not end before the column's values do. Its greatest value is that of the integer, if it was that of the old one.

Implementation

void compileSequences(StringBuffer buf) {
  // The names after the renames, which run before.
  var table = sql.quotePostgresTableName(_currentName);
  var tableOwnName = sql.unqualifiedPostgresName(_currentName);
  // By the columns' own names, without their quotes, as the catalog knows
  // them.
  _sequences.forEach((name, integer) {
    var column = '$table.${sql.quotePostgresPart(name)}';
    // Of the schema of the table, as `serial` gives it.
    var sequence = sql.quotePostgresInSchemaOf(
      _currentName,
      '${tableOwnName}_${name}_seq',
    );
    var body =
        '''
DECLARE
seq_name text := pg_get_serial_sequence(${sql.quoteLiteral(table)}, ${sql.quoteLiteral(name)});
BEGIN
IF seq_name IS NULL THEN
  CREATE SEQUENCE $sequence AS $integer OWNED BY $column;
  ALTER TABLE $table ALTER COLUMN ${sql.quotePostgresPart(name)} SET DEFAULT nextval(${sql.quoteLiteral(sequence)});
  PERFORM setval(${sql.quoteLiteral(sequence)}, COALESCE((SELECT MAX(${sql.quotePostgresPart(name)}) FROM $table), 0)::bigint + 1, false);
ELSE
  IF (SELECT t.typlen FROM pg_sequence s JOIN pg_type t ON t.oid = s.seqtypid
      WHERE s.seqrelid = seq_name::regclass) < ${_integerSizes[integer]} THEN
    EXECUTE format('ALTER SEQUENCE %s AS $integer', seq_name);
  END IF;
  -- A column whose default was dropped keeps its sequence, of which it
  -- gets the default again. An identity column has no default, but its
  -- identity, which takes none.
  IF NOT EXISTS (SELECT 1 FROM pg_attrdef d JOIN pg_attribute a
      ON a.attrelid = d.adrelid AND a.attnum = d.adnum
      WHERE d.adrelid = ${sql.quoteLiteral(table)}::regclass
      AND a.attname = ${sql.quoteLiteral(name)})
    AND NOT EXISTS (SELECT 1 FROM pg_attribute a
      WHERE a.attrelid = ${sql.quoteLiteral(table)}::regclass
      AND a.attname = ${sql.quoteLiteral(name)}
      AND a.attidentity <> '') THEN
    EXECUTE format('ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval(%L::regclass)', ${sql.quoteLiteral(table)}, ${sql.quoteLiteral(sql.quotePostgresPart(name))}, seq_name);
  END IF;
END IF;
END
''';
    buf.writeln(_doBlock('sequence', body));
  });
}