4

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.

1 Answer 1

3

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.