Stored Procedures in SQL: PostgreSQL Tutorial with Parameters, Transactions and Examples

A stored procedure is more than "saved SQL": it must coordinate statements, accept inputs, reject unsafe calls and keep data consistent after failure. The account-transfer procedure coordinates statements, traces balances and an audit row, demonstrates rollback and INOUT results, and shows error cases through practical exercises. Syntax and capabilities differ across database engines.

Stored procedures in SQL: what they store and how PostgreSQL calls them

A stored procedure is a named database routine invoked to perform a task. In PostgreSQL, CREATE PROCEDURE defines it; CALL supplies arguments and runs it.

A view names a query result shape. A PostgreSQL function can run through SELECT and return values or rows. A procedure uses CALL, fitting an operation that validates, updates and audits a transfer. Output parameters may return a result row, so procedures do not simply "return nothing".

This topic belongs to CS Fundamentals for Exams & Placements. Revise SQL Queries and Joins in DBMS: A Clear Guide first if SELECT, WHERE, joins or arithmetic updates are unclear.

Stored-procedure lab: create the exact account and audit data

Use a disposable PostgreSQL database because the lab creates tables and changes rows. Checks reject negative balances and non-positive transfers; foreign keys require real accounts. The audit table records successful calls. NUMERIC(10,2) stores two decimal places, while the identity and CURRENT_TIMESTAMP supply an ID and execution time. Each run records its own execution time in moved_at.

