PostgreSQL中的自定义自动递增字段(发票/订单号)
基线要求是以以下格式创建订单号:
(M)M-SSS
其中MM表示当前月份,SSSS表示该月份的订单顺序.例如1-002将代表一月份提交的第二个订单.
Where MM represents the current month and SSSS represents the order sequence for that month. For example 1-002 would represent the second order submitted in January.
使用触发器,我希望自动递增并透明地插入.
Using a TRIGGER I'd like the auto-increment and insert to work transparently.
不幸的是,距离我接触存储过程已经很长时间了,这是我第一次尝试Postgresql.指出正确方向的任何帮助将不胜感激.
Unfortunately, it has been a long time since I have touched a stored procedure and this is my first foray into postgresql. Any help pointing in the right direction would be greatly appreciated.
更新:这是使用@peterm的代码的最终实现
-- The trigger
CREATE TRIGGER add_order_number
BEFORE INSERT ON orders FOR EACH ROW
EXECUTE PROCEDURE order_number_update();
-- The trigger function
CREATE FUNCTION order_number_update() RETURNS TRIGGER AS $$
DECLARE
next_order TEXT;
BEGIN
-- get the next order number
SELECT INTO next_order CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_number), 3) AS INT), 0) + 1 AS VARCHAR(3)), 3, '0'))
FROM orders
WHERE CAST(LEFT(order_number, STRPOS(order_number, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE);
-- update the field
NEW.order_number = next_order;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
注意:通过将更新添加为INSERT触发器之前,它可以完全透明且可预测的方式运行,就像正常的SERIAL或BIGSERIAL字段一样.
您是否正在寻找类似的东西?
Are you looking for something like this?
-- Next No calculated for current month
SELECT CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
FROM orders
WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE)
;
输出:
| NEXT_NO |
-----------
| 5-0001 |
假设有一条记录,其中order_no
='1-0001',然后
Assuming that there is a record with order_no
= '1-0001' then
-- Next No for January
SELECT CONCAT(CAST(DATE_PART('MONTH', DATE '2013-01-01') AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_no), 4) AS INT), 0) + 1 AS VARCHAR(4)), 4, '0')) next_no
FROM orders
WHERE CAST(LEFT(order_no, STRPOS(order_no, '-') - 1) AS INT) = DATE_PART('MONTH', DATE '2013-01-01')
;
输出:
| NEXT_NO |
-----------
| 1-0002 |
SQLFiddle