必须用security definer，因为跨schema操作、写审计日志或truncate等高权限动作需以有权限的owner身份执行；invoker模式下调用者权限不足会直接报错，而definer模式通过显式委托（owner须预先授予权限）安全实现操作。
为什么必须用 SECURITY DEFINER 而不是默认的 SECURITY INVOKER
当你写的存储过程要操作跨 schema 的表、写入审计日志、或执行 TRUNCATE 等需要高权限的操作时，调用者自身往往没有这些权限。默认的 SECURITY INVOKER 会让过程以调用者身份运行，一执行就报 permission denied for table xxx。而 SECURITY DEFINER 允许你指定一个有足够权限的 owner（比如 postgres 或专用运维角色），过程内部所有 SQL 都按这个 owner 权限执行。
注意：这不是权限“提升”，而是显式委托——owner 必须提前被授予对应对象的 USAGE、SELECT、INSERT 等权限，否则即使加了 SECURITY DEFINER 也照样失败。
- 不要在创建后手动
GRANT EXECUTE给 public；应只授给可信角色 - 避免在
SECURITY DEFINER过程里拼接用户输入进动态 SQL（如EXECUTE 'UPDATE ' || quote_ident(tablename)），否则可能触发权限绕过 - 若过程只读且不涉及敏感表，优先用
SECURITY INVOKER，更安全、更易审计
CALL 调用存储过程时传参和接收 OUT 参数的实际写法
PostgreSQL 存储过程不返回值，但可以通过 OUT 或 INOUT 参数把结果“带出来”。关键在于：调用时必须显式声明变量来接收，不能像函数那样直接 SELECT。
示例：一个带统计输出的过程
CREATE OR REPLACE PROCEDURE count_active_users(
OUT total_count bigint,
IN min_last_login timestamp DEFAULT NOW() - INTERVAL '30 days'
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
SELECT COUNT(*) INTO total_count
FROM users
WHERE last_login >= min_last_login AND status = 'active';
END;
$$;
调用它必须这样写（在 psql 或支持匿名块的客户端中）：
DO $$
DECLARE
v_total bigint;
BEGIN
CALL count_active_users(v_total, NOW() - INTERVAL '7 days');
RAISE NOTICE '活跃用户数: %', v_total;
END $$;
-
CALL本身不返回结果集，所以不能用SELECT * FROM ...包裹 - 如果只用
IN参数，可直接CALL proc_name(arg1, arg2)，无需声明变量 -
OUT参数变量类型必须严格匹配定义，否则报function argument type mismatch
PL/pgSQL 中批量操作的性能陷阱：别用 LOOP 遍历小数组
很多人看到“批量更新”第一反应是写 FOREACH + LOOP，比如对 100 个 ID 做逐条 UPDATE。这在数据量小时看似可行，但实际会触发 100 次独立的 SQL 解析、计划生成和锁获取，性能比单条语句差 5–10 倍。
真正高效的做法是把逻辑压进一条 SQL —— 尤其当输入是数组时，用 UNNEST() 或 = ANY()。
低效写法（避免）：
FOREACH uid IN ARRAY user_ids LOOP
UPDATE orders SET status = 'shipped' WHERE id = uid;
END LOOP;
高效写法（推荐）：
UPDATE orders SET status = 'shipped' WHERE id = ANY(user_ids);
- 如果业务逻辑复杂到必须逐行判断（比如不同 ID 要设不同状态），再考虑
LOOP，但务必加上PERFORM pg_sleep(0.001)防止单次过程执行太久阻塞其他连接 - 数组长度超过 1000 时，
= ANY()可能退化为顺序扫描，此时应改用临时表 +JOIN - 所有批量操作都应在事务块内完成，
SECURITY DEFINER过程里更要显式加BEGIN ... EXCEPTION ... END处理部分失败
调试 SECURITY DEFINER 过程时最容易忽略的三件事
权限问题不是“开了 DEFINER 就万事大吉”，很多线上故障源于以下疏忽：
- 过程 owner 的 search_path 没设好：比如 owner 是
postgres，但postgres的search_path是"$user", public，而你的表在auditschema 下——结果就是relation "log_table" does not exist。解决方法：创建时显式指定SET search_path = 'audit, public' - 过程里用了临时表（
CREATE TEMP TABLE），但SECURITY DEFINER过程无法访问调用者的 session 级临时表，必须改用普通表或UNLOGGED表 - 过程依赖的函数/视图本身也是
SECURITY DEFINER，但它们的 owner 权限没同步更新——导致“权限链断裂”。检查方式：SELECT proname, proowner::regrole, prosecdef FROM pg_proc WHERE proname = 'xxx';
最稳妥的调试路径：先用 owner 账号手动执行过程体内的每条 SQL，确认全通；再切回普通用户 CALL 测试；最后在应用层捕获 SQLSTATE 错误码而非只看消息文本。
