Stored Procedures Basics

Learning Focus

Use this lesson to create PostgreSQL procedures with CREATE PROCEDURE, call them with CALL, and understand how procedures differ from functions.

Concept Overview

A stored procedure is a database object you invoke with CALL.

In PostgreSQL, procedures are distinct from functions:

• Procedures: invoked with CALL, do not return a value the way a function does, and are often used for operational or batch workflows.
• Functions: invoked in SQL expressions (SELECT my_func(...)), must return a value, and are commonly used for reusable computations.

Why is it important?

• Encapsulation: package multi-step operations behind a single call
• Consistency: centralize business workflows (with careful governance)
• Operational tasks: batch updates, maintenance routines, controlled migrations
• Reduced round trips: run multi-statement logic server-side

Where does it fit?

Procedures are typically used for:

• backfills and batch processing
• administrative routines
• controlled workflows that update multiple tables

---

Syntax & Rules

Core Syntax

Create a procedure:

CREATE PROCEDURE proc_name(arg_name arg_type, ...)
LANGUAGE plpgsql
AS $$
BEGIN
  -- statements
END;
$$;

Call a procedure:

CALL proc_name(...);

Drop a procedure:

DROP PROCEDURE IF EXISTS proc_name(arg_type, ...);

Available Options / Parameters

Feature	Example	Meaning / Notes
OR REPLACE	CREATE OR REPLACE PROCEDURE ...	update procedure definition
Parameter modes	IN, OUT, INOUT	input only vs output values
Language	LANGUAGE plpgsql	most common for procedural logic
Ownership	ALTER PROCEDURE ... OWNER TO ...	operational management

Key Rules and Considerations

• PostgreSQL procedures are invoked with CALL, not SELECT.
• OUT parameters are returned as a result row from CALL.
• Procedures are often used for side effects; keep them small and testable.
• Transaction control inside procedures exists in PostgreSQL, but is an advanced topic and must be used carefully (procedures invoked inside an explicit transaction cannot freely commit/rollback).

---

Step-by-Step Examples

Use this setup for the examples:

DROP PROCEDURE IF EXISTS create_customer(text, text);
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Example 1: Create and Call a Simple Procedure (Beginner)

CREATE OR REPLACE PROCEDURE create_customer(
  IN p_email text,
  IN p_name  text
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customers (email, name)
  VALUES (p_email, p_name);
END;
$$;

CALL create_customer('a@example.com', 'Ada');

SELECT customer_id, email, name
FROM customers
ORDER BY customer_id;

Expected output:

CREATE PROCEDURE
CALL
 customer_id |     email      | name
-------------+----------------+------
 1           | a@example.com  | Ada
(1 row)

Explanation:

• The procedure runs an INSERT.
• CALL returns CALL for procedures without OUT parameters.

---

Example 2: Return Values Using OUT Parameters (Intermediate)

DROP PROCEDURE IF EXISTS create_customer_returning(text, text);

CREATE OR REPLACE PROCEDURE create_customer_returning(
  IN  p_email text,
  IN  p_name  text,
  OUT new_customer_id bigint
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customers (email, name)
  VALUES (p_email, p_name)
  RETURNING customer_id INTO new_customer_id;
END;
$$;

CALL create_customer_returning('b@example.com', 'Bob');

Expected output:

DROP PROCEDURE
CREATE PROCEDURE
 new_customer_id
-----------------
 2
(1 row)

Explanation:

• OUT parameters are returned as a result row from CALL.
• RETURNING ... INTO is the PostgreSQL-native pattern to capture generated IDs.

---

Example 3: Procedures for Batch Updates (Intermediate)

Add a simple table and procedure to update many rows:

DROP TABLE IF EXISTS orders;
DROP PROCEDURE IF EXISTS mark_orders_paid(bigint);

CREATE TABLE orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  status text NOT NULL,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO orders (customer_id, status, amount) VALUES
  (1, 'pending', 10.00),
  (1, 'pending', 25.00),
  (2, 'pending', 5.00);

CREATE OR REPLACE PROCEDURE mark_orders_paid(IN p_customer_id bigint)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE orders
  SET status = 'paid'
  WHERE customer_id = p_customer_id
    AND status = 'pending';
END;
$$;

CALL mark_orders_paid(1);

SELECT order_id, customer_id, status
FROM orders
ORDER BY order_id;

Expected output:

DROP TABLE
DROP PROCEDURE
CREATE TABLE
INSERT 0 3
CREATE PROCEDURE
CALL
 order_id | customer_id | status
----------+-------------+--------
 1        | 1           | paid
 2        | 1           | paid
 3        | 2           | pending
(3 rows)

Explanation:

• Procedures are a convenient wrapper for multi-statement or batch DML.

---

Practical Use Cases

1) Controlled Backfills

Context: run a backfill in batches with a single entry point.

CALL backfill_some_column(10000);

2) Operational Runbooks

