SQL Functions

I l @ ve RuBoard

PostgreSQL includes several functions that return values based on expressions supplied in the current SQL statement. Moreover, these functions are not constrained to acting on specific data types; rather, they act as control structures within a SQL statement.

CASE WHEN

Description

The CASE WHEN function is a simple conditional evaluation tool. Most programming languages contain similar constructs. It can be thought of as analogous to the ubiquitous IF THEN ELSE statement.

Inputs

 CASE WHEN condition THEN result        [ WHEN condition THEN result ]               [ ELSE result ]  END 

Example

This example shows a classic IF THEN ELSE paradigm in which the CASE WHEN function can be used. The age of an employee is compared against certain constants, and the possible outputs of minor, adult, or unknown are returned depending on their age.

 SELECT name, age,  CASE WHEN age<18 THEN 'minor'        WHEN age>=18 THEN 'adult'        ELSE 'unknown'  END  FROM employees;  name        age   case  ------------------- Bill        13    minor  Timmy       7     minor  Pam         25    adult  Barry       NULL  unknown 

COALESCE

Description

The COALESCE function accepts an arbitrary number of input arguments and returns the first one that is evaluated as NOT NULL . The COALESCE function is very useful for providing display defaults for arbitrary data sources.

Input

 COALESCE(  arg1, , argN  ) 

Example

Return a default message to the user :

 SELECT COALESCE(book.title, book.description, 'Not Available'); 

NULLIF

Description

The NULLIF function accepts two arguments. It returns a NULL value only if the value of both arguments is equal. Otherwise, it returns the value of the first argument.

Input

 NULLIF(  arg1, arg2  ) 

Example

In this case, the first value will be returned because the values are not equal:

 SELECT NULLIF('hello', 'world');  ---------------- 'hello' 

However, when the values are equal, a NULL value is returned:

 SELECT NULLIF('hello', SUBSTR('helloword',1,5));  NULL 

Notes

The NULLIF function behaves in an inverse-like manner of the COALESCE function. It is useful for exception testing, in which a variable is being tested against a known value. If the variable equals the known value, nothing is returned. However, if the values do not match, the value of the evaluated variable is returned instead.

I l @ ve RuBoard