Multiple Approaches to Variable Declaration in PostgreSQL: A Comprehensive Guide

Overview of Variable Declaration in PostgreSQL

In database query development, variables are essential tools for storing temporary data. Unlike database systems such as MS SQL Server, PostgreSQL does not directly support variable declaration syntax in pure SQL environments. This design difference stems from PostgreSQL's architectural philosophy, but the system provides multiple alternative approaches to achieve similar functionality.

Simulating Variables with WITH Clauses

In pure SQL queries, the closest approach to variable declaration is using WITH clauses (Common Table Expressions). This method simulates variable functionality by creating temporary result sets. While the syntax is less concise than traditional variable declaration, it meets basic functional requirements.

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

In this example, myconstants acts as a temporary table containing multiple "variables." var1 and var2 are assigned values 5 and 'foo' respectively, then used as filter conditions in the main query. This method is particularly suitable for scenarios requiring reuse of multiple constant values within a single query.

Variable Declaration in PL/pgSQL

For situations requiring complete variable support, PostgreSQL provides the PL/pgSQL procedural language. In PL/pgSQL, variable declaration follows strict syntax rules and must be defined in the DECLARE section.

DO $$
DECLARE myvar integer;
BEGIN
    SELECT 5 INTO myvar;
    
    DROP TABLE IF EXISTS tmp_table;
    CREATE TEMPORARY TABLE tmp_table AS
    SELECT * FROM yourtable WHERE id = myvar;
END $$;

SELECT * FROM tmp_table;

The advantage of this approach lies in providing complete variable support, including data type checking, scope management, and complex assignment operations. Note that this method requires PostgreSQL 9.0 or later and can only be used in PL/pgSQL environments.

Detailed Variable Declaration Syntax

In PL/pgSQL, variable declaration follows specific syntax structures. Each variable must specify a name and data type, with optional default values and constraints.

DECLARE
    user_id integer;
    quantity numeric(5);
    url varchar;
    myrow tablename%ROWTYPE;
    myfield tablename.columnname%TYPE;
    arow RECORD;

Variable declaration supports multiple advanced features:

• Creating constant variables using the CONSTANT keyword
• Specifying collation rules through COLLATE
• Ensuring non-null values with NOT NULL constraints
• Support for multiple assignment operators (:= or =)

Dynamic Configuration Settings as Variables

PostgreSQL also provides an innovative approach to simulating variables: using dynamic configuration settings. This method leverages PostgreSQL's configuration system to store temporary values.

-- Choose a prefix unlikely to be used by PostgreSQL
set session my.vars.id = '1';

select *
from person 
where id = current_setting('my.vars.id')::int;

The advantage of this method is its usability in any SQL client, not limited to psql. Note that configuration settings are always stored as varchar type, requiring explicit conversion to appropriate data types when used. This feature requires PostgreSQL 9.2 or later.

psql Client Variables

For users working with the psql client, PostgreSQL provides dedicated variable support. This approach is simple and direct but limited to psql environments.

my_db=> \set myvar 5
my_db=> SELECT :myvar + 1 AS my_var_plus_1;
 my_var_plus_1 
---------------
             6

For text variables, quotes are required:

\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';

Data Types and Type Copying

PostgreSQL provides a powerful type system supporting type copying using %TYPE and %ROWTYPE. This mechanism ensures consistency between variable declarations and database schemas.

DECLARE
    film_title film.title%TYPE;
    user_id users.user_id%TYPE;
    user_record users%ROWTYPE;

Advantages of using %TYPE include:

• No need to understand specific data types of referenced columns
• No requirement to modify function definitions when underlying data types change
• Improved code maintainability and portability

Variable Scope and Lifecycle

Understanding variable scope and lifecycle is crucial for writing correct PL/pgSQL code. Variables are valid within their declared blocks. Nested blocks can access variables from outer blocks, but variables with the same name hide outer definitions.

DO $$
DECLARE
    outer_var integer := 1;
BEGIN
    DECLARE
        inner_var integer := 2;
    BEGIN
        RAISE NOTICE 'Outer: %, Inner: %', outer_var, inner_var;
    END;
END $$;

Practical Recommendations and Best Practices

Choose appropriate variable declaration methods based on different usage scenarios:

• Use WITH clauses for simple query constants
• Use PL/pgSQL for complex business logic
• Use client variables for temporary testing in psql environments
• Consider dynamic configuration settings for cross-session persistence

In PostgreSQL 8.3 environments, WITH clauses and PL/pgSQL are the most reliable choices. As versions upgrade, consider using new features to simplify code structure.