PostgreSQL传参调用TRUNCATE TABLE函数无生效问题排查

你写的多版函数存在3个明确错误，外加1个调用参数传错的问题，共同导致无报错但不截断数据的现象：

• 初版函数TRUNCATE时未指定schema，数据库会默认在search_path配置的路径下找表，根本不会定位到你目标schema下的表，匹配不到自然无操作
• 更新1版本的SQL语法顺序错误：TRUNCATE TABLE是固定关键字顺序，你写的TRUNCATE %i.TABLE 表名是非法语法，且标识符转义要用大写%I格式符，小写%i不会做合规的标识符转义
• 更新2版本把执行动态SQL的关键字EXECUTE写成了PERFORM：PERFORM仅会计算表达式值然后丢弃结果，你拼出来的TRUNCATE字符串根本没被执行，等于空跑循环
• 调用命令参数传错：你函数第二个入参是schema名，但调用时传的是$DB_DATABASE（数据库名），数据库和schema是完全不同的层级，pg_tables里查不到对应schemaname的记录，循环根本不会进入

函数定义 #

CREATE OR REPLACE FUNCTION truncate_tables(dbUserName text, dbSchema text) 
RETURNS void LANGUAGE plpgsql AS $function$
DECLARE
    current_table record;
BEGIN
    -- 增加schema存在性校验，避免参数传错无感知
    IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = dbSchema) THEN
        RAISE EXCEPTION '指定schema「%」不存在，请检查入参', dbSchema;
    END IF;

    FOR current_table IN 
        SELECT tablename 
        FROM pg_tables
        WHERE tableowner = dbUserName
          AND schemaname = dbSchema
          AND tablename NOT LIKE 'flyway%'
    LOOP
        -- 用%I自动转义schema、表名标识符，避免特殊字符、关键字冲突问题，加CASCADE级联截断关联表，RESTART IDENTITY重置自增序列
        EXECUTE format('TRUNCATE TABLE %I.%I RESTART IDENTITY CASCADE;', dbSchema, current_table.tablename);
        RAISE NOTICE '已完成表截断：%.%', dbSchema, current_table.tablename;
    END LOOP;
END
$function$;

修正后的Shell调用命令 #

注意第二个入参要传schema名，不要传数据库名：

docker exec $CONTAINER_NAME psql -U dev -d $DB_DATABASE -v ON_ERROR_STOP=1 -c "select $DB_SCHEMA.truncate_tables('$DB_USERNAME','$DB_SCHEMA');"

如果需要确认执行逻辑，可以手动在psql客户端开启notice日志后调用函数，会逐行打印被操作的表名：

SET client_min_messages = notice;
-- 替换成自己的schema、用户名参数
SELECT public.truncate_tables('dev', 'public');

内容的提问来源于stack exchange，提问作者Ren