Guide to Declaring Variables in PostgreSQL

PostgreSQL: Declaring Variables

In PostgreSQL, variables are often declared within functions or anonymous code blocks. They are used to store temporary data during the execution of a function or script. Variable declaration is primarily achieved through the PL/pgSQL procedural language.

This guide explains how to declare variables in PostgreSQL, provides syntax, and includes examples to help users leverage variables effectively in their queries and functions.

Syntax:

Variables in PostgreSQL are declared using the DECLARE keyword within the DO block or CREATE FUNCTION. Here's the basic syntax:

 
DO $$
DECLARE
    variable_name data_type [DEFAULT value];
BEGIN
    -- Statements using the variable
END $$;

Example 1: Declaring and Using a Variable

Code:

-- Use an anonymous DO block to declare a variable
DO $$
DECLARE
    employee_count INTEGER; -- Declare an integer variable
BEGIN
    -- Assign a value to the variable using a SELECT statement
    SELECT COUNT(*) INTO employee_count FROM employees;

    -- Print the variable value
    RAISE NOTICE 'Total employees: %', employee_count;
END $$;

Explanation:

1. Declaration:

• employee_count is declared as an INTEGER.

2. Assignment:

• The result of SELECT COUNT(*) is stored in employee_count using INTO.

3. Output:

• RAISE NOTICE prints the variable value.

Example 2: Using Variables in Functions

Code:

-- Create a function to calculate employee salaries
CREATE OR REPLACE FUNCTION calculate_bonus(salary_threshold NUMERIC)
RETURNS TABLE(employee_id INT, bonus NUMERIC) AS $$
DECLARE
    bonus_rate NUMERIC := 0.10; -- Declare and initialize a variable
BEGIN
    -- Use the variable in a query
    RETURN QUERY
    SELECT id, salary * bonus_rate AS bonus
    FROM employees
    WHERE salary > salary_threshold;
END $$ LANGUAGE plpgsql;

Explanation

1. Variable Declaration:

• bonus_rate is declared and initialized with 0.10 (10% bonus rate).

2. Function Logic:

• The RETURN QUERY uses the variable bonus_rate to calculate bonuses for employees with a salary above the threshold.

Example 3: Default Values in Variables

Code:

DO $$
DECLARE
    tax_rate NUMERIC DEFAULT 0.05; -- Declare with a default value
    total_salary NUMERIC;
BEGIN
    -- Assign the sum of salaries to the variable
    SELECT SUM(salary) INTO total_salary FROM employees;

    -- Calculate total tax
    RAISE NOTICE 'Total tax: %', total_salary * tax_rate;
END $$;

Explanation:

1. Default Initialization:

• tax_rate is initialized with a default value of 0.05.

2. Computation:

• The total tax is calculated using the variable total_salary and tax_rate.

Common Use Cases

1. Control Flow: Variables help manage control flow by storing intermediate results for loops or conditionals.

2. Dynamic Queries: Use variables to construct and execute dynamic SQL statements.

Additional Notes

• Scope: Variables declared within a DO block or function are local to that block/function.
• Type Casting: Ensure appropriate data types during declaration to avoid runtime errors.

All PostgreSQL Questions, Answers, and Code Snippets Collection.