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

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;

主站蜘蛛池模板: 家居| 盘山县| 金湖县| 贞丰县| 德昌县| 龙州县| 克东县| 沙河市| 雅安市| 七台河市| 隆德县| 绥棱县| 石景山区| 丽江市| 阳西县| 阳高县| 耒阳市| 井陉县| 德阳市| 左云县| 多伦县| 盐城市| 罗源县| 凤冈县| 阳城县| 巩义市| 黎平县| 安龙县| 凌源市| 满洲里市| 怀仁县| 台东县| 肥乡县| 泾川县| 定安县| 那坡县| 赤壁市| 兴国县| 宁陵县| 余干县| 涟水县|