PostgreSQL Stored Procedures and Functions

Functions and stored procedures allow blocks of SQL logic to be saved in the database and executed by name. Instead of writing the same query logic in every application that connects to the database, that logic lives once inside the database and any caller can invoke it. This promotes consistency, reduces duplication, and centralizes business logic.

Functions vs Stored Procedures

PostgreSQL has two related but distinct constructs:

PL/pgSQL — The Procedural Language

SQL is declarative — it describes what to retrieve. PL/pgSQL is PostgreSQL's procedural extension that adds programming constructs: variables, conditionals, loops, and exception handling. Most functions and procedures are written in PL/pgSQL, though other languages (Python, JavaScript, etc.) are also supported.

Creating a Simple Function

Syntax

CREATE OR REPLACE FUNCTION function_name(parameter_name data_type)
RETURNS return_type
LANGUAGE plpgsql
AS $$
BEGIN
    -- function body
    RETURN value;
END;
$$;

The $$ signs are dollar-quoting delimiters — they mark the start and end of the function body, avoiding the need to escape single quotes inside the code.

Example: Function to Calculate Tax

CREATE OR REPLACE FUNCTION calculate_tax(price NUMERIC, tax_rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN price * tax_rate / 100;
END;
$$;

Call the function directly in a query:

SELECT calculate_tax(200.00, 18);

Result: 36.00

Using Variables in a Function

CREATE OR REPLACE FUNCTION discounted_price(original NUMERIC, pct NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    discount_amount NUMERIC;
    final_price     NUMERIC;
BEGIN
    discount_amount := original * pct / 100;
    final_price     := original - discount_amount;
    RETURN final_price;
END;
$$;

SELECT discounted_price(500.00, 10);
-- Result: 450.00

The DECLARE block defines local variables. The := operator assigns values to them.

Functions That Return a Table

CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_name VARCHAR)
RETURNS TABLE(id INT, name VARCHAR, salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT e.id, e.name, e.salary
    FROM employees e
    WHERE e.department = dept_name;
END;
$$;

SELECT * FROM get_employees_by_dept('IT');

RETURNS TABLE defines the shape of the returned result set. RETURN QUERY executes a query and sends its results back as the function's output.

Conditional Logic with IF/ELSE

CREATE OR REPLACE FUNCTION grade_score(score INT)
RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 75 THEN
        RETURN 'B';
    ELSIF score >= 60 THEN
        RETURN 'C';
    ELSE
        RETURN 'F';
    END IF;
END;
$$;

SELECT grade_score(82);
-- Result: B

Loops in Functions

CREATE OR REPLACE FUNCTION sum_to_n(n INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    total INT := 0;
    i     INT := 1;
BEGIN
    WHILE i <= n LOOP
        total := total + i;
        i := i + 1;
    END LOOP;
    RETURN total;
END;
$$;

SELECT sum_to_n(10);
-- Result: 55

Creating a Stored Procedure

Stored procedures are defined with CREATE PROCEDURE and called with CALL. They are suited for operations that modify data and require transaction control.

CREATE OR REPLACE PROCEDURE give_raise(dept VARCHAR, raise_pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + (salary * raise_pct / 100)
    WHERE department = dept;

    COMMIT;
END;
$$;

CALL give_raise('IT', 10);

All IT employees receive a 10% salary increase. The COMMIT inside the procedure finalizes the change.

Dropping a Function or Procedure

DROP FUNCTION calculate_tax(NUMERIC, NUMERIC);
DROP PROCEDURE give_raise(VARCHAR, NUMERIC);

The parameter types must be included because PostgreSQL supports function overloading — multiple functions can share the same name as long as their parameter types differ.

Listing Functions

-- In psql
\df

-- In information_schema
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public';

Example: Complete Function and Procedure Workflow

-- Function: total value of items in an order
CREATE OR REPLACE FUNCTION order_total(order_id_input INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    total NUMERIC;
BEGIN
    SELECT SUM(quantity * unit_price)
    INTO total
    FROM order_items
    WHERE order_id = order_id_input;

    RETURN COALESCE(total, 0);
END;
$$;

-- Use the function
SELECT order_id, order_total(order_id) AS total
FROM orders
ORDER BY total DESC;

Key Points

• Functions return a value and can be used inside SELECT queries.
• Stored procedures support transaction control (COMMIT/ROLLBACK) inside the procedure body.
• PL/pgSQL extends SQL with variables, conditionals, loops, and exception handling.
• The DECLARE block defines local variables; := assigns values to them.
• Functions can return a single value, a table, or use RETURNS VOID for side-effect-only logic.
• Use CREATE OR REPLACE to update an existing function without dropping it first.
• Function overloading allows multiple functions to share a name if their parameter types differ.