Re: Needed function IF(expr, expr, expr) - Mailing list pgsql-general

"Marek Lewczuk" <newsy@lewczuk.com> writes:

> Hello,
> I'm moving out from MySQL to PostgreSQL and there are some function
> which are not supported in PG so I'm trying to write my own functions.
> Currently I have big problem with function IF(), below the description
> of this function from MySQL manual.
>
> Anybody can help me with this ?? I think that PLPGSQL language can be
> used or maybe other (plPerl) etc.

Well I can't really make heads or tails of the part of the manual discussion
NULL handling.

It's possible to emulate this with straight SQL functions. SQL functions have
the advantage that they can be inlined directly into your query and
potentially optimized to use indexes etc. I don't think 7.3 will do so, but
7.4 definitely will. I doubt case expressions leave much room for
optimizations though.

In 7.3 you would have to define an argument for every data type you want to be
able to handle. From the sounds of the manual just integer and varchar (or
text) ought to be enough. Perhaps you would want a floating point version
and/or a numeric version.

For 7.4 you'll be able to define it something like this:
(I'm not sure why "anyelement" works but "any" doesn't work.)

slo=> create or replace function iff(boolean,"anyelement","anyelement") returns "anyelement" language sql strict
immutableas 'select case when $1 then $2 else $3 end' ; 
CREATE FUNCTION
slo=> select iff(true,1,2);
 iff
-----
   1
(1 row)

slo=> select iff(false,1,2);
 iff
-----
   2
(1 row)


That still doesn't let you use an integer argument for the first argument. To
define it for integers you would have to do something like:

slo=> create or replace function iff(integer,"anyelement","anyelement") returns "anyelement" language sql strict
immutableas 'select case when $1<>0 then $2 else $3 end' ; 
CREATE FUNCTION
slo=> select iff(1,1,2);
 iff
-----
   1
(1 row)

slo=> select iff(0,1,2);
 iff
-----
   2
(1 row)


Postgres will never automatically convert from a float to an integer throwing
away precision like you describe, so you would have to do something like this
to support passing a float as a true/false value:

slo=> create or replace function iff(float,"anyelement","anyelement") returns "anyelement" language sql strict
immutableas 'select case when $1::integer<>0 then $2 else $3 end' ; 
CREATE FUNCTION
slo=> select iff(0.1,1,2);
 iff
-----
   2
(1 row)


I would suggest only defining the forms you actually need. If your code always
uses logical expressions for the first argument and never passes integer or
floating point values then don't bother with the second two.

--
greg