Database Reference
In-Depth Information
Searched CASE
The searched
CASE
statement executes a condition based on the result of the
boolean-expression
. This is quite similar to
IF-THEN-ELSIF
. The evaluation of
the expression continues until it inds a match and then subsequent statements are
executed. Control is then transferred to the next statement after
END CASE
.
The syntax for a searched
CASE
statement is as follows:
CASE
WHEN boolean-expression THEN
Statements
END CASE;
We will implement the same
marks
example in this as well in the following manner:
warehouse_db=# CREATE OR REPLACE FUNCTION search_case(marks
integer)
RETURNS text AS $$
DECLARE
grade text;
BEGIN
CASE
WHEN marks >= 40 THEN
grade := 'PASS';
RETURN grade;
WHEN marks <= 39 AND marks > 0 THEN
grade := 'FAIL';
RETURN grade;
ELSE
grade := 'Did not appear in exam';
RETURN grade;
END CASE;
END;
$$ LANGUAGE plpgsql;
The result with
30
marks can be seen using the following statement:
warehouse_db=# SELECT search_case(30);
search_case
-------------
FAIL
(1 row)
