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 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$;

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$;

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;