Differences Between Functions and Procedures

In a lot of programming languages, there's almost no difference between functions and procedures. But in SQL, there is. In PostgreSQL, functions and procedures aren't just two ways to run code. They're totally different mindsets.

A function in SQL can't change data in the database. It should only work with the data you pass in and return a result based on that. It's made to be used inside SELECT queries.

A procedure in SQL is made to change the database. So it can work with transactions (unlike functions), write stuff to the database. And you can't use it inside SELECT queries.

Here's a quick comparison:

Differences in SQL

In regular SQL, a function is like an expression: it calculates and returns a value. A procedure is an instruction: it does something, but doesn't participate in expressions.

Function in SQL

SELECT calculate_discount(200);

• Can be used in WHERE, ORDER BY, INSERT, UPDATE, etc.
• Should be pure: shouldn't change database state (if IMMUTABLE/STABLE).

Procedure in SQL

CALL process_order(123);

• Doesn't return a result.
• Can do COMMIT, ROLLBACK, call RAISE, run loops.

Differences in PL/pgSQL

Functions in PostgreSQL can be thought of as a group of calculations. They're super flexible: you can pass parameters, use conditionals, loops, cursors, subqueries, return rows, scalars, tables.

Functions in PL/pgSQL

CREATE FUNCTION square(x INT) RETURNS INT AS $$
BEGIN
    RETURN x * x;
END;
$$ LANGUAGE plpgsql;

Features:

• RETURNS is required
• Can use DECLARE, BEGIN, END, LOOP, IF, CASE
• Can't do COMMIT/ROLLBACK
• Can be called in SELECT, UPDATE, CHECK, WHERE, RETURNING

Call:

SELECT square(5);  -- will return 25

Procedures in PL/pgSQL

Procedures are a way to control actions. They're perfect when you need to:

• do a bunch of steps with logic;
• update and insert big amounts of data;
• use transaction control: COMMIT, ROLLBACK, SAVEPOINT.

CREATE PROCEDURE log_event(msg TEXT) AS $$
BEGIN
    INSERT INTO logs(message) VALUES (msg);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Features:

• No RETURNS
• Only called via CALL
• Allowed to use COMMIT, ROLLBACK, SAVEPOINT
• Great for batch processing, migrations, ETL

Call:

CALL log_event('Processing finished');

Why are functions and procedures separated?

Because they have different goals in SQL:

The key advantage of procedures — COMMIT

Procedures can control transactions inside themselves. So right inside a procedure you can do:

BEGIN;
-- logic
SAVEPOINT point1;
-- try to update
ROLLBACK TO point1;
COMMIT;

But in a function, COMMIT and ROLLBACK are forbidden. If you try — you'll get: ERROR: invalid transaction termination in function

This means that a function has to be deterministic and safe, while a procedure can do the "dirty work" — cleaning, logging, inserting.

Comparison Table

Where should you use what?

Use a function if:

• you want a return value;
• you're calling it in SELECT, filtering data;
• it's a simple calculation, check, or a wrapper for SQL.

Use a procedure if:

• you want to do complex actions;
• you need transaction control;
• you're processing batches, moving data, archiving, logging.