Context: encode a safe sequence of steps.

CALL rotate_partitions();

3) Admin Utilities

Context: create maintenance routines.

CALL rebuild_reporting_tables();

---

Common Mistakes & Troubleshooting

Mistake 1: Trying to SELECT a Procedure

Wrong:

SELECT create_customer('x@example.com', 'X');

What happens: PostgreSQL errors because procedures are invoked with CALL.

Fix:

CALL create_customer('x@example.com', 'X');

---

Mistake 2: Forgetting to Specify the Procedure Signature

What happens: DROP PROCEDURE can fail when overloaded procedures exist.

Fix: include argument types:

DROP PROCEDURE IF EXISTS create_customer(text, text);

---

Mistake 3: Hiding Too Much Logic in Procedures

What happens: debugging and migrations become harder.

Fix: keep procedures small, versioned, and tested; prefer SQL migrations for schema changes.

Debugging tips:

1. Use \df in psql to list functions/procedures and filter by name.
2. Add RAISE NOTICE statements temporarily in plpgsql for debugging.
3. Use explicit constraint names so errors are actionable.
4. Wrap complex procedures with logging in the application layer.

---

Best Practices

• ✅ Use procedures for operational workflows and batches; ❌ use them as a dumping ground for all logic.
• ✅ Prefer RETURNING for capturing generated IDs; ❌ run a separate SELECT to find inserted rows.
• ✅ Use OUT parameters when you need a result from CALL; ❌ rely on side effects only.
• ✅ Keep procedure signatures stable; ❌ change args without coordinating callers.
• ✅ Test procedures on staging data; ❌ ship untested write logic.

---

Hands-On Practice

Use this setup for the exercises:

DROP PROCEDURE IF EXISTS practice_add_one(integer);

Exercise 1 (Easy): Create a procedure

Task: Create practice_add_one(IN p_x integer, OUT y integer) that sets y = p_x + 1.

-- Your SQL here

Solution:

CREATE OR REPLACE PROCEDURE practice_add_one(IN p_x integer, OUT y integer)
LANGUAGE plpgsql
AS $$
BEGIN
  y := p_x + 1;
END;
$$;

---

Exercise 2 (Medium): Call the procedure

Task: Call practice_add_one(41).

-- Your SQL here

Solution:

CALL practice_add_one(41);

---

Exercise 3 (Advanced): Drop by signature

Task: Drop the procedure safely.

-- Your SQL here

Solution:

DROP PROCEDURE IF EXISTS practice_add_one(integer);

---

Connection to Other Concepts

Concept	Why it matters
Functions	functions are used in queries; procedures are called
Triggers	triggers often call functions for write-time logic
Transactions	procedures run inside transactions; transaction control is advanced
Constraints	procedures still must respect constraints
Migrations	procedures are schema objects that need versioning

---

Visual Learning Diagram

flowchart LR
  A[Client] --> B[CALL procedure]
  B --> C[plpgsql body]
  C --> D[DML/DDL steps]
  D --> E[OUT params result row]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E allNodes
  class B highlight

---

Common Pitfalls

Pitfall	Consequence	Prevention
Using SELECT to invoke procedures	errors	use CALL
Not specifying signature when dropping	wrong procedure remains	drop with arg types
Overloading without discipline	ambiguous calls	keep signatures clear
Hiding complex logic	hard debugging	keep procedures small and tested
Assuming procedures are portable	migrations break	document PostgreSQL-specific behavior

---

Quick Reference

CREATE OR REPLACE PROCEDURE p(...) LANGUAGE plpgsql AS $$ BEGIN ... END; $$
CALL p(...)
DROP PROCEDURE IF EXISTS p(type, ...)
ALTER PROCEDURE p(type, ...) RENAME TO p2
\df

What's Next

• Next: User-Defined Functions - Create functions you can call from SQL.
• Module Overview - Return to Stored Procedures and Functions overview.