Needed function IF(expr, expr, expr)

From: "Marek Lewczuk" <newsy(at)lewczuk(dot)com>
To: "Lista dyskusyjna pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Needed function IF(expr, expr, expr)
Date: 2003-09-06 13:55:12
Message-ID: 000001c3747e$7ed119c0$02694cd5@marek
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2003-09-06 14:05:28 Re: Needed function IF(expr, expr, expr)
Previous Message Claudio Lapidus 2003-09-06 12:18:10 Re: aggregate function

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2003-09-06 14:05:28 Re: Needed function IF(expr, expr, expr)
Previous Message Mendola Gaetano 2003-09-06 13:46:33 Re: Notices for redundant operations