Variable declaration and assignment in PostgreSQL - Deep Dive

Overview - Variable declaration and assignment

What is it?

Variable declaration and assignment in PostgreSQL means creating a named storage space inside a block of code, like a function or a procedure, and giving it a value. Variables hold data temporarily while the code runs. You can change the value of a variable by assigning new data to it. This helps organize and reuse data within your database programs.

Why it matters

Without variables, you would have to repeat the same values or calculations many times, making your code longer and harder to manage. Variables let you store results, make decisions, and perform calculations step-by-step. This makes your database programs more flexible, easier to read, and less error-prone.

Where it fits

Before learning variables, you should understand basic SQL queries and how to write simple functions or procedures in PostgreSQL. After mastering variables, you can learn about control flow (like IF statements and loops) and more complex programming concepts inside the database.

Mental Model

Core Idea

A variable is a named container inside your database code that temporarily holds a value you can use and change while the code runs.

Think of it like...

Think of a variable like a labeled jar in your kitchen. You can put something inside the jar, take it out, or replace it with something else whenever you want while cooking.

┌─────────────────────────────┐
│  Variable Declaration Block │
├─────────────────────────────┤
│  variable_name : data_type   │
│  variable_value : assigned  │
│                             │
│  Use variable_name to access │
│  or change variable_value   │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Declaring Variables in PL/pgSQL

Concept: How to declare a variable with a name and data type inside a PL/pgSQL block.

In PostgreSQL, variables are declared inside a DECLARE section of a PL/pgSQL block, function, or procedure. You write the variable name followed by its data type. For example: DECLARE my_number INTEGER; my_text TEXT; This tells PostgreSQL to reserve space for these variables.

Result

Variables my_number and my_text are created but have no value yet (NULL).

Understanding that variables must be declared with a type before use helps prevent errors and clarifies what kind of data each variable holds.

2

Foundation

Assigning Values to Variables

Concept: How to give a variable a value using the assignment operator.

After declaring variables, you assign values using the := operator inside the BEGIN...END block. For example: BEGIN my_number := 10; my_text := 'Hello'; END; This sets the variables to hold these values during execution.

Result

my_number holds 10, and my_text holds 'Hello' for use in the block.

Knowing that assignment happens inside the execution block separates declaration from value setting, which is key to writing clear code.

3

Intermediate

Using Variables in Expressions and Queries

🤔Before reading on: Do you think variables can be used directly in SQL queries inside PL/pgSQL blocks? Commit to yes or no.

Concept: Variables can be used in expressions and SQL statements inside PL/pgSQL blocks to make dynamic queries and calculations.

You can use variables in SQL commands inside PL/pgSQL. For example: DECLARE user_count INTEGER; BEGIN SELECT COUNT(*) INTO user_count FROM users; RAISE NOTICE 'There are % users', user_count; END; Here, user_count stores the result of the query and is then used to display a message.

Result

The variable user_count holds the number of users, and the notice shows that number.

Understanding that variables bridge SQL and procedural code lets you write powerful, flexible database programs.

4

Intermediate

Default Values and NULL Behavior

🤔Before reading on: Do you think variables have a default value after declaration? Commit to yes or no.

Concept: Variables start with NULL if not assigned a value, which can affect logic and calculations.

When you declare a variable but don't assign a value, it holds NULL by default. For example: DECLARE x INTEGER; BEGIN IF x IS NULL THEN x := 5; END IF; END; This means you must handle NULL carefully to avoid unexpected results.

Result

Variable x is set to 5 because it was initially NULL.

Knowing that variables start as NULL helps prevent bugs from uninitialized data and guides safe programming practices.

5

Intermediate

Variable Scope and Lifetime

🤔Before reading on: Do you think variables declared inside a function are accessible outside it? Commit to yes or no.

Concept: Variables exist only inside the block or function where they are declared and disappear afterward.

Variables declared in a PL/pgSQL block are local to that block. For example, a variable declared inside a function cannot be used outside it. This means each function call gets its own fresh variables. CREATE FUNCTION test_var() RETURNS VOID AS $$ DECLARE temp_var INTEGER := 1; BEGIN RAISE NOTICE 'Value: %', temp_var; END; $$ LANGUAGE plpgsql; Calling test_var() prints the value, but temp_var is not accessible elsewhere.

Result

temp_var exists only during the function execution and is lost afterward.

Understanding variable scope prevents confusion about where data lives and avoids accidental data leaks or conflicts.

6

Advanced

Using %TYPE and %ROWTYPE for Flexible Declarations

🤔Before reading on: Do you think variable types can be linked to table columns to adapt automatically? Commit to yes or no.

Concept: PostgreSQL allows declaring variables based on existing table column types or entire row structures for easier maintenance.

Instead of hardcoding data types, you can declare variables like this: DECLARE user_name users.name%TYPE; user_record users%ROWTYPE; This means user_name has the same type as the 'name' column in 'users' table, and user_record matches the whole row structure. This keeps your code safe if the table changes.

Result

Variables automatically adapt to table structure changes, reducing errors.

Knowing how to link variable types to table columns improves code robustness and reduces maintenance effort.

7

Expert

Performance and Memory Considerations of Variables

🤔Before reading on: Do you think declaring many large variables inside functions can impact database performance? Commit to yes or no.

Concept: Variables consume memory during function execution, and excessive or large variables can affect performance and resource usage.

