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
