Needed function IF(expr, expr, expr)

[Marek Lewczuk]

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

[Peter Eisentraut]

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

[Bruno Wolff III]

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

[Marek Lewczuk]

>

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

[Jeff Eckermann]

--- Peter Eisentraut <pe*****@gmx.net> 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

[Franco Bruno Borghesi]

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

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

iD8DBQA/Whcp21dVnhLsBV0RAiGcAJ9R/cDkbsnf31lORk2TMFqvHttS/QCfXPm5
tlOXhTXBSIxa/7+K/uzy+fk=
=6n6O
-----END PGP SIGNATURE-----

[elein]

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.com

On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote:

Peter Eisentraut <pe*****@gmx.net> 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,anyelement,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*******@postgresql.org so that your
message can get through to the mailing list cleanly

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

[Greg Stark]

"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,"anyelement","anyelement") 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,"anyelement","anyelement") 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,"anyelement","anyelement") 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

[Tom Lane]

Greg Stark <gs*****@mit.edu> 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,"anyelement","anyelement") 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,null)" should yield 33 not
null, no?

regards, tom lane

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

[Tom Lane]

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,anyelement,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*******@postgresql.org