评估嵌入在每一行中的 SELECT 子句 [英] Evaluate SELECT clause embedded in each row

问题描述

我有一张这样的桌子:

Table: survey

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

诀窍是formula列中的值可以用作针对其行的SELECT子句以获得最终分数，例如:

The trick is that the values inside column formula can be used as the SELECT clause against its row to get the final score, for example:

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

我正在开发一个 sql 程序来动态计算每一行的分数.

I am working on a sql procedure to dynamically compute the score for each row.

我当前的解决方案是遍历每一行并使用 EXECUTE 来评估每个公式.代码如下:

My current solution is to loop over each row and use EXECUTE to evaluate each formula. Here is the code:

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 子句中的硬编码列名会在有太多指标无法手动输入时造成麻烦.

I would like to know if there is any better way to handle this task. I think that the hard-coded column names in that FROM clause can cause trouble when there are too many metrics to type manually.

推荐答案

您可以利用 query_to_xml 函数并使用您的 formula 列合成其参数.查看示例(dbfiddle here):

You can utilize query_to_xml function and synthetize its argument using your formula column. See example (dbfiddle here):

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 列，这是更合适的.

Explanation: Synthetized query works on single row of original table. Since it has from t clause, it has access to any needed column. To pass proper row from outer query, my example uses ctid system column. I hope you actually have id column in your table which is more appropriate.

这篇关于评估嵌入在每一行中的 SELECT 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！