Function vs Procedure in PostgreSQL: Key Differences and Usage

In PostgreSQL, a function returns a value and can be used in SQL expressions, while a procedure does not return a value and is called with CALL. Procedures support transaction control commands like COMMIT and ROLLBACK, unlike functions.

Quick Comparison

This table summarizes the main differences between PostgreSQL functions and procedures.

Aspect	Function	Procedure
Return Value	Must return a value (scalar, composite, set)	Does not return a value
Call Syntax	Called within SQL expressions or SELECT	Called using CALL statement
Transaction Control	Cannot manage transactions (no COMMIT/ROLLBACK)	Can manage transactions inside (supports COMMIT/ROLLBACK)
Use Case	Calculations, data retrieval, reusable expressions	Complex operations, transaction control, batch tasks
Introduced In	Available since early PostgreSQL versions	Introduced in PostgreSQL 11
Volatility	Can be IMMUTABLE, STABLE, or VOLATILE	Typically VOLATILE due to transaction control

Key Differences

Functions in PostgreSQL are designed to return a value and can be used directly in SQL queries, such as in SELECT statements. They are limited in that they cannot execute transaction control commands like COMMIT or ROLLBACK. This means functions run within the calling transaction and cannot commit or rollback independently.

On the other hand, procedures do not return values and are invoked using the CALL statement. Procedures can contain transaction control commands, allowing them to commit or rollback transactions inside their body. This makes procedures suitable for complex operations that require explicit transaction management.

Functions are often used for calculations, data transformations, or returning query results, while procedures are better for performing tasks that involve multiple steps and transaction boundaries. Procedures were introduced in PostgreSQL 11 to fill this gap in transaction control capabilities.

Code Comparison

Here is an example of a PostgreSQL function that returns the total count of rows in a table.

sql

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

Procedure Equivalent

This procedure performs a similar task but does not return a value. Instead, it raises a notice with the count.

sql

CREATE OR REPLACE PROCEDURE show_employee_count() AS $$
DECLARE
  emp_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM employees;
  RAISE NOTICE 'Employee count: %', emp_count;
END;
$$ LANGUAGE plpgsql;

When to Use Which

Choose a function when you need to return a value that can be used in queries or expressions, such as calculations or data retrieval. Functions are ideal for reusable logic that fits within a single transaction.

Choose a procedure when you need to perform complex operations that require transaction control, such as committing or rolling back changes mid-operation. Procedures are better for batch jobs, administrative tasks, or workflows that need explicit transaction boundaries.

Key Takeaways

Functions return values and are used within SQL queries; procedures do not return values and are called with CALL.

Procedures support transaction control commands like COMMIT and ROLLBACK; functions do not.

Use functions for calculations and data retrieval; use procedures for complex operations needing transaction management.

Procedures were introduced in PostgreSQL 11 to enable transaction control inside server-side code.

Functions can be IMMUTABLE, STABLE, or VOLATILE; procedures are typically VOLATILE due to their nature.