Why Variable declaration and assignment in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could stop juggling numbers in your head and let the database do the math perfectly every time?

The Scenario

Imagine you need to calculate the total sales for a day by adding many numbers manually on paper or in a simple text file without any way to store intermediate results.

The Problem

Doing this by hand is slow and easy to make mistakes. You have to remember every number and every step, and if you lose track, you must start over. It's frustrating and wastes time.

The Solution

With variable declaration and assignment, you can store values in named containers inside your database code. This lets you keep track of numbers, update them easily, and use them later without confusion.

Before vs After

✗ Before

total = 0
for each sale:
  total = total + sale_amount
print total

✓ After

DECLARE total INTEGER := 0;
FOR sale IN SELECT * FROM sales LOOP
  total := total + sale.amount;
END LOOP;
RAISE NOTICE '%', total;

What It Enables

It makes complex calculations and data handling inside your database simple, clear, and error-free.

Real Life Example

A shop owner can calculate daily earnings by storing each sale amount in a variable and adding them up automatically, avoiding manual errors.

Key Takeaways

Variables store data temporarily for easy use.

They help keep calculations organized and accurate.

Using variables saves time and reduces mistakes.