Stored Procedures (CREATE PROCEDURE)

Create procedures that can contain transaction control and have no return value.

Overview

Stored procedures (added in PostgreSQL 11) are similar to functions but can commit and roll back transactions with COMMIT and ROLLBACK inside the procedure body. They are called with CALL, not SELECT. Procedures do not return a value (use functions for that). Use procedures when you need transaction control inside a procedure for example, processing batches where each chunk should be committed separately, or when the procedure must handle partial success.

Key Points

•CREATE PROCEDURE name(params) LANGUAGE plpgsql AS $$...$$
•Called with CALL name(args) not SELECT
•Procedures can use COMMIT/ROLLBACK inside their body
•No return value use OUT parameters for output
•Unlike functions, procedures cannot be used in expressions
•DROP PROCEDURE name(arg_types)

Examples

Simple stored procedure

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < cutoff_date;
  DELETE FROM orders WHERE created_at < cutoff_date;
  COMMIT;
END;
$$;

-- Call the procedure
CALL archive_old_orders('2023-01-01');

Stored Procedures (CREATE PROCEDURE)

Overview

Key Points

Examples

Exercises

Knowledge Check

How do you call a stored procedure?