PostgreSQL variables.

Preamble

In this tutorial, you will learn how to declare variables in PostgreSQL using syntax and examples.

What is a variable in PostgreSQL?

In PostgreSQL, a variable allows a programmer to temporarily store data at runtime.

The syntax for declaring a variable in PostgreSQL

DECLARE variable_name [ CONSTANT ] datatype [ NOT NULL ] [ { DEFAULT |:= } initial_value ]

Parameters and arguments of variables

• variable_name – The name to be assigned to the variable.
• CONSTANT – Optional. If specified, the variable value cannot be changed after the initialization of the variable.
• datatype – The data type to be assigned to a variable.
• NOT NULL – Optional. If specified, the variable cannot contain the value of NULL.
• initial_value – Optional. This is the value originally assigned to a variable when it is declared. If no
• initial_value is specified, the variable is assigned a value of NULL.

Declaring a variable

Below is an example of a PostgreSQL declaration of a variable with vSite name.

DECLARE vSite varchar;

In this example, the variable vSite is declared as the varchar data type.

Then you can later set or change the value of the vSite variable as follows:

vSite:= 'Google.com';

This operator will set the vSite variable to ‘Google.com’.

Declaring a variable with an initial value (not a constant)

Below is an example of how to declare a variable in PostgreSQL and give it an initial value. This differs from the constant in that the variable value can be changed later.

DECLARE vSite varchar DEFAULT 'Google.com';

OR:

DECLARE vSite varchar:= 'Google.com';

This will declare the variable named vSite as varchar data type and assign the initial value to ‘Google.com’.

Later you can change the value of the vSite variable as follows:

vSite:= 'Bing.com';

This SET operator will change the value of the vSite variable from ‘Google.com’ to ‘Bing.com’.

Declaring a constant

Here is an example of how to declare a constant in PostgreSQL with the name vSiteID.

DECLARE vSiteID CONSTANT integer DEFAULT 50;

OR:

DECLARE vSiteID CONSTANT integer:= 50;

This will declare a constant named vSiteID as integer data type and assign an initial value of 50. Since this variable is declared using the CONSTANT keyword, you cannot change its value after initializing the variable.

Learn PostgreSQL Tutorial; Full Course for Beginners

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.