PostgreSQL 12.15中基于JSONB参数动态生成SQL查询（WHERE子句求助）

先明确参数结构（基于你提到的场景），假设输入JSONB参数格式如下：

{
  "MODULE": "sales",
  "RESULT_ATTRIB_NAME": "amount",
  "AGGREGATE_OP": "SUM",
  "CONDITIONAL_ATTRIBS": {
    "region": "north",
    "year": 2023,
    "is_active": true
  }
}

期望生成的SQL类似：

SELECT SUM(amount) FROM sales WHERE region = 'north' AND year = 2023 AND is_active = true;

核心实现：动态WHERE子句的安全生成 #

要避免SQL注入，必须使用参数化查询结合标识符转义，不能直接拼接字符串。以下是完整函数代码：

CREATE OR REPLACE FUNCTION getjsontesterabledata(p_params JSONB)
RETURNS NUMERIC -- 若需适配多聚合类型，可改为RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
  v_table TEXT := p_params->>'MODULE';
  v_result_col TEXT := p_params->>'RESULT_ATTRIB_NAME';
  v_agg_op TEXT := p_params->>'AGGREGATE_OP';
  v_cond_json JSONB := p_params->'CONDITIONAL_ATTRIBS';
  v_sql TEXT;
  v_cond_clause TEXT := '';
  v_args TEXT[];
  v_arg_count INT := 0;
BEGIN
  -- 处理WHERE子句：遍历CONDITIONAL_ATTRIBS生成参数化条件
  IF v_cond_json IS NOT NULL AND v_cond_json != '{}'::JSONB THEN
    FOR v_col, v_val IN SELECT key, value FROM jsonb_each(v_cond_json) LOOP
      v_arg_count := v_arg_count + 1;
      -- 拼接条件片段，标识符用quote_ident转义避免关键字冲突
      IF v_arg_count > 1 THEN
        v_cond_clause := v_cond_clause || ' AND ';
      END IF;
      v_cond_clause := v_cond_clause || format('%I = $%s', quote_ident(v_col), v_arg_count);
      -- 将JSON值转为TEXT存入参数数组，PostgreSQL会自动隐式转换为对应字段类型
      v_args := array_append(v_args, v_val::TEXT);
    END LOOP;
    v_cond_clause := ' WHERE ' || v_cond_clause;
  END IF;

  -- 拼接完整SQL语句
  v_sql := format(
    'SELECT %I(%I) FROM %I %s',
    quote_ident(v_agg_op),
    quote_ident(v_result_col),
    quote_ident(v_table),
    v_cond_clause
  );

  -- 执行动态SQL并返回结果
  EXECUTE v_sql INTO v_result USING UNNEST(v_args);
  RETURN v_result;
END;
$$;

关键细节说明 #

• 标识符安全：所有表名、字段名、聚合函数名都用quote_ident()转义，防止因标识符含特殊字符或关键字导致的语法错误，同时避免标识符注入。
• 参数化条件：WHERE子句的每个条件都用$1、$2占位符，实际值通过USING UNNEST(v_args)传入，彻底规避SQL注入风险。
• 空条件处理：如果CONDITIONAL_ATTRIBS为空或不存在，WHERE子句会自动省略，直接执行聚合查询。
• 类型适配：JSON值转为TEXT后传入，PostgreSQL会根据表字段类型自动隐式转换，需确保CONDITIONAL_ATTRIBS的值类型与目标字段类型匹配。

函数调用示例 #

SELECT getjsontesterabledata('{
  "MODULE": "sales",
  "RESULT_ATTRIB_NAME": "amount",
  "AGGREGATE_OP": "SUM",
  "CONDITIONAL_ATTRIBS": {
    "region": "north",
    "year": 2023,
    "is_active": true
  }
}'::JSONB);

扩展优化（可选） #

1. 通用返回类型：如果需要支持不同聚合操作的返回类型（如COUNT返回INT、AVG返回NUMERIC），可将函数返回类型改为JSONB，把结果包装成JSON返回：

   -- 修改RETURNS为JSONB，执行部分调整为：
   EXECUTE v_sql INTO v_result USING UNNEST(v_args);
   RETURN to_jsonb(v_result);
   

2. 多运算符支持：若需支持不等于、大于等运算符，可修改CONDITIONAL_ATTRIBS结构为{"col": {"op": ">", "val": 100}}，遍历解析时加入运算符逻辑。

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