将SQL Server动态存储过程转换为PostgreSQL等效PL/SQL

需求背景 #

原SQL Server存储过程usp_getrowcounts用于动态统计dbo schema下非下划线开头的基表行数，用于审计SQL Server到PostgreSQL的复制同步状态，需转换为PostgreSQL等效实现。

精确统计实现（PL/pgSQL函数） #

以下是对应原逻辑的PostgreSQL函数，生成准确的行数统计：

CREATE OR REPLACE FUNCTION usp_getrowcounts()
RETURNS TABLE(tablename text, rows bigint)
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql text;
BEGIN
    -- 清理并创建临时结果表
    DROP TABLE IF EXISTS temp_tablerows;
    CREATE TEMP TABLE temp_tablerows (
        tablename text,
        rows bigint
    );

    -- 拼接动态SQL：为每个符合条件的表生成插入统计语句
    SELECT string_agg(
        format(
            'INSERT INTO temp_tablerows SELECT ''%I'', COUNT(*) FROM %I.%I;',
            table_name,
            table_schema,
            table_name
        ),
        ' '
    ) INTO v_sql
    FROM information_schema.tables
    WHERE table_schema = 'dbo'
      AND table_type = 'BASE TABLE'
      AND left(table_name, 1) != '_';

    -- 执行动态SQL（若存在符合条件的表）
    IF v_sql IS NOT NULL THEN
        EXECUTE v_sql;
    END IF;

    -- 返回排序后的统计结果
    RETURN QUERY SELECT tablename, rows FROM temp_tablerows ORDER BY tablename;
END;
$$;

使用方式 #

调用函数直接获取结果：

SELECT * FROM usp_getrowcounts();

快速近似统计（可选） #

如果不需要绝对精确的行数，仅用于快速同步状态审计，可以利用PostgreSQL的系统统计视图，速度更快且非阻塞：

CREATE OR REPLACE FUNCTION usp_getrowcounts_fast()
RETURNS TABLE(tablename text, rows bigint)
LANGUAGE sql
AS $$
SELECT relname AS tablename, n_live_tup AS rows
FROM pg_stat_user_tables
WHERE schemaname = 'dbo'
  AND left(relname, 1) != '_'
ORDER BY relname;
$$;

关键差异说明 #

1. 标识符处理：用format函数的%I占位符自动转义表名/ schema名，替代SQL Server的[]，避免SQL注入和标识符冲突。
2. 动态SQL拼接：使用string_agg批量拼接语句，比循环拼接更高效。
3. 临时表：PostgreSQL临时表默认会话级生命周期，无需手动清理（也可添加ON COMMIT DROP让事务结束后销毁）。
4. Nolock替代：PostgreSQL无直接等效的NOLOCK，默认READ COMMITTED隔离级别已保证读操作不会被行级写锁长期阻塞；若需完全非阻塞，推荐使用快速近似统计方案。

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