PostgreSQL Server Programming - Second Edition

Conditional expressions

Conditional expressions allow developers to control the action of the function, based on a defined criteria. PostgreSQL provides the CASE and IF statements to execute different commands based on conditions. The following is an example of the usage of a CASE statement to control how a string is treated based on its value. If the value is null or contains a zero-length string, it is treated the same as null:

CREATE OR REPLACE FUNCTION format_us_full_name( prefix text, firstname text, mi text, lastname text, suffix text) RETURNS text AS $$ DECLARE fname_mi text; fmi_lname text; prefix_fmil text; pfmil_suffix text; BEGIN fname_mi := CONCAT_WS(' ', CASE trim(firstname) WHEN '' THEN NULL ELSE firstname END, CASE trim(mi) WHEN ...