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) 


 

Sat, 6 Sep 2003 14:55:12 +0100
"Marek Lewczuk" <newsy@lewczuk.com>
Needed function IF(expr, expr, expr)
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


Sat, 6 Sep 2003 16:05:28 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Needed function IF(expr, expr, expr)
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.

Sat, 6 Sep 2003 10:43:38 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Needed function IF(expr, expr, expr)
--- 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

Sat, 6 Sep 2003 09:32:24 -0700 (PDT)
Jeff Eckermann <jeff_eckermann@yahoo.com>
Re: Needed function IF(expr, expr, expr)
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

Sat, 06 Sep 2003 12:47:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Needed function IF(expr, expr, expr) 
> 
> 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. 

> 


 

Sat, 6 Sep 2003 18:09:08 +0100
"Marek Lewczuk" <newsy@lewczuk.com>
Re: Needed function IF(expr, expr, expr)
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
>
06 Sep 2003 14:19:37 -0300
Franco Bruno Borghesi <franco@akyasociados.com.ar>
Re: Needed function IF(expr, expr, expr)


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

Sat, 6 Sep 2003 11:22:08 -0700
elein <elein@varlena.com>
Re: Needed function IF(expr, expr, expr)
"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 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


06 Sep 2003 15:18:03 -0400
Greg Stark <gsstark@mit.edu>
Re: Needed function IF(expr, expr, expr)
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 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

Sat, 06 Sep 2003 21:13:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Needed function IF(expr, expr, expr) 

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

Hm. I wonder if there are cases of people using functions like this with
user-defined volatile functions depending on the function's side effects
happening the correct number of times. Or do volatile functions not get
inlined like this?

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

I think there's a problem implementing decode() surrounding NULL:

SELECT decode(col, 'foo', 1, NULL, 2, 3)

would mean:

SELECT CASE WHEN col='foo' THEN 1
            WHEN col IS NULL THEN 2
            ELSE 3
       END

To do it I think you would need a iseq() function that compared NULLs as being
equal.

-- 
greg


07 Sep 2003 19:00:03 -0400
Greg Stark <gsstark@mit.edu>
Re: [GENERAL] Needed function IF(expr, expr, expr)
> 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
Sun, 07 Sep 2003 19:25:30 -0400
Rod Taylor <rbt@rbt.ca>
Re: Needed function IF(expr, expr, expr)
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

Sun, 07 Sep 2003 20:38:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Needed function IF(expr, expr, expr) 
Greg Stark <gsstark@mit.edu> writes:
> Hm. I wonder if there are cases of people using functions like this with
> user-defined volatile functions depending on the function's side effects
> happening the correct number of times. Or do volatile functions not get
> inlined like this?

SQL functions can't have side effects, at least not if they are simple
SELECTs, which is the only kind that gets inlined.

> To do it I think you would need a iseq() function that compared NULLs as being
> equal.

No, just

	CASE WHEN (col = checkval) OR (col IS NULL AND checkval IS NULL)
	...

			regards, tom lane

Sun, 07 Sep 2003 21:14:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [GENERAL] Needed function IF(expr, expr, expr) 
> 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.
Sun, 07 Sep 2003 21:29:01 -0400
Rod Taylor <rbt@rbt.ca>
Re: Needed function IF(expr, expr, expr)