PostgreSQL MySQL 兼容性之 - 控制流函数

CASE

MySQL

CASE OPERATOR
  CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
  CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one                                                        |
+------------------------------------------------------------+

SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+


SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
|                                                NULL |
+-----------------------------------------------------+

PostgreSQL

  用法一样

IFNULL

MySQL

  IFNULL(expr1,expr2)
  If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

SELECT IFNULL(1,0); 
+-------------+
| IFNULL(1,0) |
+-------------+
|           1 |
+-------------+

SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
|              10 |
+-----------------+

SELECT IFNULL(1/0,10);
+----------------+
| IFNULL(1/0,10) |
+----------------+
|        10.0000 |
+----------------+

SELECT IFNULL(1/0,'yes');
+-------------------+
| IFNULL(1/0,'yes') |
+-------------------+
| yes               |
+-------------------+

PostgreSQL

postgres=# create or replace function ifnull(numeric,numeric) returns numeric as 
$$

  select case when $1 is not null then $1 else $2 end;

$$
 language sql;
CREATE FUNCTION

postgres=# select ifnull(null,2);
 ifnull 
--------
      2
(1 row)

postgres=# select ifnull(1,3);
 ifnull 
--------
      1
(1 row)

或者使用coalesce
postgres=# select coalesce(null,2);
 coalesce 
----------
        2
(1 row)

postgres=# select coalesce(1,3);
 coalesce 
----------
        1
(1 row)

nullif

MySQL

  NULLIF(expr1,expr2)
  Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+

SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+

PostgreSQL

postgres=# select nullif(1,1);
 nullif 
--------

(1 row)

postgres=# select nullif(1,2);
 nullif 
--------
      1
(1 row)

IF

MySQL

  IF(expr1,expr2,expr3)
    If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. 
    IF() returns a numeric or string value, depending on the context in which it is used.

SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
|           3 |
+-------------+

SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes                |
+--------------------+

SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no                                    |
+---------------------------------------+

PostgreSQL

create or replace function if(boolean, text, text) returns text as 
$$

  select case when $1 and $1 is not NULL then $2 else $3 end;         

$$
 language sql ;

create or replace function if(boolean, numeric, numeric) returns numeric as 
$$

  select case when $1 and $1 is not NULL then $2 else $3 end;         

$$
 language sql ;

create or replace function strcmp(text,text) returns boolean as 
$$

  select $1<>$2;

$$
 language sql;

postgres=# SELECT IF(1>2,2,3);
 if 
----
  3
(1 row)

postgres=# SELECT IF(1<2,'yes','no');
 if  
-----
 yes
(1 row)

postgres=# SELECT IF(STRCMP('test','test1'),'no','yes');
 if 
----
 no
(1 row)