Stored procedures
Stored procedures and functions store reusable logic inside the database, written in PL/pgSQL or other languages.
In this page:
Syntax
CREATE FUNCTION function_name(param type) RETURNS type AS $$
BEGIN
RETURN expression;
END;
$$ LANGUAGE plpgsql;
CREATE PROCEDURE procedure_name(param type) AS $$
BEGIN
-- statements
END;
$$ LANGUAGE plpgsql;
CALL procedure_name(value);Stored procedures
CREATE FUNCTION returns a value and can be used inside queries, while CREATE PROCEDURE (PostgreSQL 11 and later) is invoked with CALL and can control transactions.
PL/pgSQL adds variables, IF, loops and exceptions. Use them for data-heavy logic close to the data, but keep business logic testable.
Note:
Functions are used in queries; procedures are run with CALL.
Example: Stored procedures
shop=# CREATE FUNCTION add_tax(price numeric, rate numeric DEFAULT 0.2) RETURNS numeric
shop-# LANGUAGE sql IMMUTABLE AS $$ SELECT round(price * (1 + rate), 2) $$;
shop=# SELECT add_tax(100), add_tax(100, 0.1);
add_tax | add_tax
---------+---------
120.00 | 110.00
shop=# CREATE PROCEDURE archive_old_orders(cutoff date) LANGUAGE plpgsql AS $$
shop$# BEGIN
shop$# INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < cutoff;
shop$# DELETE FROM orders WHERE created_at < cutoff;
shop$# END $$;
shop=# CALL archive_old_orders('2023-01-01');
CALL⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Putting all business logic in the database
- Forgetting to declare variables
- Confusing functions and procedures
Chapter Summary
- FUNCTION returns a value
- PROCEDURE is run with CALL
- PL/pgSQL adds control flow
- Keep logic testable
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: