Procedures (PostgreSQL 11+)

PostgreSQL 11 introduced stored procedures with the CREATE PROCEDURE statement. Procedures differ from functions in one key way: they support transaction control (COMMIT and ROLLBACK inside the procedure body).

Creating a Procedure

CREATE OR REPLACE PROCEDURE procedure_name(
  param1 data_type,
  param2 data_type
)
LANGUAGE plpgsql AS $$
BEGIN
  -- statements including COMMIT / ROLLBACK
END;
$$;

Calling a Procedure

Procedures are called with CALL, not SELECT:

CALL procedure_name(arg1, arg2);

A Simple Example

CREATE OR REPLACE PROCEDURE transfer_funds(
  from_id INT,
  to_id   INT,
  amount  NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END;
$$;

CALL transfer_funds(1, 2, 500.00);

Transaction Control Inside Procedures

This is the main advantage of procedures over functions:

CREATE OR REPLACE PROCEDURE batch_insert(p_count INT)
LANGUAGE plpgsql AS $$
DECLARE
  i INT;
BEGIN
  FOR i IN 1..p_count LOOP
    INSERT INTO log_entries(created_at) VALUES (NOW());
    IF i % 1000 = 0 THEN
      COMMIT;  -- commit every 1000 rows
    END IF;
  END LOOP;
  COMMIT;  -- final commit
END;
$$;

Functions cannot contain COMMIT or ROLLBACK — they always run within the caller's transaction.

OUT Parameters

Procedures can return values through OUT parameters:

CREATE OR REPLACE PROCEDURE get_stats(
  OUT total_employees INT,
  OUT avg_salary      NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
  SELECT COUNT(*), AVG(salary)
  INTO   total_employees, avg_salary
  FROM   employees;
END;
$$;

Calling with OUT parameters:

CALL get_stats(NULL, NULL);
-- Or capture results into variables in PL/pgSQL:
DO $$
DECLARE
  v_total INT;
  v_avg   NUMERIC;
BEGIN
  CALL get_stats(v_total, v_avg);
  RAISE NOTICE 'Total: %, Avg: %', v_total, v_avg;
END;
$$;

Functions vs Procedures

Feature	Function	Procedure
Transaction control (COMMIT/ROLLBACK)	No	Yes
Called with	SELECT or expression	CALL
Returns value	Yes (RETURNS clause)	Via OUT params
Can be used in SQL expressions	Yes	No
Requires PostgreSQL version	All	11+

Dropping a Procedure

DROP PROCEDURE procedure_name(param_types);