sql - 如何根据行创建日期在PostgreSQL中创建自定义序列？

我正在创建一个Rails应用程序，它将替代我的雇主的（非常旧的）遗留订单管理应用程序。新系统的规格之一是订单编号系统保持不变。现在，我们的订单号的格式如下：
前四位是本年度
接下来的两位数是本月
下一个（最后一个）四位数是一个计数器，该计数器在当月下单时每次递增一位。
例如，2014年6月下的第一个订单的订单号为2014060001。下一个订单的订单号为2014060002，以此类推。
此订单号必须是订单表中的主ID。似乎我需要为PostgreSQL设置一个自定义序列来分配主键，但是我能找到的创建自定义序列的唯一文档是非常基本的（如何增加两个而不是一个，等等）。如何根据上述日期创建自定义序列？

最佳答案

您可以使用EXTRACT()函数将手动创建的序列设置为特定值：

setval('my_sequence',
  (EXTRACT(YEAR FROM now())::integer * 1000000) +
  (EXTRACT(MONTH FROM now())::integer * 10000)
);

输入的下一个订单将采用序列中的下一个值，即YYYYMM0001等。
关键是何时更新序列值。你可以在PG中用很难的方法，在orders表上写一个BEFORE INSERT触发器，检查这是否是新月份的第一条记录：

CREATE FUNCTION before_insert_order() RETURNS trigger AS $$
DECLARE
  base_val  integer;
BEGIN
  -- base_val is the minimal value of the sequence for the current month: YYYYMM0000
  base_val := (EXTRACT(YEAR FROM now())::integer * 1000000) +
              (EXTRACT(MONTH FROM now())::integer * 10000);

  -- So if the sequence is less, then update it
  IF (currval('my_sequence') < base_val)
    setval('my_sequence', base_val);
  END IF;

  -- Now assign the order id and continue with the insert
  NEW.id := nextval('my_sequence');
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bi_order
  BEFORE INSERT ON order_table
  FOR EACH ROW EXECUTE PROCEDURE before_insert_order();

为什么这么难？因为每次插入都要检查序列的值。如果您每天只有几个插入，并且您的系统不是很忙，这是一种可行的方法。
如果您不能留出所有的CPU周期，您可以安排一个cron作业在每月的第一天00:00:01运行，通过psql执行一个PG函数来更新序列，然后将序列用作新订单记录的默认值（因此不需要触发器）。