Example 1: Simple SQL UDF Definition Using a CASE Expression in its RETURN Statement
The following CREATE FUNCTION request performs an addition operation if the first argument submitted to calc is the literal ‘A’, a subtraction operation if the first argument is ‘S’, a multiplication operation if the first argument is ‘M’, and a division operation if the first argument is anything else.
CREATE FUNCTION calc (func CHARACTER(1), A INTEGER, B INTEGER) RETURNS INTEGER LANGUAGE SQL DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN CASE WHEN func = 'A' THEN A + B WHEN func = 'S' THEN A - B WHEN func = 'M' THEN A * B ELSE A / B END;
The following SELECT request returns a result value of 5.
SELECT calc('A', 2, 3);
The following SELECT request returns a result value of 6.
SELECT calc('M', 2, 3);
Example 2: Defining an SQL UDF With a CASE Expression in its RETURN Statement
Similar to “Example 1: Simple SQL UDF Definition Using a CASE Expression in its RETURN Statement," this CREATE FUNCTION request specifies a CASE expression in its RETURN statement.
CREATE FUNCTION test.case_expr (a INTEGER, b INTEGER) RETURNS CHARACTER(50) LANGUAGE SQL DETERMINISTIC CONTAINS SQL SPECIFIC test.case_expr CALLED ON NULL INPUT SQL SECURITY DEFINER COLLATION INVOKER INLINE TYPE 1 RETURN CASE WHEN a > 1 THEN 'a is greater than 1' WHEN b > 1 THEN 'b is greater than 1' ELSE 'a and b are not greater than 1' END;