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

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-variables/ below:

PostgreSQL - Variables - GeeksforGeeks

Last Updated : 15 Jul, 2025

PostgreSQL, one of the most powerful and advanced open-source relational database management systems, provides robust support for procedural programming through its PL/pgSQL language. A fundamental aspect of PL/pgSQL is the use of variables that play a crucial role in storing temporary data and facilitating complex computations within database functions and procedures.

Let us better understand the Variables in PostgreSQL to better understand the concept.

• PostgreSQL is an advanced open-source relational database management system (RDBMS) known for its robustness, extensibility and support for complex data types.
  
• One of the powerful features of PostgreSQL is its ability to use variables within its procedural language, PL/pgSQL.
  
• Variables in PostgreSQL allow developers to store temporary data for use within functions and stored procedures, enabling more dynamic and flexible queries and operations.

Syntax

The following illustrates the syntax for declaring a variable:

variable_name data_type [:= expression];

Let's analyze the above syntax:

• Variable Name: Specify the name of the variable. It is a good practice to assign a meaningful name to a variable. For example, instead of naming a variable "i" one should use index or counter.
  
• Data Type: Associate a specific data type with the variable. The data type can be any valid PostgreSQL data type such as INTEGER, NUMERIC, VARCHAR, and CHAR.
  
• Default Value: Optionally assign a default value to a variable. If you don’t, the initial value of the variable is initialized to NULL.

Let us take a look at some of the examples of Variables in PostgreSQL to better understand the concept.

DO $$ 
DECLARE
   counter    INTEGER := 1;
   first_name VARCHAR(50) := 'John';
   last_name  VARCHAR(50) := 'Doe';
   payment    NUMERIC(11,2) := 20.5;
BEGIN 
   RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
END $$;

Output:

Explanation: In this example, we declared four variables: 'counter', 'first_name', 'last_name', and 'payment'. Each variable is initialized with a specific value. The 'RAISE NOTICE' statement is used to display the values of these variables.

DO $$ 
DECLARE
   created_at time := NOW();
BEGIN 
   RAISE NOTICE '%', created_at;
   PERFORM pg_sleep(10);
   RAISE NOTICE '%', created_at;
END $$;

Output:

Explanation: In this example, we declared a variable 'created_at' and initialized it with the current time using the NOW() function. The 'pg_sleep(10)' function pauses the execution for 10 seconds.

The 'RAISE NOTICE' statements display the value of 'created_at' before and after the pause, showing that the time remains the same as it was initialized once.

• Variables declared within a block or function are only accessible within that block or function.
  
• Variables can be assigned values using the `:=` operator. Alternatively, you can utilize the `SELECT INTO` statement to assign values from query results to variables.
  
• When assigning values to variables, it is essential to ensure that the data types are compatible. PostgreSQL will generate an error if there is a type mismatch.
  
• If a variable is not explicitly initialized, its default value is NULL.

In conclusion, PostgreSQL variables are an integral part of developing efficient and dynamic database applications. Mastering the syntax for declaring variables in PostgreSQL not only enhances the functionality of database procedures but also contributes to cleaner, more maintainable code by allowing developers to build robust applications that meet modern data processing needs.

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