咨询PostgreSQL中LIMIT变量普通入参正常、表类型/JSON入参报错问题

问题背景 #

• 自定义表类型定义注意：原示例中两次创建同名tt_input会触发类型冲突，需调整为不同命名，参考定义：

create type tt_input as (id integer, batchsize integer);
create type tt_input_detail as (id integer, name character varying);

• 当LIMIT子句使用普通标量输入参数时，存储函数可正常运行
• 当LIMIT参数来自关联派生表（含JSON解析生成的表类型字段）时，抛出「LIMIT子句不支持包含变量」类报错

正常运行示例（标量参数场景） #

CREATE OR REPLACE FUNCTION sample_function(
    id integer,
    batchsize integer)
    RETURNS SETOF tt_output
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$  
DECLARE
        p_id alias for $1;
        p_batchsize alias for $2;
BEGIN 
    return query (select t.id, t.name from sample_table t 
                        where t.id = p_id 
                        limit p_batchsize);
END
$BODY$;

报错场景示例（派生列作为LIMIT参数） #

-- 原示例参数名与类型重名、类型定义冲突问题已做注释修正
CREATE OR REPLACE FUNCTION sample_function(
    tt_input json)
    RETURNS SETOF tt_output
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$  
BEGIN 
    return query (select t.id, t.name from sample_table t 
                        inner join json_populate_recordset(null::tt_input, tt_input::json) as r
                        on t.id = r.id 
                        limit r.batchsize);
END
$BODY$;

报错根本原因 #

PostgreSQL语法明确要求LIMIT/OFFSET子句仅支持常量、plpgsql声明的标量变量作为参数，不接受查询执行过程中产生的列引用。示例中r.batchsize是json_populate_recordset生成的派生列，属于查询内部列值，不符合LIMIT参数规范，因此触发报错。

对应解决方案 #

场景1：传入JSON为单条id+batchsize配置 #

提前将JSON解析的标量值赋值给plpgsql声明的标量变量，再传入LIMIT使用：

CREATE OR REPLACE FUNCTION sample_function(
    tt_input json)
    RETURNS SETOF tt_output
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$  
DECLARE
    v_id integer;
    v_batchsize integer;
BEGIN 
    -- 提前解析JSON值为plpgsql标量变量
    SELECT (r).id, (r).batchsize 
    INTO v_id, v_batchsize
    FROM json_populate_record(null::tt_input, tt_input) r;

    return query 
    select t.id, t.name 
    from sample_table t 
    where t.id = v_id 
    limit v_batchsize;
END
$BODY$;

场景2：传入JSON为多组id+batchsize配置 #

如果需要对每个匹配id分别返回对应batchsize数量的记录，不能使用全局LIMIT，需用窗口函数按id分组限制返回条数：

CREATE OR REPLACE FUNCTION sample_function(
    tt_input json)
    RETURNS SETOF tt_output
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$  
BEGIN 
    return query 
    with input_config as (
        -- 解析JSON为派生配置表
        select * from json_populate_recordset(null::tt_input, tt_input)
    ),
    matched_rows as (
        select 
            t.id, 
            t.name,
            -- 按id分组排序，计算行号
            row_number() over(partition by t.id order by t.id) as row_num,
            cfg.batchsize
        from sample_table t 
        inner join input_config cfg on t.id = cfg.id
    )
    -- 每个分组仅取行号小于等于对应batchsize的记录
    select id, name from matched_rows where row_num <= batchsize;
END
$BODY$;

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