How to declare variables in PostgreSQL PLpgSQL?
Declaring Variables in PostgreSQL
Unlike some other relational databases, PostgreSQL does not support direct variable declaration in plain SQL. However, variables can be declared and used within PL/pgSQL blocks, which are utilized for creating stored procedures, functions, and triggers. This article explains how to declare variables in PostgreSQL, provides examples, and details their use cases.
Declaring Variables in PostgreSQL PL/pgSQL
Variables in PostgreSQL are declared in PL/pgSQL blocks within the DECLARE section. These blocks are commonly used in stored procedures or functions. Variables can store temporary data, control the flow, or hold intermediate results.
Syntax:
DO $$ -- Begin an anonymous PL/pgSQL block DECLARE variable_name data_type [DEFAULT value]; -- Declare a variable BEGIN -- Your code goes here END $$;
Example: Declaring and Using a Variable
Here’s how to declare and use a variable within a PL/pgSQL block:
Code:
DO $$ -- Begin an anonymous PL/pgSQL block
DECLARE
user_count INTEGER; -- Declare an integer variable
BEGIN
-- Assign the result of a query to the variable
SELECT COUNT(*) INTO user_count FROM users;
-- Use the variable in a conditional statement
IF user_count > 100 THEN
RAISE NOTICE 'There are more than 100 users.';
ELSE
RAISE NOTICE 'There are % users.', user_count;
END IF;
END $$;
Explanation:
- DO $$: Initiates an anonymous PL/pgSQL block.
- DECLARE: Introduces the declaration section where variables are defined.
- user_count INTEGER: Declares a variable named user_count of type INTEGER.
- SELECT COUNT(*) INTO user_count: Assigns the count of rows in the users table to the user_count variable.
- RAISE NOTICE: Outputs messages to the console, demonstrating the variable’s usage.
Additional Example: Using a Default Value
Variables can also have default values assigned during declaration:
Code:
DO $$
DECLARE
greeting TEXT DEFAULT 'Hello, World!'; -- Default value assignment
BEGIN
-- Output the value of the variable
RAISE NOTICE '%', greeting;
END $$;
Explanation
- TEXT DEFAULT 'Hello, World!': Declares a text variable with a default value.
- RAISE NOTICE '%', greeting: Outputs the value of the variable.
Use Cases of Variables in PostgreSQL
- Stored Procedures: Managing temporary data and controlling logic within procedures.
- Data Validation: Checking and manipulating data before insertion or updates.
- Control Flow: Implementing conditional operations based on variable values.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics