Re: Needed function IF(expr, expr, expr)

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Marek Lewczuk writes:
>> Currently I have big problem with function IF(), below the description
>> of this function from MySQL manual.

> You cannot implement this kind of function, unless you want to create one
> version for each data type combination.

As of 7.4, one can avoid the data type problem with a polymorphic
function:

regression=# create function if (bool,anyelement,anyelement) returns anyelement
regression-# as 'select case when $1 then $2 else $3 end' language sql;
CREATE FUNCTION

However, there are some limitations:

regression=# select if(true, 33, 44);
if
----
33
(1 row)

regression=# select if(true, 33, 44.4);
ERROR: function if(boolean, integer, numeric) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.

regression=# select if(true, 'a','b');
ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN

You can hack around these problems by adding explicit casts:

regression=# select if(true, 'a'::text,'b');
if
----
a
(1 row)

but I wonder whether we shouldn't allow all-UNKNOWN inputs to be
resolved as TEXT in this situation, as we do when working directly with
CASE.

BTW, I started out this email intending to point out that a function
cannot replace CASE in general because the function will insist on
evaluating all its arguments, which is a behavior you do not want for
CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe
their IF() does evaluate the "unused" argument. Anyone know?)

However, as of 7.4, that problem is gone too. If you write the function
just as above (language sql, volatile, not strict) then the planner will
inline it and indeed what you get is a CASE. Watch this:

regression=# explain select * from tenk1 where if(ten<hundred,unique1,unique2) = 44;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..508.00 rows=50 width=244)
Filter: (CASE WHEN (ten < hundred) THEN unique1 ELSE unique2 END = 44)
(2 rows)

So we do actually have a sort-of-credible way to make a user-defined
function that emulates IF(). I think we might be able to do Oracle's
DECODE() as well, though I don't know its exact definition. (You'd
still need to make several of 'em to handle differing numbers of
arguments, but that seems well within the bounds of feasibility.)

Any comments on the UNKNOWN issue? It's not too late to change that for
7.4, if we have consensus that we should.

regards, tom lane