Evaluate comparison operator from table column-CodePudding

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.

CodePudding user response：

You can do this only using case statement, or using case in the function code

CREATE OR REPLACE FUNCTION check_values(pvalue1 integer, pvalue2 integer, pop text)
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_count integer;
    v_ret bool; 
begin

    case pop
       when '=' then
          return (pvalue1 = pvalue2);
       when '>' then
          return (pvalue1 > pvalue2);
       when '<' then
          return (pvalue1 < pvalue2);
       when '>=' then
          return (pvalue1 >= pvalue2);
       when '<=' then
          return (pvalue1 <= pvalue2);
       else
          return false;
       end case;
          
END;
$function$
;

Then you can use this function on your query:

select * 
    from examples.math 
where 
    examples.check_values(value1, value2, "operator");

I think that this is only possible using the case operator