TimescaleDB time_bucket输出转JSON数组的动态SQL优化与多变量通用实现

临时表中转环节的必要性与并发风险 #

• 会话级临时表完全是冗余设计，根本不是实现该功能的必须环节。动态SQL查询得到的聚合结果，可直接在函数执行链路中生成JSON结构返回，根本不需要落地到临时表做中转存储，额外的临时表创建/读写流程只会增加不必要的事务开销与IO成本。
• 固定临时表名带来的并发风险是真实存在的：尽管PostgreSQL的临时表天然按会话做了隔离（不同会话无法看到对方的临时表），但如果函数内写死了固定的临时表名，尤其是搭配CREATE TEMP TABLE IF NOT EXISTS且不做旧数据清空的写法，在连接池复用同一会话的场景下，前一次调用残留的表数据会直接混入后续查询结果，引发数据串扰。
• 最直接的解决方案就是把整个临时表中转的流程删掉，从根源上消除这类风险。

冗余变量赋值的简化实现 #

• 原实现中“先把动态SQL结果赋值给声明变量、再返回变量”的写法没有实际价值，属于plpgsql开发中的常见冗余写法。
• plpgsql本身支持直接将动态SQL的执行结果作为函数返回值，完全不需要单独声明中间变量承接：

  -- 原冗余写法
  DECLARE
    result json;
  BEGIN
    EXECUTE build_query_sql() INTO result;
    RETURN result;
  END
  

  可直接简化为：

  BEGIN
    -- 单值JSON返回场景直接执行返回
    RETURN EXECUTE build_query_sql();
    -- 返回多行数据集的场景，用RETURN QUERY EXECUTE即可
  END
  

• 这种写法既减少了无意义的代码行数，也避免了变量类型不匹配导致的隐式转换额外开销。

多指标查询能力的泛化改造 #

• 原实现硬编码2个指标的查询逻辑，可通过入参调整、动态SQL拼接的方式，零成本扩展为支持1到任意数量的指标查询，具体改造路径：
  • 入参重构：将原来两个独立的单值指标名入参，替换为text[]类型的数组入参，调用方可以灵活传入任意数量的目标指标名，不需要为不同指标数量维护多个函数。
  • 安全逻辑保留：遍历传入的指标数组，对每个指标名执行quote_ident()处理后再拼入动态SQL的聚合片段，完全延续原实现防SQL注入的安全逻辑，不会因为泛化改造降低安全性。
  • 结构兼容性：动态拼接聚合逻辑时，不硬编码固定的指标列，而是根据传入的指标数组，逐个生成avg(指标列名)的聚合计算片段；最终生成JSON结果时，通过jsonb_object_agg自动将每个指标列映射为对应名称的数值数组，输出的JSON格式与原固定2个指标的结构完全兼容，前端调用侧不需要做任何适配调整。
  • 泛化后的核心动态SQL逻辑参考：

    SELECT
      jsonb_build_object(
        'time_buckets', jsonb_agg(bucket_ts ORDER BY bucket_ts),
        'metrics', metric_value_collection
      )
    FROM (
      -- 基于传入的指标数组动态生成聚合列，无硬编码指标
      SELECT time_bucket($1, ts) AS bucket_ts,
             avg(speed) AS speed,
             avg(temperature) AS temperature,
             avg(pressure) AS pressure
      FROM 目标时序表
      WHERE ts BETWEEN $2 AND $3
      GROUP BY bucket_ts
    ) raw_bucket
    CROSS JOIN LATERAL (
      -- 自动把所有指标列转成键值对结构，不需要逐个硬编码
      SELECT jsonb_object_agg(key, val ORDER BY bucket_ts) AS metric_value_collection
      FROM jsonb_each(to_jsonb(raw_bucket) - 'bucket_ts') AS x(key, val)
    ) metric_transform
    

• 改造完成后，无论调用方传入1个指标还是10个指标，函数都能输出结构一致的结果，不需要为指标数量调整函数本体的代码。

内容的提问来源于stack exchange，提问作者Héctor