RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.w3resource.com/PostgreSQL/snippets/postgres-declare-variable.php below:

Website Navigation

Guide to Declaring Variables in PostgreSQL

Guide to Declaring Variables in PostgreSQLLast update on December 31 2024 13:03:30 (UTC/GMT +8 hours)

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4