IF function in PostgreSQL as in MySQL

Question

I am trying to replicate the IF function from MySQL into PostgreSQL.

The syntax of IF function is IF(condition, return_if_true, return_if_false)

I created following formula:

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
   RETURNS anyelement AS $$
BEGIN
    CASE WHEN ($1) THEN
    RETURN ($2);
    ELSE
    RETURN ($3);
    END CASE;
    EXCEPTION WHEN division_by_zero THEN
    RETURN ($3);
END;
$$ LANGUAGE plpgsql;

It works well with most of the things like if(2>1, 2, 1) but it raises an error for:

if( 5/0 > 0, 5, 0)

fatal error division by zero.

In my program I can't check the denominator as the condition is provided by user.

Is there any way around? Maybe if we can replace first parameter from boolean to something else, as in that case the function will work as it will raise and return the exception.

Answer 1

PostgreSQL is following the standard

This behaviour appears to be specified by the SQL standard. This is the first time I've seen a case where it's a real problem, though; you usually just use a CASE expression or a PL/PgSQL BEGIN ... EXCEPTION block to handle it.

MySQL's default behaviour is dangerous and wrong. It only works that way to support older code that relies on this behaviour. It has been fixed in newer versions when strict mode is active (which it absolutely always should be) but unfortunately has not yet been made the default. When using MySQL, always enable STRICT_TRANS_TABLES or STRICT_ALL_TABLES.

ANSI-standard zero division is a pain sometimes, but it'll also protect against mistakes causing data loss.

SQL injection warning, consider re-design

If you're executing expressions from the user then you quite likely have SQL injection problems. Depending on your security requirements you might be able to live with that, but it's pretty bad if you don't totally trust all your users. Remember, your users could be tricked into entering the malicious code from elsewhere.

Consider re-designing to expose an expression builder to the user and use a query builder to create the SQL from the user expressions. This would be much more complicated, but secure.

If you can't do that, see if you can parse the expressions the user enters into an abstract syntax, validate it before execution, and then produce new SQL expressions based on the parsed expression. That way you can at least limit what they can write, so they don't slip any nasties into the expression. You can also rewrite the expression to add things like checks for zero division. Finding (or writing) parsers for algebraic expressions isn't likely to be hard, but it'll depend on what kinds of expressions you need to let users write.

At minimum, the app needs to be using a role ("user") that has only SELECT privileges on the tables, is not a superuser, and does not own the tables. That'll minimise the harm any SQL injection will cause.

CASE won't solve this problem as written

In any case, because you currently don't validate and can't inspect the expression from the user, you can't use the SQL-standard CASE statement to solve this. For if( a/b > 0, a, b) you'd usually write something like:

CASE
    WHEN b = 0 THEN b
    ELSE CASE 
        WHEN a/b=0 THEN a
        ELSE b
    END
END

This explicitly handles the zero denominator case, but is only possible when you can break the expression up.

Ugly workaround #1

An alternative solution would be to get Pg to return a placeholder instead of raising an exception for division by zero by defining a replacement division operator or function. This will only solve the divide-by-zero case, not others.

I wanted to return 'NaN' as that's the logical result. Unfortunately, 'NaN' is greater than numbers not less then, and you want a less-than or false-like result.

regress=# SELECT NUMERIC 'NaN' > 0;
 ?column? 
----------
 t
(1 row)

This means we have to use the icky hack of returning NULL instead:

CREATE OR REPLACE FUNCTION div_null_on_zero(numeric,numeric) returns numeric AS $$
VALUES (CASE WHEN $2 = 0 THEN NULL ELSE $1/$2 END)
$$ LANGUAGE 'SQL' IMMUTABLE;

CREATE OPERATOR @/@ (
    PROCEDURE = div_null_on_zero(numeric,numeric),
    LEFTARG = numeric,
    RIGHTARG = numeric
);

with usage:

regress=# SELECT 5 @/@ 0, 5 @/@ 0>0, CASE WHEN 5 @/@ 0 > 0 THEN 5 ELSE 0 END;
 ?column? | ?column? | case 
----------+----------+------
          |          |    0
(1 row)

Your app can rewrite '/' in incoming expressions into @/@ or whatever operator name you choose pretty easily.

There's one pretty critical problem with this approach, and that's that @/@ will have different precedence to / so expressions without explicit parentheses may not be evaluated as you expect. You might be able to get around this by creating a new schema, defining an operator named / in that schema that does your null-on-error trick, and then adding that schema to your search_path before executing user expressions. It's probably a bad idea, though.

Ugly workaround #2

Since you can't inspect the denominator, all I can think of is to wrap the whole thing in a DO block (Pg 9.0+) or PL/PgSQL function and catch any exceptions from the evaluation of the expression.

Erwin's answer provides a better example of this than I did, so I've removed this. In any case, this is an awful and dangerous thing to do, do not do it. Your app needs to be fixed.

Answer 2

With a boolean argument, a division by zero will always throw an exception (and that's a good thing), before your function is even called. There is nothing you can do about it. It's already happened.

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)
 RETURNS anyelement LANGUAGE SQL AS
$func$
SELECT CASE WHEN $1 THEN $2 ELSE $3 END
$func$;

I would strongly advise against a function named if to begin with. IF is a keyword in PL/pgSQL. If you use user defined functions written in PL/pgSQL this will be very confusing.

Just use the standard SQL expression CASE directly.

---

The alternative would be to take a text argument and evaluate it with dynamic SQL.

Proof of concept

What you ask for would work like this:

CREATE OR REPLACE FUNCTION f_if(_expr text
                              , _true anyelement
                              , _else anyelement
                              , OUT result anyelement)
  RETURNS anyelement LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE '
   SELECT CASE WHEN (' || _expr || ') THEN $1 ELSE $2 END' -- !! dangerous !!
   USING _true, _else
   INTO result;

   EXCEPTION WHEN division_by_zero THEN
   result := _else;
   -- possibly catch more types of exceptions ...
END
$func$;

Test:

SELECT f_if('TRUE'   , 1, 2)  --> 1
      ,f_if('FALSE'  , 1, 2)  --> 2
      ,f_if('NULL'   , 1, 2)  --> 2
      ,f_if('1/0 > 0', 1, 2); --> 2

This is a big security hazard in the hands of untrusted users. Read @Craig's answer about making this more secure. However, I fail to see how it can be made bulletproof and would never use it.