CREATE TABLE accounts (
  account_id INTEGER PRIMARY KEY,
  holder_name TEXT NOT NULL,
  balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

CREATE TABLE transfer_audit (
  transfer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_account INTEGER NOT NULL REFERENCES accounts(account_id),
  to_account INTEGER NOT NULL REFERENCES accounts(account_id),
  amount NUMERIC(10,2) NOT NULL CHECK (amount > 0),
  moved_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Seed and inspect three accounts:

INSERT INTO accounts (account_id, holder_name, balance)
VALUES
  (101, 'Asha', 1250.00),
  (202, 'Bharat', 400.00),
  (303, 'Charu', 900.00);

SELECT account_id, holder_name, balance
FROM accounts
ORDER BY account_id;

This baseline totals 1250.00 + 400.00 + 900.00 = 2550.00; transfer_audit is empty.

CREATE PROCEDURE with input parameters, validation and row locks

The inputs identify the debit account, credit account and amount. Validation rejects identical accounts and a missing, zero or negative amount before any change.

CREATE OR REPLACE PROCEDURE transfer_funds(
  IN p_from INTEGER,
  IN p_to INTEGER,
  IN p_amount NUMERIC
)
LANGUAGE plpgsql
AS $procedure$
DECLARE
  v_source_balance NUMERIC(10,2);
  v_locked_rows INTEGER;
BEGIN
  IF p_from = p_to THEN
    RAISE EXCEPTION 'Source and destination must differ';
  END IF;

  IF p_amount IS NULL OR p_amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be positive';
  END IF;

  PERFORM account_id
  FROM accounts
  WHERE account_id IN (p_from, p_to)
  ORDER BY account_id
  FOR UPDATE;

  GET DIAGNOSTICS v_locked_rows = ROW_COUNT;
  IF v_locked_rows <> 2 THEN
    RAISE EXCEPTION 'Both accounts must exist';
  END IF;

  SELECT balance INTO v_source_balance
  FROM accounts
  WHERE account_id = p_from;

  IF v_source_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient balance';
  END IF;

  UPDATE accounts
  SET balance = balance - p_amount
  WHERE account_id = p_from;

  UPDATE accounts
  SET balance = balance + p_amount
  WHERE account_id = p_to;

  INSERT INTO transfer_audit (from_account, to_account, amount)
  VALUES (p_from, p_to, p_amount);
END;
$procedure$;

PERFORM locks matching rows by ascending ID. GET DIAGNOSTICS verifies that both exist, then the procedure reads the locked source balance. This order removes inconsistent locking for opposite transfers in this example, but cannot prevent every deadlock.

After validation, the routine debits, credits, then audits. The insert follows both updates because it records an applied transfer. An exception rolls back this call's changes, including its audit row; locks remain until the surrounding transaction ends.

CALL the stored procedure and trace one successful transfer

Run the first transfer:

CALL transfer_funds(101, 202, 300.00);

Asha becomes 1250.00 - 300.00 = 950.00; Bharat becomes 400.00 + 300.00 = 700.00; Charu stays at 900.00. The total remains 2550.00: 950.00 + 700.00 + 900.00 = 2550.00.

SELECT account_id, holder_name, balance
FROM accounts
ORDER BY account_id;

Inspect stable audit values:

SELECT transfer_id, from_account, to_account, amount
FROM transfer_audit;

On this fresh setup, ID 1 is the first audit entry. Its fields match the call: one procedure coordinated two updates and one insert. The moved_at value records when your call executes.

Stored procedures, rollback and concurrent calls

From that state, call the procedure inside an outer transaction:

BEGIN;
CALL transfer_funds(202, 303, 125.50);

Inside it, 101 remains 950.00; 202 becomes 700.00 - 125.50 = 574.50; 303 becomes 900.00 + 125.50 = 1025.50. Audit count becomes 2, while 950.00 + 574.50 + 1025.50 = 2550.00.

Undo the transaction:

ROLLBACK;

Rollback restores 101 = 950.00, 202 = 700.00, 303 = 900.00 and audit count 1. The procedure contains no COMMIT or ROLLBACK, so the caller controls this boundary and how long affected rows stay locked.

With opposite transfers, Session A CALL transfer_funds(101, 202, 50.00) locks 101 then 202. Session B CALL transfer_funds(202, 101, 25.00) requests the same order, waits at 101, then reads the committed balances after Session A ends. The shared order prevents this two-row lock cycle, although other statements can still create deadlocks.

CREATE PROCEDURE, CALL and INOUT are PostgreSQL routine concerns; Transactions & Concurrency Control in DBMS treats the broader ACID, schedule, isolation and 2PL layer. PostgreSQL transaction rules do not automatically transfer to every engine.

Stored-procedure parameters, INOUT results and lifecycle

transfer_funds uses three IN parameters and returns no row. PostgreSQL INOUT receives and returns a changed value:

CREATE OR REPLACE PROCEDURE add_bonus(
  IN p_rate NUMERIC,
  INOUT p_total NUMERIC
)
LANGUAGE plpgsql
AS $procedure$
BEGIN
  IF p_rate < 0 THEN
    RAISE EXCEPTION 'Rate cannot be negative';
  END IF;
  p_total := ROUND(p_total * (1 + p_rate), 2);
END;
$procedure$;

Call it with 10 per cent:

CALL add_bonus(0.10, 800.00);

The result is p_total = 880.00 because 800.00 x 1.10 = 880.00. No account changes because the routine never references accounts. p_rate is read-only input; p_total enters as 800.00 and returns as 880.00.

In PostgreSQL, CREATE OR REPLACE PROCEDURE replaces a compatible definition; changing the name or input types creates another routine. In production, schema-qualify names and grant only required EXECUTE access. SECURITY DEFINER needs deliberate security design.

Stored-procedure errors, assessment patterns and exact exercises

Common errors and their corrections are:

Typical questions ask you to distinguish procedures from functions, identify parameter modes, trace rows, predict rollback, explain lock order, or choose CALL versus SELECT. KnowledgeGate's DBMS practice bank offers topic-wise questions across the subject. For placement revision, use CS Fundamentals for Placements by Sanchit Sir.

From the post-rollback state, solve in sequence:

1. CALL transfer_funds(202, 303, 125.50); gives 202 = 574.50, 303 = 1025.50, audit count 2: one amount moves and one audit row is added.

2. CALL transfer_funds(303, 303, 50.00); raises the same-account exception; balances and count stay unchanged because validation precedes updates.

3. CALL transfer_funds(101, 202, 1000.00); raises insufficient balance because 101 has 950.00; balances and count remain unchanged.

Stored procedures in SQL: the short version and next step

Remember six moves: choose a dialect, define inputs, validate, lock rows consistently, update and audit together, then test success and failure in a transaction. PostgreSQL CALL runs the procedure, but cannot make unsafe SQL safe.

Rebuild from an empty schema and predict each exercise before running it. For a structured DBMS and broader CS sequence, continue with Zero to Hero, Complete CS Course.