官术网_书友最值得收藏!

Conditionals

There are two main conditional constructs: IF ... END IF and CASE ... END CASE, which are similar to the Java switch construct or the Perl given construct. Both constructs evaluate a Boolean condition and execute the branch only if the condition results in a true value. Both IF and CASE allow for multiple branches with different condition evaluations—ELSIF and WHEN respectively—and both allow for a catch-all branch named ELSE.

Listing 13 shows how to use an IF statement. First, we extract the maximum file size from the files table and check it against the predefined file sizes to print out a human readable file size with the measurement unit:

testdb=> DO $code$
DECLARE
file_name text;
file_size numeric;
size_kb int := 1024;
size_mb numeric := size_kb * size_kb;
size_gb numeric := size_mb * size_mb;
unit text;
BEGIN
-- get the max file size
SELECT max( f_size )
INTO file_size
FROM files;

IF file_size > size_kb THEN
file_size := file_size / size_kb;
unit := 'kB';
ELSIF file_size > size_mb THEN
file_size := file_size / size_mb;
unit := 'MB';
ELSIF file_size > size_gb THEN
file_size := file_size / size_gb;
unit := 'GB';
ELSE
unit := 'bytes';

END IF;
RAISE INFO 'Biggest file size is % %', file_size, unit;
END $code$;
Listing 13:  Multiple branches IF example

Listing 14 shows the very same execution flow with a CASE statement. As you can see, translating an IF to a CASE statement mainly involves re-writing any ELSIF predicates to WHEN predicates:

testdb=> DO $code$
DECLARE
file_name text;
file_size numeric;
size_kb int := 1024;
size_mb numeric := size_kb * size_kb;
size_gb numeric := size_mb * size_mb;
unit text;
BEGIN
-- get the max file size
SELECT max( f_size )
INTO file_size
FROM files;

CASE
WHEN file_size > size_kb THEN
file_size := file_size / size_kb;
unit := 'kB';
WHEN file_size > size_mb THEN
file_size := file_size / size_mb;
unit := 'MB';
WHEN file_size > size_gb THEN
file_size := file_size / size_gb;
unit := 'GB';
ELSE
unit := 'bytes';
END CASE;
RAISE INFO 'Biggest file size is % %', file_size, unit;
END $code$;
Listing 14:  Multiple branches CASE e xample

The CASE example also has another operational form in which equality is imposed. A value is checked for equality against one or more values expressed in one or more WHEN predicates. In Listing 15, for example, the file_type variable is checked for equality against different values. Each WHEN predicate can check for multiple values (such as png or jpg):

testdb=> DO $code$
DECLARE
file_type text;
BEGIN
-- get the biggest file type
SELECT f_type
INTO STRICT file_type
FROM files
ORDER BY f_size DESC
LIMIT 1;

CASE file_type
WHEN 'txt', 'org' THEN RAISE INFO 'Biggest file is a text one';
WHEN 'png', 'jpg' THEN RAISE INFO 'Biggest file is an image one';
WHEN 'mp3' THEN RAISE INFO 'Biggest file is an audio';
ELSE
RAISE INFO 'Biggest file is of type %', file_type;
END CASE;
END $code$;
Listing 15:  Equality case example

The same result can be achieved with an IF conditional and the appropriate usage of the AND and OR Boolean operators, as follows:

IF file_type = 'org' OR file_type = 'txt' THEN
...
ELSIF file_type = 'png' OR file_type = 'jpg' THEN ...
END IF;

主站蜘蛛池模板: 丘北县| 博罗县| 黑河市| 新余市| 万载县| 罗城| 卓资县| 丰台区| 荔波县| 永定县| 藁城市| 黑河市| 泸水县| 呼和浩特市| 沈阳市| 浦北县| 游戏| 漳平市| 从江县| 黄陵县| 新疆| 天峻县| 林口县| 九龙县| 叶城县| 基隆市| 阳江市| 德格县| 湖北省| 本溪| 肃宁县| 拜城县| 民县| 宣恩县| 祁东县| 林西县| 高雄县| 湘乡市| 双辽市| 高邑县| 北海市|