Conditional Expression Functions

Precautions

This section describes only the new conditional expression functions of Dolphin. For details about the conditional expression functions of the original openGauss, see Conditional Expression Functions.

Conditional Expression Functions

if(bool, expr1, expr2)
Description: Condition judgment function. If bool is true, expr1 is returned. If bool is false, expr2 is returned.
Example:
```
openGauss=# select if(true, 1, 2);
 case
------
    1
(1 row)
```
```
openGauss=# select if(false, 1, 2);
 case
------
    2
(1 row)
```
ifnull( expr1 , expr2 )
Description:
- If the value of expr1 is NULL, the value of expr2 is returned.
- If the value of expr1 is not NULL, the value of expr1 is returned.
Example:
```
openGauss=# SELECT ifnull('hello','world');
  nvl  
-------
 hello
(1 row)
```
Remarks: The parameter conversion logic is the same as that of the NVL.
isnull( expr )
- Returns true if expr is NULL.
- Returns false if expr is not NULL.
Example:
```
openGauss=# SELECT ifnull('hello');
?column?  
--------
      f 
(1 row)
```
Remarks: The null check logic is the same as that of expr is null.
interval(base_expr, expr1, expr2, …, exprn)
Description:
- Compares base_expr with expr(n) one by one until expr(n) is greater than base_expr and returns value(n-1). If expr(n) is less than or equal to base_expr, returns value(n).
- If base_expr or expr(n) is non-numeric data:
  - BOOL: TRUE is converted to 1, and FALSE is converted to 0.
  - If it can be truncated to a floating point number in float8 format, it is truncated to float8.
  - If it cannot be truncated to a floating point number float8, it is considered as 0.
Example:
```
openGauss=# SELECT interval(5,2,3,4,6,7);
 interval
----------
        3
(1 row)
```
```
openGauss=# SELECT interval(false,-1,0,true,2);
 interval
----------
        2
(1 row)
```
```
openGauss=# SELECT interval('2022-12-12'::timestamp,'asdf','2020-12-12'::date,2023);
 interval
----------
        2
(1 row)
```

strcmp(str1, str2)

Description: Compares str1 with str2 from left to right. If str1 is equal to str2, 0 is returned. If str1 is greater than str2, 1 is returned. If str1 is less than str2, -1 is returned.

Example:

openGauss=# SELECT strcmp('asd','asd');
 strcmp 
--------
      0
(1 row)

openGauss=# SELECT strcmp(312,311);
 strcmp 
--------
      1
(1 row)

openGauss=# SELECT strcmp('2021-12-12'::timestamp,20210::float8);
 strcmp 
--------
     -1
(1 row)