02-PostgreSQL 存储过程的进阶介绍（含游标、错误处理、自定义函数、事务）

CREATE OR REPLACE FUNCTION insert_product(_id UUID, _name TEXT, _price NUMERIC)
RETURNS VOID AS $$
BEGIN
    -- 尝试插入产品信息
    INSERT INTO products (id, name, price) VALUES (_id, _name, _price);
EXCEPTION
    WHEN unique_violation THEN
        -- 如果违反唯一性约束，则输出警告信息
        RAISE NOTICE 'Product with ID % already exists.', _id;
END;
$$ LANGUAGE plpgsql;
 
-- 创建或替换一个函数，用于更新产品信息，并处理潜在的错误
CREATE OR REPLACE FUNCTION update_product(_id UUID, _name TEXT, _price NUMERIC)
RETURNS VOID AS $$
BEGIN
    -- 尝试更新产品信息
    UPDATE products SET name = _name, price = _price WHERE id = _id;
    -- 如果没有找到对应的产品，则抛出自定义异常
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product with ID % does not exist.', _id;
    END IF;
EXCEPTION
    WHEN unique_violation THEN
        -- 如果违反唯一性约束，则输出警告信息
        RAISE NOTICE 'Product with name % already exists.', _name;
END;
$$ LANGUAGE plpgsql;
 
-- 创建或替换一个事务测试函数
CREATE OR REPLACE FUNCTION test_transaction()
RETURNS VOID AS $$
DECLARE
    error_occurred BOOLEAN;
BEGIN
    error_occurred := FALSE;
    -- 开始一个事务
    BEGIN;
        -- 尝试插入一些数据
        INSERT INTO products (id, name, price) VALUES (uuid_generate_v4(), 'Test Product 1', 99.99);
        -- 制造一个错误（例如，尝试使用一个不存在的UUID插入）
        INSERT INTO products (id, name, price) VALUES ('00000000-0000-0000-0000-000000000000'::UUID, 'Test Product 2', 99.99);
    EXCEPTION
        WHEN OTHERS THEN
            -- 如果发生错误，标记错误发生，并回滚事务
            error_occurred := TRUE;
            ROLLBACK;
            -- 重新抛出异常以向调用者报告错误
            RAISE;
    END;
    -- 如果没有错误发生，则提交事务
    IF NOT error_occurred THEN
        COMMIT;
    END IF;
END;
$$ LANGUAGE plpgsql;

这个代码实例展示了如何在PostgreSQL中创建存储过程，包括使用游标、错误处理、自定义函数和事务控制。每个函数都包含了简单的逻辑，用于演示如何处理特定的数据库操作。