473,835 Members | 1,918 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Needed function IF(expr, expr, expr)

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','n o');
-> '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

Nov 11 '05 #1
9 13922
Marek Lewczuk writes:
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.


You cannot implement this kind of function, unless you want to create one
version for each data type combination. But consider replacing the IF()
function with the standard CASE expression:

CASE WHEN expr1 THEN expr_if_true ELSE expr_if_false END

Also note that in PostgreSQL there is a boolean type, and 0 and 1 are not
valid boolean values.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2
On Sat, Sep 06, 2003 at 14:55:12 +0100,
Marek Lewczuk <ne***@lewczuk. com> 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.


You should be able to do what you want using CASE instead of IF.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #3
>
You should be able to do what you want using CASE instead of IF.
Yes I know that, but as I said before I'm moving from MySQL and my
application is using many querys with this function and replacing all
querys will be very hard. So I belive that the best solution is to write
this function -> I know that it is difficult, but it will help all of
people who wants to user PG.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4
--- Peter Eisentraut <pe*****@gmx.ne t> wrote:
Marek Lewczuk writes:
Also note that in PostgreSQL there is a boolean
type, and 0 and 1 are not
valid boolean values.


Sort of:

jeff=# select 1 = true;
ERROR: Unable to identify an operator '=' for types
'integer' and 'boolean'
You will have to retype this query using an
explicit cast
jeff=# select '1' = true;
?column?
----------
t
(1 row)

jeff=# select version();
version

---------------------------------------------------------------------
PostgreSQL 7.3.1 on i386-portbld-freebsd4.7, compiled
by GCC 2.95.4
(1 row)
_______________ _______________ ____
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #5
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','n o');
-> '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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Whcp21dVnhLsBV0 RAiGcAJ9R/cDkbsnf31lORk2T MFqvHttS/QCfXPm5
tlOXhTXBSIxa/7+K/uzy+fk=
=6n6O
-----END PGP SIGNATURE-----

Nov 11 '05 #6


If the function is defined with ANY*
and you defer typing the arguments until the first reference
then I think you will get what you want with the CASE statement.

If the function is called if( x>y, x+1, y), the first reference
is in the argument list and so should be typed there. But if
you pass constants or non-expressions, then delaying the
typing would enable a better fit when using ANY*.

Letting the first usage define the type would leverage
the existing expression handling work as is without
special cases. It would also avoid implicit casts
of unexpected argument types. If the operation in
the function is dependent on the arguments being, say
some kind of numeric or a class of types we ought
to raise an error if they are incompatible.

Of course this would ONLY be for ANY arguments.
A function declared with a non-generic type needs
to have arguments of the declared types.
--*****@varlena.c om

On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote:
Peter Eisentraut <pe*****@gmx.ne t> writes:
Marek Lewczuk writes:
Currently I have big problem with function IF(), below the description
of this function from MySQL manual.

You cannot implement this kind of function, unless you want to create one
version for each data type combination.


As of 7.4, one can avoid the data type problem with a polymorphic
function:

regression=# create function if (bool,anyelemen t,anyelement) returns anyelement
regression-# as 'select case when $1 then $2 else $3 end' language sql;
CREATE FUNCTION

However, there are some limitations:

regression=# select if(true, 33, 44);
if
----
33
(1 row)

regression=# select if(true, 33, 44.4);
ERROR: function if(boolean, integer, numeric) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit typecasts.

regression=# select if(true, 'a','b');
ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN

You can hack around these problems by adding explicit casts:

regression=# select if(true, 'a'::text,'b');
if
----
a
(1 row)

but I wonder whether we shouldn't allow all-UNKNOWN inputs to be
resolved as TEXT in this situation, as we do when working directly with
CASE.

BTW, I started out this email intending to point out that a function
cannot replace CASE in general because the function will insist on
evaluating all its arguments, which is a behavior you do not want for
CASE, and I'd imagine not for MySQL's IF() either. (But I dunno, maybe
their IF() does evaluate the "unused" argument. Anyone know?)

However, as of 7.4, that problem is gone too. If you write the function
just as above (language sql, volatile, not strict) then the planner will
inline it and indeed what you get is a CASE. Watch this:

regression=# explain select * from tenk1 where if(ten<hundred, unique1,unique2 ) = 44;
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..508 .00 rows=50 width=244)
Filter: (CASE WHEN (ten < hundred) THEN unique1 ELSE unique2 END = 44)
(2 rows)

