PostgreSQL Functions

SQL functions in PostgreSQL are reusable blocks of code that perform operations and return a value. They allow encapsulating complex logic, calculations, or repeated queries to simplify development and maintain cleaner SQL code.

1. Types of Functions

• Built-in Functions: Provided by PostgreSQL, such as SUM(), AVG(), LENGTH(), NOW(), etc.
• User-Defined Functions (UDFs): Functions created by the user for custom logic.

2. Built-in Functions Examples

-- Aggregate functions
            SELECT AVG(salary) AS avg_salary, SUM(salary) AS total_salary
            FROM employees;

            -- String functions
            SELECT UPPER(name) AS upper_name, LENGTH(name) AS name_length
            FROM employees;

            -- Date/Time functions
            SELECT NOW() AS current_time, CURRENT_DATE AS today;

3. Creating a User-Defined Function

You can create a function in PostgreSQL using CREATE FUNCTION statement.

-- Function to calculate annual salary
            CREATE FUNCTION calculate_annual_salary(monthly_salary NUMERIC)
            RETURNS NUMERIC AS $$
            BEGIN
                RETURN monthly_salary * 12;
            END;
            $$ LANGUAGE plpgsql;

            -- Using the function
            SELECT name, calculate_annual_salary(salary) AS annual_salary
            FROM employees;

4. Function with Parameters

-- Function with multiple parameters
            CREATE FUNCTION salary_bonus(salary NUMERIC, bonus_percent NUMERIC)
            RETURNS NUMERIC AS $$
            BEGIN
                RETURN salary + (salary * bonus_percent / 100);
            END;
            $$ LANGUAGE plpgsql;

            -- Using the function
            SELECT name, salary_bonus(salary, 10) AS total_salary
            FROM employees;

5. Functions Returning Tables

Functions can return a table with multiple columns, acting like a parameterized view.

-- Function returning a table of employees in a department
            CREATE FUNCTION get_employees_by_department(dept_name TEXT)
            RETURNS TABLE(id INT, name TEXT, salary NUMERIC) AS $$
            BEGIN
                RETURN QUERY
                SELECT id, name, salary
                FROM employees
                WHERE department = dept_name;
            END;
            $$ LANGUAGE plpgsql;

            -- Using the function
            SELECT * FROM get_employees_by_department('IT');

6. Dropping a Function

-- Drop a function
            DROP FUNCTION IF EXISTS calculate_annual_salary(NUMERIC);

            -- Drop a function with multiple parameters
            DROP FUNCTION IF EXISTS salary_bonus(NUMERIC, NUMERIC);

7. Benefits of Functions

• Encapsulate logic for reuse across multiple queries.
• Simplify complex SQL operations and improve maintainability.
• Allow parameterization to handle dynamic inputs.
• Can improve performance by reducing repeated calculations.

8. Best Practices

• Name functions clearly to indicate their purpose.
• Use descriptive parameter names.
• Document your functions for better maintainability.
• Use set-returning functions carefully to avoid performance issues on large datasets.

Conclusion

PostgreSQL functions are a powerful feature that allows developers to encapsulate logic, perform calculations, and create reusable operations. By mastering built-in functions and user-defined functions, you can write cleaner, more maintainable, and efficient SQL queries. In the next tutorial, we will explore Aggregate Functions, which are commonly used for summarizing data in PostgreSQL.