Re: [GENERAL] Needed function IF(expr, expr, expr) - Mailing list pgsql-hackers

If the function is defined with ANY*
and you defer typing the arguments until the first reference
then I think you will get what you want with the CASE statement.

If the function is called if( x>y, x+1, y), the first reference
is in the argument list and so should be typed there.  But if
you pass constants or non-expressions, then delaying the
typing would enable a better fit when using ANY*.

Letting the first usage define the type would leverage
the existing expression handling work as is without
special cases.  It would also avoid implicit casts
of unexpected argument types.  If the operation in
the function is dependent on the arguments being, say
some kind of numeric or a class of types we ought
to raise an error if they are incompatible.

Of course this would ONLY be for ANY arguments.
A function declared with a non-generic type needs
to have arguments of the declared types.


--elein@varlena.com

On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly