PostgreSQL编写分页查询声明变量报integer附近语法错误

你碰到的ERROR: syntax error at or near "integer"是因为PostgreSQL中PL/pgSQL过程化代码不能直接作为普通SQL执行，必须包裹在DO匿名块、自定义函数或存储过程的代码体内。裸写DECLARE开头的变量声明段时，SQL解析器无法识别过程化语法，读到变量类型integer时就会抛出语法错误。

除了这个直接触发报错的问题，你的代码还存在多处PL/pgSQL语法和逻辑错误，全部修正后才能正常运行：

存在的其他问题 #

• 变量赋值语法错误：PL/pgSQL中给变量赋值不需要加SET关键字，直接使用变量名 := 值;格式即可，你写的Set rowsofpage :=3;属于无效语法。
• 查询结果赋值语法错误：需要将查询结果存入变量时，必须使用SELECT 字段 INTO 变量 FROM 表的格式，你写的select maxpagecount=count(*) from ...是其他数据库的语法，PostgreSQL中这个写法里的=会被识别为比较运算符，无法完成赋值。
• 循环结构语法错误：WHILE循环的固定结构是WHILE 条件表达式 LOOP 循环体 END LOOP;，你漏写了LOOP关键字，直接在条件后接BEGIN是无效写法。
• 整数除法计算错误：两个integer类型的值做除法时PostgreSQL会直接截断小数部分取整，计算总页数时需要先将总条数转为numeric类型再做除法，否则CEILING函数无法得到正确的总页数。
• 分页逻辑错误：你在循环中对rowsofpage（每页条数）做累加是逻辑错误，每页条数应该是固定值，循环中只需要累加页码即可。
• 语法细节缺失：多处语句末尾缺少必填的分号，匿名块/过程体需要用成对的字符串标签（通常用$$）包裹标识过程代码段。

修正后的可运行代码 #

DO $$
DECLARE
    maxpagecount integer;
    rowsofpage integer := 3; -- 声明变量时直接赋初始值更简洁
    pagenumber integer := 1;
    total_count integer;
BEGIN
    -- 查询数据总条数
    SELECT count(*) INTO total_count FROM UNLABELED_IMAGE_PREDICTION;
    -- 计算总页数，转numeric避免整数除法截断结果
    maxpagecount := CEILING(total_count::numeric / rowsofpage);

    WHILE maxpagecount >= pagenumber LOOP
        -- 匿名块中查询不存入变量的话需要用PERFORM，若需要返回结果可将逻辑写为函数用RETURN QUERY
        PERFORM x.image_id, x.score
        FROM (
            SELECT 
                *,
                row_number() OVER (ORDER BY score DESC) as row_count
            FROM UNLABELED_IMAGE_PREDICTION
        ) x
        OFFSET (pagenumber - 1) * rowsofpage ROWS
        FETCH FIRST rowsofpage ROWS ONLY;

        -- 累加页码进入下一页
        pagenumber := pagenumber + 1;
    END LOOP;
END $$;

补充说明：如果你需要拿到每一页的查询结果返回给客户端，不建议用DO匿名块，直接写普通分页查询即可，不需要用循环逐页取数；如果确实要在过程中循环处理每一页数据，可以把上述逻辑放到返回集合的自定义函数中，把PERFORM替换为RETURN QUERY就能正常返回结果集。

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