Home » PL/pgSQL » PL/pgSQL IF

PL/pgSQL IF

Summary: In this tutorial, you’ll learn how to execute one or more statements based on a condition using the PL/pgSQL IF statement.

To execute one or more statements based on Boolean conditions, you use the IF statement.

PL/pgSQL offers three forms of the IF statements:

  • IF-THEN
  • IF-THEN-ELSE
  • IF-THEN-ELSEIF

IF-THEN statement #

Here’s the basic syntax of the IF-THEN statement:

IF condition THEN
    statement1;
    statement2;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The IF-THEN statement evaluates a Boolean condition. If the condition evaluates to true, it executes one or more statements between the THEN and END IF keywords. Otherwise, it passes the control to the following statement after the END IF.

The Boolean condition may include operators such as:

  • Comparison operators (=, <>, >, >=, <, <=)
  • Logical operators like AND, OR, and NOT.
  • The IS NULL and IS NOT NULL operators If the condition involves NULL.

The following example defines a function that uses the IF-THEN statement to display a notice when the product with an id is not found:

CREATE FUNCTION get_price(id INT) RETURNS DEC
AS
$$
DECLARE
    v_price products.price%TYPE;
BEGIN
    SELECT price
    INTO v_price
    FROM products
    WHERE product_id = id;

    IF NOT FOUND THEN
      RAISE NOTICE 'The product with id % was not found', id;
    END IF;

    RETURN v_price;
END;
$$
LANGUAGE PLPGSQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How it works:

First, retrieve the price of the product from the products table and assign it to the v_price variable using the SELECT INTO statement:

SELECT price
INTO v_price
FROM products
WHERE product_id = id;

The FOUND is a special variable local to the block. The SELECT INTO statement sets the FOUND variable to true if there is a row returned or false otherwise.

Second, check the FOUND variable and display a notice if the product with the id does not exist:

IF NOT FOUND THEN
   RAISE NOTICE 'The product with id % was not found', id;
END IF;Code language: JavaScript (javascript)

Third, return the product price:

RETURN v_price;Code language: PHP (php)

If you call the get_price function and pass a valid product id, you’ll get the product price:

SELECT get_price(1);

Output:

 get_price
-----------
    999.99Code language: CSS (css)

However, if you use a product id that does not exist, you’ll get a notice:

SELECT get_price(1000);

Output:

NOTICE:  The product with id 1000 was not found
 get_price
-----------
      NULL
(1 row)
Code language: JavaScript (javascript)

IF-THEN-ELSE statement #

Here’s the syntax of the IF-THEN-ELSE statement:

IF condition THEN
    statement1;
ELSE
    statement2;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the condition is true, the IF statement executes the statement1. Otherwise, it executes the statement2 in the ELSE clause. For example:

CREATE OR REPLACE FUNCTION get_price(id INT) 
  RETURNS DEC
AS
$$
DECLARE
    v_price products.price%TYPE;
BEGIN
    SELECT price
    INTO v_price
    FROM products
    WHERE product_id = id;

    IF NOT FOUND THEN
      RAISE NOTICE 'The product with id % was not found', id;
    ELSE 
      RAISE NOTICE 'Price $%', v_price;
    END IF;

    RETURN v_price;
END;
$$
LANGUAGE PLPGSQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this example, we modify the get_price function to raise a notice when the product not found and display the price otherwise:

IF NOT FOUND THEN
   RAISE NOTICE 'The product with id % was not found', id;
ELSE 
   RAISE NOTICE 'Price $%', v_price;
END IF;
Code language: PHP (php)

If the product id is valid, you’ll get the price back with a message:

SELECT get_price(1);

Try it

Output:

NOTICE:  Price $999.99
 get_price
-----------
    999.99
(1 row)

If the product id does not exist, you’ll get the product with the id not found:

SELECT get_price(1000);

Try it

Output:

NOTICE:  The product with id 1000 was not found
 get_price
-----------
      NULL
(1 row)Code language: JavaScript (javascript)

IF-THEN-ELSEIF statement #

So far, you’ve learned how to use the IF-THEN and IF-THEN-ELSE statements to evaluate a single condition.

When you have multiple conditions to evaluate, you can use the IF-THEN-ELSEIF statement with the following syntax:

IF condition1 THEN
    statement1;
ELSEIF condition2 THEN
    statement2;
ELSEIF condition3 THEN
    statement3;
[ELSE
    else_statement;]
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The IF-THEN-ELSEIF evaluates conditions sequentially from top to bottom. If a condition is true, it stops evaluating the subsequent condition and executes the corresponding statement. If no condition is true, it executes the statements in the ELSE branch.

The following example defines a function that uses the IF-THEN-ELSEIF statement to get the price segment of a product based on its price:

CREATE OR REPLACE FUNCTION get_price_segment (id INT) 
  RETURNS VARCHAR AS 
$$
DECLARE
    v_price products.price%TYPE;
    v_price_segment VARCHAR;
BEGIN
    SELECT price
    INTO v_price
    FROM products
    WHERE product_id = id;

    IF NOT FOUND THEN
       RAISE NOTICE 'The product with id % was not found', id;
       RETURN NULL;
    END IF;

    IF v_price < 200 THEN
        v_price_segment = 'Low End';
    ELSEIF v_price >= 200 AND v_price < 400 THEN
        v_price_segment = 'Mid Range';
    ELSE
        v_price_segment = 'High End';
    END IF;

    RETURN v_price_segment;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

First, retrieve the price of the product with a specific id from the products table and assign it to the v_price variable:

SELECT
  price 
INTO v_price
FROM
  products
WHERE
  product_id = id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, raise a notice and return NULL if the product with the id was not found:

IF NOT FOUND THEN
   RAISE NOTICE 'The product with id % was not found', id;
   RETURN NULL;
END IF;Code language: PHP (php)

Third, set the price segment based on the product price using the IF-THEN-ELSEIF statement:

IF v_price < 200 THEN
   v_price_segment = 'Low End';
ELSEIF v_price >= 200 AND v_price < 400 THEN
   v_price_segment = 'Mid Range';
ELSE
   v_price_segment = 'High End';
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, return the product’s price segment:

RETURN v_price_segment;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the get_price_segment function to get the price segment of the product id 1:

SELECT get_price_segment(1);

Output:

 get_price_segment
-------------------
 High End

Nested IF statements #

Note that the IF statements can be nested. It means that you can place IF statements within other IF statements:

IF condition THEN
    statement1;
    IF condition2 THEN
        statement2;
    END IF;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Please notice that code can become difficult to read if you use multiple levels of nesting.

Summary #

  • Use the IF-THEN statement to execute statements when a condition is true.
  • Use the IF-THEN-ELSE statement to run statements when a condition is true and other statements when the condition is false.
  • Use the IF-THEN-ELSEIF statement to evaluate multiple conditions and execute statements when the corresponding condition is true.

Quiz #

Was this tutorial helpful ?
PL/pgSQL Procedures
Previously
PL/pgSQL Procedures
Up Next
PL/pgSQL CASE Statement
PL/pgSQL CASE Statement