Databases - Practical PostgreSQL - Conditional statements

Databases - Practical PostgreSQL
Previous Page	Home	Next Page

Conditional statements

A conditional statement specifies an action (or set of actions) that should be executed instead of continuing execution of the function, based on the result of logical condition specified within the statement. That definition of conditional statements may make them sound a bit complex, but they are actually fairly simple. Essentially, a conditional statement informs the parser that if a given condition is true, a specified action should be taken.

The IF/THEN statement

The IF/THEN statement allows you to specify a statement (or block of statements) that should be executed if a given condition evaluates true. The syntax of the IF/THEN statement is shown in Example 11-34.

Example 11-34. Syntax of an IF/THEN statement

CREATE FUNCTION 
identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
declarations

  BEGIN
    
    IF 
condition
 THEN
      
statement
;
      [...]
    END IF;
  
  END;
' LANGUAGE 'plpgsql';

In Example 11-35, a function is created that checks the stock of a book when given its book ID and edition number. The book ID is an internally recorded and tracked number listed in a few of the database's tables; thus, this function is designed to be used by other functions, as most users won't directly know the book ID number. The stock_amount function first retrieves the book's ISBN number with a SELECT INTO statement.

If the SELECT INTO statement could not retrieve an ISBN number for the book with the provided book ID number and edition number the stock amount function returns a value of –1, which should be interpreted as an error by the function that called it. The function's flow continues on if there was an ISBN number found for the book, and another SELECT INTO statement is used to retrieve the amount of stock remaining for the book in question. The stock amount is then returned and the function ends.

Example 11-35. Using the IF/THEN statement

CREATE FUNCTION stock_amount (integer, integer) RETURNS integer AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    b_id ALIAS FOR $1;
    b_edition ALIAS FOR $2;
     
     -- Declare variable to store the ISBN number.
    b_isbn TEXT;
     
     -- Declare variable to store the stock amount.
    stock_amount INTEGER;
  
  BEGIN
     
     -- This SELECT INTO statement retrieves the ISBN number of the row in
     -- the editions table that had both the book ID number and edition number
     -- that were provided as function arguments.
    SELECT INTO b_isbn isbn FROM editions WHERE
      book_id = b_id AND edition = b_edition;
     
     -- Check to see if the ISBN number retrieved is NULL.  This will
     -- happen if there is not an existing book with both the ID number
     -- and edition number specified in the function arguments. If the
     -- ISBN is null, the function returns a value of -1 and ends.
    IF b_isbn IS NULL THEN
      RETURN -1;
    END IF;
     
     -- Retrieve the amount of books available from the stock table
     -- and record the number in the stock_amount variable.
    SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn;
     
     -- Return the amount of books available.
    RETURN stock_amount;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-36 shows the result of the stock_amount function when it is called for the book ID value 7808 and edition number 1.

Example 11-36. Results of the stock_amount( ) function

booktown=# 
SELECT stock_amount(7808,1);

 stock_amount
--------------
           22
(1 row)

The IF/THEN/ELSE statement

The IF/THEN/ELSE statement allows you to specify a block of statements that should be executed if a condition evaluates to true, and also a block of statements that should be executed if the condition evaluates to false. The syntax of the IF/THEN/ELSE statement is shown in Example 11-37.

Example 11-37. Syntax of an IF/THEN/ELSE statement

CREATE FUNCTION 
identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
declarations

  BEGIN
    
    IF 
condition
 THEN
      
statement
;
      [...]
    ELSE
      
statement
;
      [...]
    END IF;
  END;
' LANGUAGE 'plpgsql';

In Example 11-38, essentially the same steps that were taken in Example 11-35 are taken again to retrieve the ISBN number, store it, then use it to retrieve the quantity in stock for the book in question.

Once the in-stock number is retrieved, an IF/THEN/ELSE statement is used to decide whether or not the number is above zero. If it is above zero the function returns a TRUE value, indicating that the title is in stock. If the in-stock is below zero, the function returns a FALSE value, indicating the title is out of stock. Again, this is a function designed to be used by another function, so only values are returned. Returned values must be interpreted by the function that called the in_stock() function.

Example 11-38. Using the IF/THEN/ELSE statement

CREATE FUNCTION in_stock (integer,integer) RETURNS boolean AS '
  DECLARE
     
     -- Declare aliases for function arguments.
    b_id ALIAS FOR $1;
    b_edition ALIAS FOR $2;
     
     -- Declare a text variable to hold the ISBN of the book
     -- once found.
    b_isbn TEXT;
     
     -- Declare an integer variable to hold the amount of stock.
    stock_amount INTEGER;
  
  BEGIN
     
     -- This SELECT INTO statement retrieves the ISBN number of
     -- the row in the editions table that had both the book ID
     -- number and edition number that were provided as function
     -- arguments.
    SELECT INTO b_isbn isbn FROM editions WHERE
      book_id = b_id AND edition = b_edition;
     
     -- Check to see if the ISBN number retrieved  is NULL.  This
     -- will happen if there is not an existing book with both the
     -- ID number and edition number specified in the function
     -- arguments. If the ISBN is null, the function returns a
     -- FALSE value and ends.
    IF b_isbn IS NULL THEN
      RETURN FALSE;
    END IF;
     
     -- Retrieve the amount of books available from the stock
     -- table and record the number in the stock_amount variable.
    SELECT INTO stock_amount stock FROM stock WHERE isbn = b_isbn;
     
     -- Use an IF/THEN/ELSE check to see if the amount of books
     -- available is less than or equal to 0.  If so, return FALSE.
     -- If not, return TRUE.
    IF stock_amount <= 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  
  END;
