PostgreSQL グループ毎 自動採番

新規入力時、サロゲートキー以外のグループ毎に採番したい場合

元ネタ：pgsql-jp ML

[TABLE:users]
u_id serial（サロゲートキー）
id integer（重複あり）
s_id integer（重複あり）
n_id integer（重複あり）

[トリガでmax()を使うケース]

CREATE FUNCTION assign_s_id() RETURNS TRIGGER AS $$
BEGIN
  PERFORM 1 FROM users WHERE u_id = 1 AND s_id = 1 FOR UPDATE;
-- IF NOT FOUND
--   FIXME: u_idごとの最初の行の時には別のロックが要る場合があるようです
-- END IF;
  SELECT coalesce(max(s_id), 0) + 1 INTO NEW.s_id FROM users WHERE
u_id = NEW.u_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_insert BEFORE INSERT ON users FOR EACH ROW
  EXECUTE PROCEDURE assign_s_id();

INSERT INTO users (u_id, s_id) VALUES (<値>, DEFAULT);