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:
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
Use Cases of Variables in PostgreSQL
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