PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - IF Statement

In PostgreSQL, the IF statement is used within PL/pgSQL code blocks to perform conditional operations. It's similar to the IF statement found in most programming languages.

To use the IF statement, you'll typically be writing a function or a DO block.

Syntax:

IF condition THEN
    -- statements
ELSIF another_condition THEN
    -- statements
ELSE
    -- statements
END IF;

Example:

Let's say you want to create a function that determines if a number is positive, negative, or zero:

CREATE OR REPLACE FUNCTION check_number(numeric)
RETURNS TEXT AS $$
BEGIN
    IF $1 > 0 THEN
        RETURN 'Positive';
    ELSIF $1 < 0 THEN
        RETURN 'Negative';
    ELSE
        RETURN 'Zero';
    END IF;
END;
$$ LANGUAGE plpgsql;

To use the function:

SELECT check_number(5);  -- Returns 'Positive'
SELECT check_number(-3); -- Returns 'Negative'
SELECT check_number(0);  -- Returns 'Zero'

Using `IF` in a `DO` block:

If you just want to run a one-off IF statement without creating a function, you can use a DO block:

DO $$ 
BEGIN
    IF (SELECT count(*) FROM some_table) = 0 THEN
        RAISE NOTICE 'Table is empty.';
    ELSE
        RAISE NOTICE 'Table is not empty.';
    END IF;
END $$;

This will print a notice depending on the row count in some_table.

Notes:

Ensure you have the PL/pgSQL language enabled in your PostgreSQL database as the IF statements are a feature of this procedural language.
The IF statement can also be nested within other IF statements to handle more complex conditions.
Besides the IF statement, PL/pgSQL also provides CASE and LOOP constructs to help control the flow of your code.

Using CASE statement in PostgreSQL:

Description: Employ the CASE statement for conditional logic.

Code:

SELECT
    column_name,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result_default
    END AS alias
FROM table_name;

Conditional logic in SELECT queries in PostgreSQL:
- Description: Apply conditional logic directly in SELECT queries.
- Code:
```
SELECT
    column_name,
    (CASE WHEN condition THEN result ELSE default_result END) AS alias
FROM table_name;
```

PL/pgSQL IF statement example:

Description: Use the IF statement in PL/pgSQL.

Code:

CREATE OR REPLACE FUNCTION example_function()
RETURNS VOID AS $$
BEGIN
    IF condition THEN
        -- Code to execute if condition is true
    ELSE
        -- Code to execute if condition is false
    END IF;
END;
$$ LANGUAGE plpgsql;

Conditional execution in PostgreSQL functions:

Description: Execute code conditionally within PostgreSQL functions.

Code:

CREATE OR REPLACE FUNCTION example_function(arg INT)
RETURNS VOID AS $$
BEGIN
    IF arg > 0 THEN
        -- Code to execute if arg is greater than 0
    END IF;
END;
$$ LANGUAGE plpgsql;

Using CASE WHEN in PostgreSQL:

Description: A concise form of the CASE statement in SELECT queries.

Code:

SELECT
    column_name,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE result_default
    END AS alias
FROM table_name;

Nested IF statements in PL/pgSQL:

Description: Nest IF statements for complex logic in PL/pgSQL.

Code:

CREATE OR REPLACE FUNCTION nested_if_example(arg1 INT, arg2 INT)
RETURNS VOID AS $$
BEGIN
    IF arg1 > 0 THEN
        IF arg2 > 0 THEN
            -- Code to execute if both conditions are true
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

PostgreSQL stored procedure with IF condition:

Description: Incorporate IF conditions in a stored procedure.

Code:

CREATE OR REPLACE PROCEDURE example_procedure(arg INT)
AS $$
BEGIN
    IF arg > 0 THEN
        -- Code to execute if arg is greater than 0
    END IF;
END;
$$ LANGUAGE plpgsql;

Dynamic SQL within IF statements in PostgreSQL:

Description: Use IF statements for dynamic SQL execution.

Code:

CREATE OR REPLACE FUNCTION dynamic_sql_example(arg INT)
RETURNS VOID AS $$
BEGIN
    IF arg > 0 THEN
        EXECUTE 'SELECT * FROM table_name WHERE column_name = $1' USING arg;
    END IF;
END;
$$ LANGUAGE plpgsql;

Handling NULL values with IF statements in PostgreSQL:

Description: Manage NULL values using IF statements.

Code:

SELECT
    column_name,
    (CASE WHEN column_name IS NOT NULL THEN column_name ELSE 'N/A' END) AS alias
FROM table_name;

Error handling with IF statements in PL/pgSQL:

Description: Implement error handling using IF statements.

Code:

CREATE OR REPLACE FUNCTION error_handling_example(arg INT)
RETURNS VOID AS $$
BEGIN
    IF arg < 0 THEN
        RAISE EXCEPTION 'Input cannot be negative';
    END IF;
END;
$$ LANGUAGE plpgsql;

Using IF statements in stored procedures in PostgreSQL:

Description: Utilize IF statements within stored procedures.

Code:

CREATE OR REPLACE PROCEDURE example_procedure(arg INT)
AS $$
BEGIN
    IF arg > 0 THEN
        -- Code to execute if arg is greater than 0
    END IF;
END;
$$ LANGUAGE plpgsql;

Using IF statements in PostgreSQL CHECK constraints:
- Description: Apply IF statements within CHECK constraints.
- Code:
```
CREATE TABLE example_table (
    column_name INT CHECK (column_name > 0)
);
```

PostgreSQL - IF Statement

Syntax:

Example:

Using IF in a DO block:

Notes:

Using `IF` in a `DO` block: