plpgsql

PL/pgSQL 核心知识

深入了解：使用 mcp__documentation__fetch_docs，并将技术指定为 postgresql，以获取全面的文档。

基本结构

CREATE OR REPLACE FUNCTION function_name(param1 type, param2 type)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- Variable declarations
    var1 type;
    var2 type := default_value;
BEGIN
    -- Function body
    RETURN result;
END;
$$;

函数

基本函数

CREATE OR REPLACE FUNCTION get_user_name(user_id INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
    user_name VARCHAR;
BEGIN
    SELECT name INTO user_name
    FROM users
    WHERE id = user_id;

    RETURN user_name;
END;
$$;

-- Usage
SELECT get_user_name(1);

返回多个值的函数

CREATE OR REPLACE FUNCTION get_user_info(p_user_id INT)
RETURNS TABLE(name VARCHAR, email VARCHAR, order_count BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.name, u.email, COUNT(o.id)::BIGINT
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.id = p_user_id
    GROUP BY u.id;
END;
$$;

-- Usage
SELECT * FROM get_user_info(1);

带 OUT 参数的函数

CREATE OR REPLACE FUNCTION calculate_stats(
    IN p_user_id INT,
    OUT total_orders INT,
    OUT total_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO total_orders, total_amount
    FROM orders
    WHERE user_id = p_user_id;
END;
$$;

-- Usage
SELECT * FROM calculate_stats(1);

SETOF 函数（多行）

CREATE OR REPLACE FUNCTION get_active_users()
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = 'active';
END;
$$;

-- Usage
SELECT * FROM get_active_users();

过程（PostgreSQL 11+）

CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INT,
    receiver_id INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Deduct from sender
    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;

    -- Add to receiver
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

    -- Commit transaction
    COMMIT;
END;
$$;

-- Usage
CALL transfer_funds(1, 2, 100.00);

变量和类型

DECLARE
    -- Scalar types
    v_count INT := 0;
    v_name VARCHAR(100);
    v_amount NUMERIC(10,2) DEFAULT 0.00;
    v_active BOOLEAN := TRUE;
    v_created TIMESTAMP := NOW();

    -- Type from column
    v_email users.email%TYPE;

    -- Type from row
    v_user users%ROWTYPE;

    -- Record (dynamic)
    v_record RECORD;

    -- Array
    v_ids INT[] := ARRAY[1, 2, 3];

    -- Constant
    c_tax_rate CONSTANT NUMERIC := 0.21;
BEGIN
    -- ...
END;

控制结构

IF 语句

IF condition THEN
    -- statements
ELSIF another_condition THEN
    -- statements
ELSE
    -- statements
END IF;

-- Example
IF v_count > 100 THEN
    v_status := 'high';
ELSIF v_count > 50 THEN
    v_status := 'medium';
ELSE
    v_status := 'low';
END IF;

CASE 语句

CASE expression
    WHEN value1 THEN
        -- statements
    WHEN value2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

-- Searched CASE
CASE
    WHEN condition1 THEN
        -- statements
    WHEN condition2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

循环

-- Simple loop
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;

-- WHILE loop
WHILE condition LOOP
    -- statements
END LOOP;

-- FOR loop (integer range)
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR loop (reverse)
FOR i IN REVERSE 10..1 LOOP
    -- statements
END LOOP;

-- FOR loop (query result)
FOR v_record IN SELECT * FROM users WHERE status = 'active' LOOP
    RAISE NOTICE 'User: %', v_record.name;
END LOOP;

-- FOREACH (arrays)
FOREACH v_id IN ARRAY v_ids LOOP
    RAISE NOTICE 'ID: %', v_id;
END LOOP;

异常处理

BEGIN
    -- Statements that might fail
    INSERT INTO users (email) VALUES (p_email);
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Email already exists: %', p_email;
        RETURN NULL;
    WHEN not_null_violation THEN
        RAISE EXCEPTION 'Email cannot be null';
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Unexpected error: % %', SQLERRM, SQLSTATE;
END;

常见异常代码

异常	描述
unique_violation	键重复
not_null_violation	NOT NULL 列中出现 NULL
foreign_key_violation	外键约束失败
check_violation	CHECK 约束失败
division_by_zero	除以零
no_data_found	SELECT INTO 未返回任何行
too_many_rows	SELECT INTO 返回了多行

抛出异常

-- Notice (info)
RAISE NOTICE 'Processing user %', v_user_id;

-- Warning
RAISE WARNING 'Value seems too high: %', v_amount;

-- Exception (stops execution)
RAISE EXCEPTION 'Invalid user ID: %', v_user_id;

-- With error code
RAISE EXCEPTION 'Invalid input' USING ERRCODE = 'invalid_parameter_value';

触发器

基本触发器

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_users_update
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

触发器变量

变量	描述
NEW	新行（INSERT/UPDATE）
OLD	旧行（UPDATE/DELETE）
TG_OP	操作：INSERT、UPDATE、DELETE
TG_TABLE_NAME	表名
TG_WHEN	BEFORE 或 AFTER

审计触发器

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_changes();

条件触发器

CREATE TRIGGER trg_orders_notify
    AFTER INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.total > 1000)
    EXECUTE FUNCTION notify_high_value_order();

动态 SQL

CREATE OR REPLACE FUNCTION search_table(
    p_table TEXT,
    p_column TEXT,
    p_value TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT * FROM %I WHERE %I = $1',
        p_table, p_column
    ) USING p_value;
END;
$$;

-- With EXECUTE INTO
DECLARE
    v_count INT;
BEGIN
    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(p_table)
    INTO v_count;
END;

游标

CREATE OR REPLACE FUNCTION process_orders()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    v_cursor CURSOR FOR SELECT * FROM orders WHERE status = 'pending';
    v_order orders%ROWTYPE;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_order;
        EXIT WHEN NOT FOUND;

        -- Process order
        UPDATE orders SET status = 'processing' WHERE id = v_order.id;
    END LOOP;
    CLOSE v_cursor;
END;
$$;

-- FOR loop cursor (auto open/close)
FOR v_order IN SELECT * FROM orders WHERE status = 'pending' LOOP
    -- Process
END LOOP;

最佳实践

应该做

• 使用 %TYPE 和 %ROWTYPE 确保类型安全
• 预期恰好返回一行时，在 SELECT INTO 中使用 STRICT
• 在动态 SQL 中使用 format() 和 %I 处理标识符
• 使用异常块处理错误
• 对于多行结果，使用 RETURNS SETOF 或 RETURNS TABLE

不应该做

• 使用字符串拼接构造动态 SQL（存在 SQL 注入风险）
• 忽略异常
• 集合操作可行时仍使用游标
• 创建具有副作用但命名不清晰的函数

何时不应使用此技能

• 基本 PostgreSQL SQL - 使用 postgresql 技能编写查询、创建索引和处理数据类型
• PL/SQL（Oracle） - 使用 plsql 技能编写 Oracle 过程
• T-SQL（SQL Server） - 使用 tsql 技能编写 SQL Server 过程
• 基本 SQL - 使用 sql-fundamentals 学习 ANSI SQL 基础知识

反模式

反模式	问题	解决方案
未进行清理的动态 SQL	SQL 注入	使用 quote_ident/quote_literal 或 format()
未处理异常	静默失败	添加 EXCEPTION 块
使用显式游标循环	代码运行较慢	使用 FOR...IN 循环
忽略 FOUND 变量	逻辑错误	查询后检查 FOUND
不使用 %TYPE/%ROWTYPE	类型不匹配	使用列类型/行类型
SELECT INTO 未使用 STRICT	意外的 NULL	添加 STRICT 或检查 FOUND

快速故障排除

问题	诊断	修复
函数意外返回 NULL	检查 FOUND 变量	添加 NOT FOUND 处理
“query returned more than one row”	SELECT INTO 返回了多行	添加 LIMIT 1 或使用 FOR 循环
“column does not exist”	区分大小写问题	使用双引号括起标识符
触发器未触发	检查触发器状态	ALTER TRIGGER ... ENABLE
性能问题	对查询使用 EXPLAIN ANALYZE	优化 SQL，添加索引

参考文档

• 过程
• 函数
• 触发器