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