Part 117: Stored Procedures: Parameters, Logic & Execution

Unlike functions, Stored Procedures can manage transaction boundaries (COMMIT and ROLLBACK) directly inside their execution body.

1. Stored Procedure Example

CREATE OR REPLACE PROCEDURE transfer_funds(
  sender_id BIGINT,
  receiver_id BIGINT,
  amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  -- Deduct from sender:
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;

  -- Add to receiver:
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

  COMMIT;
END;
$$;

-- Executing the procedure:
CALL transfer_funds(101, 202, 500.00);

Key Takeaways

• Procedures are invoked with CALL.
• Stored procedures can control transactions with COMMIT and ROLLBACK.

In Part 118, we explore Control Flow in Stored Programs: IF/ELSE, LOOPS & WHILE!