1. Functions

Purpose

Reusable code blocks that return a value (scalar, record, or table).

Good for calculations, data transformations, or encapsulating query logic.

Key Points

• Must return something.
• Runs inside the caller’s transaction (cannot commit/rollback independently).
• Invoked with SELECT.

Syntax Example

CREATE OR REPLACE FUNCTION add_numbers(a int, b int)
RETURNS int AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT add_numbers(5, 3);
-- Result: 8

When to Use

• Complex calculations (e.g., tax, discount).
• Returning a table or custom result set.
• Logic reused in triggers or queries.

2. Procedures

Purpose

Perform tasks such as maintenance, batch updates, or multi-step operations that may need their own transaction control.

Key Points

• Do not need to return a value (but can use OUT parameters).