PostgreSQL - Functions

PostgreSQL - User-Defined Functions

User-Defined Functions (UDFs) in PostgreSQL are reusable blocks of code that perform a specific operation. They are stored in the database and can be called from SQL queries, much like built-in functions. This allows you to encapsulate complex logic, reduce code duplication, and create powerful, modular database applications.

PostgreSQL supports functions written in various languages, with PL/pgSQL (Procedural Language/PostgreSQL) being the most common. It also distinguishes between FUNCTIONs, which must return a value, and PROCEDUREs (added in PostgreSQL 11), which do not.

Syntax for Creating a Function

The basic syntax for creating a function in PL/pgSQL is:

CREATE [OR REPLACE] FUNCTION function_name(argument_list)
RETURNS return_datatype AS $$
-- The $$ symbols are 'dollar quotes', a modern way to write string literals
-- without needing to escape single quotes inside the function body.
DECLARE
    -- variable declarations here
BEGIN
    -- function logic here

    RETURN return_value; -- Must match the return_datatype
END;
$$ LANGUAGE plpgsql;

Key components:

• CREATE OR REPLACE: A convenient option that allows you to create the function or update it if it already exists, avoiding a DROP and CREATE sequence.
• argument_list: A comma-separated list of arguments, each with a name and a data type (e.g., user_id INT, new_email TEXT).
• RETURNS return_datatype: Specifies the data type of the value the function will return (e.g., INT, TEXT, BOOLEAN, or even TABLE(...)).
• DECLARE: An optional section for declaring local variables used within the function.
• BEGIN...END;: The block that contains the executable part of the function.
• LANGUAGE plpgsql: Specifies that the function is written in the PL/pgSQL language.

Example 1: A Simple Function without Arguments

Let’s create a function that returns the total number of employees from our employees table.

-- Assuming the 'employees' table from the GROUP BY tutorial exists.

CREATE OR REPLACE FUNCTION get_total_employee_count()
RETURNS INTEGER AS $$
DECLARE
    total_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO total_count FROM employees;
    RETURN total_count;
END;
$$ LANGUAGE plpgsql;

To call this function, you use it in a SELECT statement:

SELECT get_total_employee_count();

-- Result:
 get_total_employee_count
--------------------------
                        6
(1 row)

Example 2: A Function with Arguments

A more practical function might take an argument. Let’s create one that returns the average salary for a specific department.

CREATE OR REPLACE FUNCTION get_avg_salary_for_department(dept_name TEXT)
RETURNS NUMERIC AS $$
DECLARE
    avg_salary NUMERIC;
BEGIN
    SELECT AVG(salary)
    INTO avg_salary
    FROM employees
    WHERE department = dept_name;

    RETURN avg_salary;
END;
$$ LANGUAGE plpgsql;

Now, you can call it with different department names:

SELECT get_avg_salary_for_department('Engineering');

-- Result:
 get_avg_salary_for_department
-------------------------------
                  87500.000000

SELECT get_avg_salary_for_department('HR');

-- Result:
 get_avg_salary_for_department
-------------------------------
                  61000.000000

Security Best Practice: SECURITY INVOKER vs SECURITY DEFINER

By default, functions run with the permissions of the user who calls them (SECURITY INVOKER). This is generally the safest option. However, sometimes you need a function to run with the permissions of the user who created it (SECURITY DEFINER).

Use SECURITY DEFINER with extreme caution. It’s powerful for allowing restricted users to perform specific, controlled actions on tables they can’t normally access. A common mistake is to not properly sanitize inputs in SECURITY DEFINER functions, which can lead to privilege escalation attacks.

CREATE FUNCTION update_user_log()
RETURNS trigger AS $$
BEGIN
    -- This function might need to write to a log table that the calling user
    -- does not have direct access to.
    INSERT INTO audit_log(message) VALUES ('User updated profile.');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Pro Tip: When using SECURITY DEFINER, always set a secure search_path at the beginning of your function to prevent search path hijacking: SET search_path = pg_catalog, public;