' LANGUAGE 'plpgsql';

Example 11-39 shows the result of the check_stock() function when it is called with the book ID value 4513 and edition number 2. A value of true is returned, indicating that the title is in stock.

Example 11-39. Results of the in_stock( ) function

booktown=# 
SELECT in_stock(4513,2);

 in_stock
----------
 t
(1 row)

Example 11-39 shows that a TRUE value was returned, indicating that the title is in stock.

The IF/THEN/ELSE/IF statement

The IF/THEN/ELSE/IF statement is a mechanism for linking several IF statements together in a series. First, one condition is checked. If the first condition evaluates to FALSE, another condition is checked, and so forth. A final ELSE can provide for the case when no condition evaluates to TRUE. The syntax for the IF/THEN/ELSE/IF statement follows:

CREATE FUNCTION 
identifier
 (
arguments
) RETURNS 
type
 AS '
  DECLARE
    
declarations

  BEGIN
    IF 
condition
 THEN
      
statement
;
      [...]
    ELSE IF 
condition

      
statement
;
      [...]
    END IF;
  END;
' LANGUAGE 'plpgsql';

This syntax shows the creation of a function that demonstrates the use of the IF/THEN/ELSE/IF statement. The books_by_subject() function first uses the provided argument, which should be a book subject, to retrieve the subject ID number of the subject in question. The first IF statement then checks to see if the argument received is the value all.

If the argument variable's value is all, the IF/THEN statement executes extract_all_titles() and assigns the returned list of books and subjects (returned as a text variable) to the found_text variable.

If all was not sent to the function as a parameter, an ELSE IF statement is used to check whether or not the subject ID number that was retrieved is zero or higher. If the value of sub_id is zero or higher, the function executes the statements in the body of the ELSE IF statement, which first use extract_title() to retrieve a list of the titles of all existing books classified under the user's provided subject, and returns the name of the subject with the acquired list of books.

Another ELSE IF statement is then nested within the previous ELSE IF statement, and is executed if the subject ID number has been set to NULL. If sub_id is null, the subject title passed to the function was not found in the booktown database when it was retrieved by the SELECT INTO statement at the function's beginning. In that case, the function returns the string subject not found .

Note: The two functions used within Example 11-38 are created later in this section as examples of using loops to control program flow.

Example 11-40. Using the IF/THEN/ELSE/IF statement

CREATE FUNCTION books_by_subject (text) RETURNS text AS '
  DECLARE
     
     -- Declare an alias for user input, which should be either all
     -- or the name of a subject.
    sub_title ALIAS FOR $1;
     
     -- Declare an integer to store the subject ID in, and a text
     -- variable to store the list of found books.  The text variable
     --  is set to a blank string.
    sub_id INTEGER;
    found_text TEXT :='''';
  
  BEGIN
       
       -- Retrieve the subject ID number for the book matching the
       -- title supplied by the user.
      SELECT INTO sub_id id FROM subjects WHERE subject = sub_title;
       
       -- Check to see if the function was given all as the the subject
       -- name.  If so, execute the SELECT INTO statement and return
       -- the found_text variable.
      IF sub_title = ''all'' THEN
        found_text extract_all_titles();
        RETURN found_text;
         
         -- If the function was NOT sent all as the name of the subject,
         -- check to see the subject ID number turned out to be within
         -- the valid range of subjects. If it did, execute the
         -- extract_title() function with the subject ID number as its
         -- argument, then assign the result to the found_text variable.
        ELSE IF sub_id  >= 0 THEN
          found_text := extract_title(sub_id);
          RETURN  ''\n'' || sub_title || '':\n'' || found_text;
           
           -- If the subject ID number was NULL, return a message telling
           -- the user that the subject specified could not be found.
          ELSE IF sub_id IS NULL THEN
            RETURN ''Subject not found.'';
          END IF;
        END IF;
    END IF;
    RETURN ''An error occurred. .'';
  END;
' LANGUAGE 'plpgsql';

Example 11-41 first shows the result of the books_by_subject function when it is called with all as the argument (an indication that the user wishes to view the books within all defined subjects). The example then shows the results received when Computers is passed as the function's argument (an indication that the user wishes to view only books categorized as computer-related books).

Example 11-41. Results of the books_by_subject() function

booktown=# 
SELECT books_by_subject('all');

books_by_subject

Arts:
Dynamic Anatomy

Business:

Children's Books:
The Cat in the Hat
Bartholomew and the Oobleck
Franklin in the Dark
Goodnight Moon

[...]

Science:

Science Fiction:
Dune
2001: A Space Odyssey

(1 row)

booktown=# 
SELECT books_by_subject('Computers');

                      books_by_subject
--------------------------------------------------------------

Computers:
Learning Python
Perl Cookbook
Practical PostgreSQL
Programming Python

(1 row)

Databases - Practical PostgreSQL
Previous Page	Home	Next Page