Each time a function runs, its variables are allocated in memory. Large variables like big text or arrays use more memory. Declaring many variables or large data structures can slow down execution or increase memory pressure. For example, declaring a large TEXT variable and filling it with big data inside a loop can cause slowdowns. Best practice is to keep variables as small and few as needed and release resources when possible.

Result

Efficient variable use leads to faster, more scalable database functions.

Understanding the resource cost of variables helps write high-performance database code and avoid hidden bottlenecks.

Under the Hood

When a PL/pgSQL block runs, PostgreSQL allocates memory space for each declared variable based on its data type. Variables hold values in this memory during execution. Assignments update the stored value. Variables are local to the block and discarded after execution. PostgreSQL manages this memory automatically, ensuring isolation between function calls.

Why designed this way?

This design isolates variable data per execution to avoid conflicts and ensure thread safety in multi-user environments. Using explicit declaration with types enforces data integrity and helps the database optimize storage and execution. Alternatives like dynamic typing were avoided to maintain performance and predictability.

┌───────────────┐
│ PL/pgSQL Code │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ DECLARE Block │
│ ┌───────────┐ │
│ │ Variables │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Execution     │
│ Memory Space  │
│ for Variables │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Values Stored │
│ and Updated   │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think variables declared outside a function are accessible inside it? Commit to yes or no.

Common Belief: Variables declared anywhere in the database can be used inside any function or procedure.

Reality: Variables declared inside a function or block are local and cannot be accessed outside or inside other functions unless passed as parameters.

Why it matters: Assuming global access leads to errors and confusion when variables seem missing or unchanged.

Quick: Do you think assigning a value to a variable with = works in PL/pgSQL? Commit to yes or no.

Common Belief: You can assign values to variables using the = operator like in many programming languages.

Reality: PL/pgSQL requires the := operator for assignment; using = causes syntax errors.

Why it matters: Using the wrong assignment operator breaks code and wastes time debugging syntax errors.

Quick: Do you think variables automatically update if the underlying table data changes? Commit to yes or no.

Common Belief: Once a variable is assigned a value from a table, it stays linked and updates automatically if the table changes.

Reality: Variables hold a snapshot of the value at assignment time; they do not track changes in the database automatically.

Why it matters: Expecting automatic updates causes stale data bugs and incorrect program behavior.

Quick: Do you think variables can be declared without specifying a data type? Commit to yes or no.

Common Belief: Variables can be declared without a data type and will adapt to assigned values dynamically.

Reality: PostgreSQL requires explicit data types for variables; dynamic typing is not supported in PL/pgSQL variables.

Why it matters: Not declaring types leads to syntax errors and misunderstanding of how PostgreSQL enforces data integrity.

Expert Zone

1

Variables declared with %ROWTYPE capture the entire row structure, which is useful but can cause subtle bugs if the table schema changes unexpectedly.

2

Using variables inside loops can cause performance issues if large data is repeatedly assigned without careful management.

3

The difference between := and = operators is critical; := is assignment, while = is comparison, and mixing them causes subtle bugs.

When NOT to use

Avoid using variables for storing large datasets; instead, use temporary tables or set-returning functions for better performance and scalability. Also, do not use variables for global state sharing; use session variables or configuration settings instead.

Production Patterns

In production, variables are used to hold intermediate results, control flow flags, and query outputs inside stored procedures. Developers often combine variables with control structures to build complex business logic inside the database, improving performance by reducing client-server round trips.

Connections

Programming Variables

Same pattern of named storage for temporary data in code.

Understanding variables in programming languages helps grasp database variables since both serve to hold and manipulate data during execution.

Memory Management

Variables consume memory during execution, linking to how systems allocate and free memory.

Knowing how memory works in computers clarifies why variable scope and lifetime matter for performance and resource use.

Mathematical Variables

Both represent placeholders for values that can change or be used in calculations.

Seeing variables as placeholders in math helps understand their role in storing and manipulating data in database code.

Common Pitfalls

#1 Using = instead of := for assignment causes syntax errors.

Wrong approach:

DECLARE
x INTEGER;
BEGIN
x = 5;
END;

Correct approach:

DECLARE
x INTEGER;
BEGIN
x := 5;
END;

Root cause:

Confusing assignment operator := with equality operator = common in other languages.

#2 Trying to use a variable outside its declared scope.

Wrong approach:

CREATE FUNCTION test() RETURNS VOID AS $$
DECLARE
x INTEGER := 10;
BEGIN
NULL;
END;
$$ LANGUAGE plpgsql;

-- Later trying to use x outside test() causes error.

Correct approach:

Use variables only inside the function or pass values via parameters or return values.

Root cause:

Misunderstanding variable scope and lifetime in PL/pgSQL.

#3 Not initializing variables before use leads to NULL-related bugs.

Wrong approach:

DECLARE
count INTEGER;
BEGIN
IF count > 0 THEN
RAISE NOTICE 'Count is positive';
END IF;
END;

Correct approach:

DECLARE
count INTEGER := 0;
BEGIN
IF count > 0 THEN
RAISE NOTICE 'Count is positive';
END IF;
END;

Root cause:

Assuming variables have default non-NULL values when they start as NULL.

Key Takeaways

Variables in PostgreSQL are named containers declared with a data type inside PL/pgSQL blocks to hold temporary data during execution.

You assign values to variables using the := operator inside the execution block, separating declaration from assignment.

Variables have local scope and lifetime limited to the block or function where they are declared, preventing unintended data sharing.

Using %TYPE and %ROWTYPE links variables to table structures, making code more maintainable and robust against schema changes.

Understanding variable behavior, scope, and memory impact is essential for writing efficient, error-free database programs.