postgresql - How to write Postgres Dynamic SQL statement to evaluate expressions stored in rows?

Question

I am using PostgreSQL, and I need to write SQL statement to solve the following problem with these 2 tables:

                                  Table "Transaction"
    Column      |              Type              | Collation | Nullable |      Default
----------------+--------------------------------+-----------+----------+-------------------
 id             | text                           |           | not null |
 amount         | integer                        |           | not null |
 merchant_name  | text                           |           | not null |

                                 Table "Rule"
     Column     |              Type              | Collation | Nullable |      Default
----------------+--------------------------------+-----------+----------+-------------------
 id             | text                           |           | not null |
 key            | text                           |           | not null |
 sql_expression | text                           |           | not null |

For example, there are 2 Rule rows:

  id  |       key       |       sql_expression
------+-----------------+-----------------------
   1  | amount          | > 100
   2  | merchant_name   | ~* '.*amazon.*'

Given a Transaction id (let's call the matched Transaction row "this_transaction"), I want to find all Rule rows where the condition constructed from "this_transaction" with each Rule row's key and sql_expression values evaluates to true. In this example, I want Rule 1 if "this_transaction".amount > 100, and Rule 2 if "this_transaction".merchant_name ~* '.*amazon.*'

I don't know how to write this WHERE expression. A non-working SQL pseudocode is provided below.

SELECT "Rule".* FROM "Rule",
  (
    SELECT "Transaction".*
    FROM "Transaction"
    WHERE "Transaction".id = $1
  ) AS "this_transaction"
WHERE "this_transaction".("Rule".key) "Rule".sql_expression;

$1 = 'some_transaction_id'

If anyone has any suggestion on how to write the WHERE expression or solve the problem in another way, please let me know. Thank you for your time and consideration.

P.S. In our situation, we will have many Rule rows that are constantly created, updated, and deleted by many users. Please suggest a solution that would support this flexible system. Thank you.

Answer 1

create or replace function eval_bool(data record, key text, expr text)
returns bool stable strict language plpgsql
as $$
declare
    result bool;
begin
    execute 'select $1.' || key || ' ' || expr into result using data;
    return result;
end $$;

-- test:

select relname
from pg_class as t
where eval_bool(t, 'relname', 'like ''%class%''');

┌───────────────────────────────────┐
│              relname              │
├───────────────────────────────────┤
│ pg_class_oid_index                │
│ pg_class_relname_nsp_index        │
│ pg_class_tblspc_relfilenode_index │
│ pg_opclass_am_name_nsp_index      │
│ pg_opclass_oid_index              │
│ pg_class                          │
│ pg_opclass                        │
└───────────────────────────────────┘

And for now it should be simple:

SELECT "Rule".* FROM "Rule",
  (
    SELECT "Transaction".*
    FROM "Transaction"
    WHERE "Transaction".id = $1
  ) AS "this_transaction"
WHERE eval_bool("this_transaction"::"Transaction", "Rule".key, "Rule".sql_expression);

Note that the first value (data) should to be the value of a registered type. So wee need to cast the subquery result to the "Transaction" type.