Evaluate SELECT clause embedded in each row

Question

I have a table like this:

Table: survey

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

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)

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

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;

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.

similar problem: https://stackoverflow.com/questions/26063328/how-to-evaluate-expression-in-select-statement-in-postgres — Turo, Jun 08 '20 at 11:21

score 3 · Accepted Answer · answered Jun 08 '20 at 11:44

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

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.

@Bing-hsuGao You are not obliged to do so, though, if my answer solved your problem, you can accept it. — Tomáš Záluský, Jun 12 '20 at 07:07

score 0 · Answer 2 · answered Jun 08 '20 at 11:32

I don't think your function works, because values %s will expand to e.g. (values ("var1 + var2",12,9,2) which is invalid SQL.

However, if the number of "variables" is fixed, you can pass a record to the function that is then evaluate that as part of another query:

CREATE or replace FUNCTION cpt_scores(p_row survey) 
  RETURNS int
AS
$$
DECLARE
  score   numeric;
  in_rec  record;
  l_sql text;
BEGIN
  l_sql := format('SELECT %s FROM (values (%s, %s, %s)) AS t(var1, var2, var3)', 
                   p_row.formula, p_row.var1, p_row.var2, p_row.var3);
  execute l_sql INTO score;
  return score;
END;  
$$ 
language plpgsql;

Note that the formula is not included in the "expansion" of the values.

Then you can use it like this:

select s.*, cpt_scores(s) as score
from survey s;

Online example

if we can introspect the current row, get the columns names. and compose the string ("SELECT %s FROM ....") dynamically then this function could work for all tables wiith formula and variables in the same row. — Bing-hsu Gao, Jun 10 '20 at 01:05

Evaluate SELECT clause embedded in each row

2 Answers2

Linked