Обсуждение: 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','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)

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   peter_e@gmx.net

On Sat, Sep 06, 2003 at 14:55:12 +0100,
  Marek Lewczuk <newsy@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.

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

>

--- Peter Eisentraut <peter_e@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

Peter Eisentraut <peter_e@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

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

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.


--elein@varlena.com

On Sat, Sep 06, 2003 at 12:47:07PM -0400, Tom Lane wrote:
> Peter Eisentraut <peter_e@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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

"Marek Lewczuk" <newsy@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
immutableas '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
immutableas '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
immutableas '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

Greg Stark <gsstark@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
immutableas '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

> 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;
CREATE FUNCTION
rbt=# select if(true, '33', '44');
ERROR:  could not determine ANYARRAY/ANYELEMENT type because input is
UNKNOWN

Rod Taylor <rbt@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

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

For my immediate purposes the output is a known type.  It is the input
that would be useful if it was passed through as unknown, or effectively
function as a placeholder if a stronger match cannot be found.


Due to inherited poorly typed data I find myself doing quite a bit of
"X_orNULL(anyelement) returns X". This takes quite a bit of interesting
structure to make it work on the current system.

CASE WHEN $1 IS OF (X) then $1
     WHEN $1 IS OF (unknown) AND cancastX($1) THEN $1::X
     ELSE NULL::X
 END


Another useful function would be an extension of IS OF with output
somewhat like format_type. Returning a string of the datatype based upon
the value passed to it.

getType(anyelement) RETURNS text


Order of type match for unknown:
- Exact match first -- function(unknown) returns <type>
- Cast match second
- Anyelement match with defined return type should be supplied as
UNKNOWN (per function examples above)

If wanted, Anyelement match with anyelement return type could be
converted to text. Perhaps this is best described as a fallback cast
(when anyelement is unknown, autocast to X)

CREATE FUNCTION x(anyelement) RETURNS anyelement
....
LANGUAGE SQL RETURNS TYPE text ON UNKNOWN OUTPUT;

Without this clause an error would be thrown as unknown is not a valid
output.