PostgreSQL 中递增多列序列

PostgreSQL 中递增多列序列

sql
-- 创建递增多列序列的函数
CREATE OR REPLACE FUNCTION increment_multi_column_sequence(
    sequence_name text,
    column_names text[],
    increment integer DEFAULT 1
)
RETURNS TRIGGER AS $$
DECLARE
    max_values RECORD;
    new_values RECORD;
BEGIN
    EXECUTE 'SELECT ' || array_to_string(column_names, ',') || ' FROM ' || TG_TABLE_NAME ||
            ' ORDER BY ' || array_to_string(column_names, ',') || ' DESC LIMIT 1' INTO max_values;
    FOR i IN 1 .. array_length(column_names, 1) LOOP
        new_values := max_values;
        new_values := new_values || (i, max_values[i] + increment);
        NEW := new_values;
        EXECUTE 'UPDATE ' || TG_TABLE_NAME || ' SET ' || column_names[i] || ' = $1 WHERE ' ||
                array_to_string(column_names, ' = $1 AND ') || ' = $1' USING NEW;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

sql
-- 创建示例表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_number INTEGER,
    order_line INTEGER
);
-- 创建触发器
CREATE TRIGGER orders_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION increment_multi_column_sequence('orders_order_number_order_line', ARRAY['order_number', 'order_line']);