PostgreSQL 10 PL/pgSQL脚本WHILE循环语法错误求助

我在PostgreSQL 10环境下编写PL/pgSQL脚本，需求为列出数据库非系统模式、各模式下的基表，以及每个表的行数与MD5哈希值。因大表处理时内存溢出，改为分批处理后，测试时WHILE循环处报错：

ERROR: syntax error at or near "<"
WHILE offset < row_count LOOP

注释循环后代码可正常运行，原脚本代码如下：

CREATE OR REPLACE FUNCTION verify_migration()
RETURNS TABLE (schema_name TEXT, table_name TEXT, row_count BIGINT, table_md5 TEXT) AS $$
DECLARE
    schema_rec RECORD;
    table_rec RECORD;
    row_count BIGINT;
    batch_size INTEGER := 10000;
    offset INTEGER := 0;
    table_md5 TEXT;
    batch_md5 TEXT;
BEGIN
    FOR schema_rec IN
        SELECT s.schema_name
        FROM information_schema.schemata s
        WHERE s.schema_name NOT IN ('pg_catalog', 'information_schema')
    LOOP
        FOR table_rec IN
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = schema_rec.schema_name
              AND t.table_type = 'BASE TABLE'
        LOOP
            table_md5 := '';
            offset := 0;
            EXECUTE format('SELECT COUNT(*) FROM %I.%I', schema_rec.schema_name, table_rec.table_name)
            INTO row_count;

            WHILE offset < row_count LOOP
                EXECUTE format('
                    SELECT md5(string_agg(t::text, ''''))
                    FROM (
                        SELECT *
                        FROM %I.%I
                        ORDER BY 1
                        LIMIT %L OFFSET %L
                    ) t', schema_rec.schema_name, table_rec.table_name, batch_size, offset)
                INTO batch_md5;
                
                IF batch_md5 IS NOT NULL THEN
                    table_md5 := md5(COALESCE(table_md5, '') || batch_md5);
                END IF;
                
               offset := offset + batch_size;
            END LOOP;
            
            RETURN QUERY
            SELECT
                schema_rec.schema_name::TEXT,       -- Cast explicite en TEXT
                table_rec.table_name::TEXT,         -- Cast explicite en TEXT
                row_count,                          -- deja un BIGINT
                table_md5;                          -- déjà en type TEXT
        END LOOP;
    END LOOP;
END $$ LANGUAGE plpgsql;

错误原因 #

脚本中WHILE循环条件里的<是HTML转义字符，PL/pgSQL无法识别这个转义后的符号，必须直接使用原生的小于号<。

修正后的脚本 #

CREATE OR REPLACE FUNCTION verify_migration()
RETURNS TABLE (schema_name TEXT, table_name TEXT, row_count BIGINT, table_md5 TEXT) AS $$
DECLARE
    schema_rec RECORD;
    table_rec RECORD;
    row_count BIGINT;
    batch_size INTEGER := 10000;
    offset INTEGER := 0;
    table_md5 TEXT;
    batch_md5 TEXT;
BEGIN
    FOR schema_rec IN
        SELECT s.schema_name
        FROM information_schema.schemata s
        WHERE s.schema_name NOT IN ('pg_catalog', 'information_schema')
    LOOP
        FOR table_rec IN
            SELECT t.table_name
            FROM information_schema.tables t
            WHERE t.table_schema = schema_rec.schema_name
              AND t.table_type = 'BASE TABLE'
        LOOP
            table_md5 := '';
            offset := 0;
            EXECUTE format('SELECT COUNT(*) FROM %I.%I', schema_rec.schema_name, table_rec.table_name)
            INTO row_count;

            WHILE offset < row_count LOOP
                EXECUTE format('
                    SELECT md5(string_agg(t::text, ''''))
                    FROM (
                        SELECT *
                        FROM %I.%I
                        ORDER BY 1
                        LIMIT %L OFFSET %L
                    ) t', schema_rec.schema_name, table_rec.table_name, batch_size, offset)
                INTO batch_md5;
                
                IF batch_md5 IS NOT NULL THEN
                    table_md5 := md5(COALESCE(table_md5, '') || batch_md5);
                END IF;
                
               offset := offset + batch_size;
            END LOOP;
            
            RETURN QUERY
            SELECT
                schema_rec.schema_name::TEXT,       -- 显式转换为TEXT
                table_rec.table_name::TEXT,         -- 显式转换为TEXT
                row_count,                          -- 已是BIGINT类型
                table_md5;                          -- 已是TEXT类型
        END LOOP;
    END LOOP;
END $$ LANGUAGE plpgsql;

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