You will need dynamic SQL for this.
As both, the values and column names are dynamic and also the context in which they are applied this is a bit tricky.
Something along the lines:
create or replace function evaluate_rule(p_row record, p_rule_id int)
returns boolean
as
$$
declare
l_expression text;
l_rule rules;
l_result boolean;
l_values jsonb;
l_type text;
begin
select *
into l_rule
from rules
where id = p_rule_id
and type = pg_typeof(p_row)::text;
if not found then
return false;
end if;
l_values := to_jsonb(p_row);
l_type := jsonb_typeof(l_values -> l_rule.field);
if l_type = 'number' then
l_expression := format('select %s %s %s', l_values ->> l_rule.field, l_rule.operator, l_rule.criteria);
else
l_expression := format('select %L %s %L', l_values ->> l_rule.field, l_rule.operator, l_rule.criteria);
end if;
execute l_expression
into l_result;
return l_result;
end;
$$
language plpgsql
stable;
Unfortunately, the conversion to JSONB in order to dynamically pick column values from the passed row does make you lose the data type information. But I can't really think of a different way to dynamically create an expression where the column names and values are not known beforehand.
If a rule specifies a column that doesn't exist, this would result in an expression like NULL > 42 which will evaluate as false.
The function assumes that there is exactly one rule for the combination of "type" and "id". If you don't care about whether the requested rule matches the passed table, you can remove the condition and type = pg_typeof(p_row)::text
Given this sample data:
create table rules (id int, type text, field text, operator text, criteria text);
insert into rules values
(1, 'people', 'age', '>', '30'),
(2, 'people', 'name', '=', 'Alice');
create table people (id int, name text, age int);
insert into people
values
(1, 'Emma', 34),
(2, 'Larry', 25),
(3, 'Alice', 22),
(4, 'Thomas', 31)
;
You can use it like this:
select p.*
from people p
where evaluate_rule(p, 1)
id | name | age
---+--------+----
1 | Emma | 34
4 | Thomas | 31
Of with the second rule:
select p.*
from people p
where evaluate_rule(p, 2)
id | name | age
---+-------+----
3 | Alice | 22
Online example
Edit
It just occurred to me, that with Postgres 12 or later, this can actually be done without dynamic SQL or a helper function:
with cte_rule as (
select (concat('$.', field, ' ', operator, ' ', criteria))::jsonpath as rule
from rules
where id = 1
)
select p.*
from people p
where to_jsonb(p) @@ (select rule from cte_rule);
The operator needs to comply with the rules for SQL/JSON path language, e.g. you need to use == instead of = and double quotes for strings. So my second example rule would need to be:
insert into rules values
(2, 'people', 'name', '==', '"Alice"');
Online example
If using JSON path is an alternative you might want to think about storing the final expression in a single column rather than column name, operator and value in three different columns.
Storing the expression in a jsonpath column has the benefit, that the syntax will be parsed and validated when you try to store the expression.
create table rules (id int, type text, expression jsonpath);
insert into rules values
(1, 'people', '$.age > 30'),
(2, 'people', '$.name == "Alice"');
Then you can use:
select p.*
from people p
where to_jsonb(p) @@ (select expression from rules where id = 1)
Online example
