IF function in PostgreSQL, as in MySQL

Question

I am trying to replicate an IF function from MySQL to PostgreSQL.

The syntax for the IF function is IF(condition, return_if_true, return_if_false)

I created the 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 great with most things like if(2>1, 2, 1) , but causes an error:

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

fatal division of error by zero.

In my program, I cannot check the denominator because the condition is provided by the user.

Is there any way? Maybe if we can replace the first parameter from boolean with something else, since in this case the function will work, since it will raise and return an exception.

Answer 1

PostgreSQL is compliant

This behavior looks as specified by the SQL standard . This is the first time that I have seen a case where this is a real problem; you usually use only a CASE expression or a PL / PgSQL BEGIN ... EXCEPTION to process it.

The default behavior of MySQL is dangerous and wrong. It only works in such a way as to support old code that relies on this behavior. fixed in newer versions when strict mode (which it should always be), but, unfortunately, has not yet been installed by default. When using MySQL, always include STRICT_TRANS_TABLES or STRICT_ALL_TABLES .

ANSI standard zero division is sometimes a pain, but it also protects against errors that result in data loss.

SQL injection warning, consider recycling

If you are executing expressions from the user, you most likely have SQL injection problems. Depending on your security requirements, you can live with this, but it is very bad if you do not fully trust all your users. Remember that your users may be deceived by the introduction of malicious code from another place .

Consider re-engineering to expose the expression creator for the user and use the query builder to create SQL from user expressions. It will be much more difficult, but safe.

If you cannot do this, see if you can parse the expressions that the user introduces into abstract syntax, check it before execution, and then create new SQL expressions based on the expressed expression. That way, you can at least limit what they can write so that they don't slip any nasty things into the expression. You can also rewrite the expression to add things like zero division checks. Finding (or writing) parsers for algebraic expressions is unlikely to be difficult, but it will depend on what kind of expressions you need for users to write.

At a minimum, the application should use a role ("user") that has only SELECT privileges in the tables, is not superuser, and does not own these tables. This will minimize the harm of any SQL injection.

CASE will not solve this problem as written

In any case, since you are not currently validating and cannot validate the expression from the user, you cannot use the CASE SQL standard to solve this problem. For if( a/b > 0, a, b) you 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 up the expression.

Ugly workaround # 1

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

I wanted to return 'NaN' as a logical result. Unfortunately, "NaN" is greater than numbers no less, and you want to get a smaller or false result.

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

This means that instead we should use a fuzzy NULL return hack:

 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 ); 

using:

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

Your application can overwrite '/' in incoming expressions with @/@ or any other operator name that you choose quite easily.

There is one rather critical problem with this approach and that @/@ will have different precedence to / , so expressions without explicit brackets may not be evaluated as you expect. You may be able to get around this by creating a new schema, specifying in this schema an operator named / , which performs the zero-error trick, and then adds this schema to your search_path before executing custom expressions. This is probably a bad idea.

Ugly Workaround # 2

Since you cannot check the denominator, all I can think of is to wrap it all in DO block (Pg 9.0+) or PL / PgSQL and catch any exceptions to the evaluation of the expression.

Erwin's answer gives a better example of this than me, so I deleted it. In any case, this is a terrible and dangerous thing, but do not do it. Your application must be fixed.

Answer 2

With a boolean argument, dividing by zero will always throw an exception (which is good) before your function is even called. There is nothing . This has 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 highly recommend using a function called if . if is a keyword in PL / pgSQL. If you use custom functions written in PL / pgSQL, this will be very confusing.

Just use the standard SQL CASE expression directly.

---

An alternative would be to accept the argument text and evaluate it using dynamic SQL .

Proof of concept

What you are asking for will work as follows:

 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 risk in the hands of unreliable users. Read @Craig's answer on how to make this more secure. However, I do not see how this can be made bulletproof and will never use it.