Database Reference
In-Depth Information
IF-THEN
IF-THEN
statements are the simplest of all
IF-ELSE
forms. The syntax of an
IF-THEN
statement is as follows:
IF boolean-expression THEN
Statements
END IF;
The fate of statements between the
THEN
and
END
block depends on the successful
evaluation of the
boolean-expression
to be true; otherwise, this block will
be ignored.
The following example could be one of the scenarios to update the
warehouse_tbl
table for certain criteria to be true:
IF warehouse_id > 2 THEN
UPDATE warehouse_tbl
SET street_address='Tulip Road'
WHERE street_address='Lilly Road';
END IF;
IF-THEN-ELSE
Now what if you have reached a point where you want your program to choose an
alternate course of action if it fails to meet the criteria for one, rather than simply
quitting.
IF-THEN-ELSE
statements takes care of this. The
ELSE
part will be executed
when the
IF
part fails to execute and vice versa. Its syntax is as follows:
IF boolean-expression THEN
Statements
ELSE
Statements;
END IF;
Let's modify the
getRecords()
function we created earlier and add an
IF-THEN-
ELSE
block as follows:
warehouse_db=# CREATE OR REPLACE FUNCTION getRecords()
RETURNS INTEGER AS $$
DECLARE
total INTEGER;
BEGIN
SELECT COUNT(*) INTO total FROM warehouse_tbl;
IF (total > 0) THEN
RETURN total;
ELSE