Evaluate comparison operator from table column

Question

Is it possible to expand a field value into a comparison operator? Something like this:

create table math (
    value1 int,
    value2 int,
    operator text
);
insert into math values(1,2,'>=');
select * from math where value1 operator value2;

PS: I know that it is possible to solve this use case by means of case when, but want to know if there is an alternative solution.

Answer 1

Yes, possible. You need dynamic SQL to evaluate the expression.

Unsafe

A simple, naive approach. A bit simpler and faster than the safe approach.

CREATE OR REPLACE FUNCTION eval_unsafe(_operand1 int, _operand2 int, _operator text, OUT _result bool)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT $1 %s $2', _operator)
   USING _operand1, _operand2
   INTO  _result;
END
$func$;

This is open to SQL injection. If your table source is safe, it's good enough. Is a table source ever safe, though?

Safe

I suggest this safe approach instead:

CREATE OR REPLACE FUNCTION eval_safe(_operand1 int, _operand2 int, _operator text, OUT _result bool)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT $1 OPERATOR (%s) $2', (_operator || '(integer, integer)')::regoperator::regoper)
   USING _operand1, _operand2
   INTO  _result;
END
$func$;

Call for your example:

SELECT *
FROM   math m
WHERE  eval_safe(m.value1, m.value2, m.operator);

db<>fiddle here

The cast to the object identifier type pg_operator enforces a valid operator (effectively checks against valid entries in the system catalog pg_operator). The next cast to pg_oper is just a convenient way to get valid format for the OPERATOR construct.

See:

• SQL injection in Postgres functions vs prepared queries