评估嵌入在每一行中的SELECT子句

一尘不染

评估嵌入在每一行中的SELECT子句

sql

我有一个这样的表：

Table: survey

|      formula           |  var1 |  var2  |  var3  |
|------------------------|-------|--------|--------|
|     var1 + var2        |   12  |    9   |    2   | 
|   var3 * (var1 * var2) |   20  |   10   |    1   |

诀窍是，formula可以将列中的值用作SELECT其行的子句以获取最终分数，例如：

SELECT var1 + var2 FROM (VALUE ('var1 + var2', 12, 9, 2) AS t(formula, var1, var2, var3)

我正在研究一个SQL过程来动态计算每一行的分数。

我当前的解决方案是遍历每一行并用于EXECUTE评估每个公式。这是代码：

CREATE FUNCTION cpt_scores() RETURNS SETOF scores as
$$
  DECLARE
    score   numeric;
    in_rec  record;
  BEGIN
    FOR in_rec IN
      SELECT * FROM survey
    LOOP
      EXECUTE format('SELECT %s FROM (VALUES %s) AS t(formula, var1, var2, var3)', in_rec.formula, in_rec) 
        INTO score
      RETURN NEXT ROW(score);
    END LOOP;
  END;
$$ language plpgsql;

我想知道是否有更好的方法来处理此任务。我认为FROM当有太多指标无法手动输入时，该子句中的硬编码列名可能会引起麻烦。

阅读 164

2021-05-16

共1个答案

一尘不染

您可以使用query_to_xml函数并使用您的formula列综合其参数。请参见示例（此处为dbfiddle）：

create table t (formula text,var1 int,var2 int,var3 int);
insert into t
  values ('     var1 + var2        ',   12  ,    9   ,    2   )
       , ('   var3 * (var1 * var2) ',   20  ,   10   ,    1   );
select *
     , (xpath('/row/f/text()',
          query_to_xml(
            'select ' || formula || ' as f from t where t.ctid = ''' || ctid || '''::tid'
            , false, true, ''
          )
       ))[1]::text::int as result
from t

说明：综合查询适用于原始表的单行。由于它具有from t子句，因此可以访问任何需要的列。为了从外部查询传递正确的行，我的示例使用ctid系统列。我希望您实际上id在表中有更合适的列。

2021-05-16