gpt4 book ai didi

postgresql - postgresql 自定义自增字段(发票/订单号)

转载 作者:行者123 更新时间:2023-11-29 12:13:40 29 4
gpt4 key购买 nike

基本要求是以以下格式创建订单号:

(M)M-SSS

其中 MM 代表当前月份,SSSS 代表当月的订单顺序。例如,1-002 代表一月份提交的第二个订单。

使用 TRIGGER 我希望自动递增和插入透明地工作。

不幸的是,我已经很久没有接触存储过程了,这是我第一次接触 postgresql。任何指向正确方向的帮助将不胜感激。

更新:这是使用@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;

注意:通过将更新添加为 BEFORE INSERT 触发器,它以完全透明和可预测的方式运行,就像普通的 SERIAL 或 BIGSERIAL 字段一样。

最佳答案

你在找这样的东西吗?

-- 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' 那么

-- 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

关于postgresql - postgresql 自定义自增字段(发票/订单号),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16598959/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com