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

I've had this function for a long time in my db... try it, I think it
does what you need. And be carefull, it assumes that false or null are
the same thing.

CREATE OR REPLACE FUNCTION IF (BOOLEAN, TEXT, TEXT) RETURNS TEXT AS '
DECLARE
condition ALIAS FOR $1;
iftrue ALIAS FOR $2;
iffalse ALIAS FOR $3;
result TEXT;
BEGIN
IF ($1) THEN
result=iftrue;
ELSE
result:=iffalse;
END IF;

RETURN result;
END;' LANGUAGE 'plpgsql';

On Sat, 2003-09-06 at 10:55, Marek Lewczuk wrote:

> 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.
>
>
> -------------------
> IF(expr1,expr2,expr3)
> If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2,
> else it returns expr3. IF() returns a numeric or string value, depending
> on the context in which it is used:
> mysql> SELECT IF(1>2,2,3);
> -> 3
> mysql> SELECT IF(1<2,'yes','no');
> -> 'yes'
> mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
> -> 'no'
>
> If expr2 or expr3 is explicitely NULL then the result type of the IF()
> function is the type of the not NULL column. (This behaviour is new in
> MySQL 4.0.3). expr1 is evaluated as an integer value, which means that
> if you are testing floating-point or string values, you should do so
> using a comparison operation:
> mysql> SELECT IF(0.1,1,0);
> -> 0
> mysql> SELECT IF(0.1<>0,1,0);
> -> 1
>
> In the first case above, IF(0.1) returns 0 because 0.1 is converted to
> an integer value, resulting in a test of IF(0). This may not be what you
> expect. In the second case, the comparison tests the original
> floating-point value to see whether it is non-zero. The result of the
> comparison is used as an integer. The default return type of IF() (which
> may matter when it is stored into a temporary table) is calculated in
> MySQL Version 3.23 as follows: Expression Return value
> expr2 or expr3 returns string string
> expr2 or expr3 returns a floating-point value floating-point
> expr2 or expr3 returns an integer integer
>
> If expr2 and expr3 are strings, then the result is case-insensitive if
> both strings are case-insensitive. (Starting from 3.23.51)
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>