CODEWITHSUNDEEP
postgresqldynamic-sql
Harsh Akshit
Harsh Akshit

Reputation: 15

How to execute an (expression involving columns of the same table) stored in another column of the table?

My table will look like this-

id     | expression                           | unit_cost | demand |total_cost|
------ | -------------------------------------| ----------|--------|----------|
1      | (unit_cost*4)*demand                 | 5         |100     |          |
2      | (unit_cost*(8/100)demand)*demand     | 10        |50      |          |

Now, I want to calculate total_cost based on the expression column using the other columns as specified in the expression. Changes in schema can be done, its just a sample to show what i actually want to do.

Note: expressions will be different for each row

Upvotes: 1

Views: 58

Answers (1)

user330315
user330315

Reputation:

You can use a function like this: 

create or replace function eval(p_row the_table) 
  returns integer
as
$body$
declare
  l_result integer;
  l_sql text;
begin

  l_sql := format('select %s from (values ($1, $2) ) as t(unit_cost, demand)', 
                   p_row.expression);

  execute l_sql
    into l_result
    using p_row.unit_cost, p_row.demand;

  return l_result;
end;
$body$
language plpgsql;

(You need to replace the_table with the actual name of your table)

I decided to pass the complete row of the table as the parameter, so that you don't need to change the anything if you decide to use more columns from the table in your expressions.

The generated SQL looks like this (e.g. for the first row):

select (unit_cost*4)*demand 
from ( values ($1, $2)) as t(unit_cost, demand);

The parameters in the values clause are then passed with the using ... part to make sure they are treated with the correct data types, which means it's executed as:

select (unit_cost*4)*demand 
from ( values (5, 100)) as t(unit_cost, demand);

You can use it like this: 

select t.id, t.unit_cost, t.demand, eval(t) as total_cost
from the_table t;

Note the table alias that is used to pass the row to the function.

If you know that the input values never change, you can also pass them directly:

create or replace function eval(p_expression text, p_demand int, p_unit_cost int)
   returns integer
as
$body$
declare
  l_result integer;
  l_sql text;
begin

  l_sql := format('select %s from (values ($1, $2) ) as t(unit_cost, demand)',
                  p_expression);

  execute l_sql
    into l_result
    using p_unit_cost, p_demand;
  return l_result;
end;
$body$
language plpgsql;

Then call it like this:

select id, unit_cost, demand, eval(t.expression, t.demand, t.unit_cost) as total_cost
from the_table t;

The first version (where you pass the complete row) has the advantage that you can never mix up the order of the parameters and accidentally pass the demand as the unit cost.

Online example

Upvotes: 1

Related Questions