So we do actually have a sort-of-credible way to make a user-defined
function that emulates IF(). I think we might be able to do Oracle's
DECODE() as well, though I don't know its exact definition. (You'd
still need to make several of 'em to handle differing numbers of
arguments, but that seems well within the bounds of feasibility.)

Any comments on the UNKNOWN issue? It's not too late to change that for
7.4, if we have consensus that we should.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #7
"Marek Lewczuk" <ne***@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,"an yelement","anye lement") returns "anyelement " language sql strict immutable as '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,"an yelement","anye lement") returns "anyelement " language sql strict immutable as '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,"anye lement","anyele ment") returns "anyelement " language sql strict immutable as '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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #8
Greg Stark <gs*****@mit.ed u> writes:
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.)
Because "any" doesn't imply anything about relationships between
datatypes of arguments and results. You need "anyelement " to inform
the parser that the result type is the same as the second and third
argument types. "any" is just a wildcard.
slo=> create or replace function iff(boolean,"an yelement","anye lement") returns "anyelement " language sql strict immutable as 'select case when $1 then $2 else $3 end' ;


You don't want this to be STRICT: "if(true,33,nul l)" should yield 33 not
null, no?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #9
Rod Taylor <rb*@rbt.ca> writes:
Any comments on the UNKNOWN issue? It's not too late to change that for
7.4, if we have consensus that we should.
I would actually prefer to get UNKNOWN so I can apply my own default
type, but we're not even given the chance to resolve the unknown issue
ourselves. CREATE OR REPLACE FUNCTION if(bool,anyelem ent,anyelement)
RETURNS anyelement
AS 'SELECT
CASE WHEN $2 is of (unknown) THEN
CASE WHEN $1 THEN $2::point ELSE $3::point END
ELSE
CASE WHEN $1 THEN $2 ELSE $3 END
END' language SQL;


There's no chance of that working --- the parser has to be able to
determine the result type of a function invocation without reference
to the function body. (Otherwise CREATE OR REPLACE FUNCTION invalidates
every use of the function.)

I don't feel that the anyelement in -> anyelement out mechanism is the
last word in polymorphism, though. Care to propose additional features
of the same kind? If you can find a way to describe the behavior you
want in terms of the function signature, it'd be worth considering ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 11 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
1928
by: Chris | last post by:
Hi Folks, I would like to forward an expression to an if-statement via a variable. Like: $a=10; $b=20; $expr="$a > $b";#this should be my if-expression if ($expr) do something;#test should be if($a > $b)
6
1519
by: Bengt Richter | last post by:
E.g., so we could write for x in seq if x is not None: print repr(x), "isn't None ;-)" instead of for x in (x for x in seq if x is not None): print repr(x), "isn't None ;-)"
20
1974
by: jvankay | last post by:
Does any C guru out there know of a C pre-processing tool that will allow limited pruning of C source based on a single #define variable. That is, in the code fragment below: XXX #ifdef UndesirableTag YYY #ifdef TagB ZZZ
39
2224
by: jamilur_rahman | last post by:
What is the BIG difference between checking the "if(expression)" in A and B ? I'm used to with style A, "if(0==a)", but my peer reviewer likes style B, how can I defend myself to stay with style A ? style A: .... .... int a = 1; if(0==a) {
32
2366
by: Stephen | last post by:
Is there a standard way to remove the warning that a C compiler might produce from the statement: if (a = b) {} I don't want to do: if ((a = b) != 0) {} Because my "a = b" is actually contained in a macro that might be used
7
8860
by: Mark Hobley | last post by:
I have some information that states that the if conditional can be be inverted from the traditional syntax if (EXPRESSION) BLOCK to an alternative syntax: if BLOCK (EXPRESSION); I have a simple line of code:
2
2077
by: marsarden | last post by:
write code like: int main(void) { int a=10; if(a<20) {} } Compiler ok on dev-cpp . don't we have to add a ";" after if
6
2256
by: vl106 | last post by:
A static code analysis tool gave me a warning on if (ptr && ptr->data) { ... } I assumed the tool doesn't get the "short circuit behaviour" in the if statement. But a collegue said it may be the missing check ot ptr against NULL.
3
1858
by: =?Utf-8?B?SmFuIEhlcHBlbg==?= | last post by:
Hi, I've a question. I'm developing a windows application and for the application i need to run functions and procedures that are stored in a database. Here is an example that i tried to get working. How can i execute the function that is stored in the string expr ?
0
9652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10807
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10517
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10559
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10230
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7765
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5802
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4430
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.