PostgreSQL: Declaring Variables

In this PostgreSQL post explains how to declare variables in PostgreSQL with syntax and examples.

What is a variable in PostgreSQL?

In PostgreSQL, a variable allows a programmer to store data temporarily during the execution of code.

Syntax

The syntax to declare a variable in PostgreSQL is:

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

Parameters or Arguments

variable_name

The name to assign to the variable.

CONSTANT

Optional. If specified, the value of the variable can not be changed after the variable has been initialized.

datatype

The datatype to assign to the variable.

NOT NULL

Optional. If specified, the variable can not contain a NULL value.

initial_value

Optional. It is the value initially assigned to the variable when it is declared. If an initial_value is not specified, the variable is assigned a value of NULL.

Example - Declaring a variable

Below is an example of how to declare a variable in PostgreSQL called vSite.

DECLARE vSite varchar;

This example would declare a variable called vSite as a varchar data type.

You can then later set or change the value of the vSite variable, as follows:

vSite := 'AODBA.com';

This statement would set the vSite variable to a value of 'AODBA.com'.

Example - 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 is different from a constant in that the variable's value can be changed later.

DECLARE vSite varchar DEFAULT 'AODBA.com';

OR

DECLARE vSite varchar := 'AODBA.com';

This would declare a variable called vSite as a varchar data type and assign an initial value of 'AODBA.com'.

You could later change the value of the vSite variable, as follows:

vSite := 'mySite.com';

This SET statement would change the vSite variable from a value of 'AODBA.com' to a value of 'mySite.com'.

Example - Declaring a constant

Below is an example of how to declare a constant in PostgreSQL called vSiteID.

DECLARE vSiteID CONSTANT integer DEFAULT 50;

OR

DECLARE vSiteID CONSTANT integer := 50;

This would declare a constant called vSiteID as an integer data type and assign an initial value of 50. Because this variable is declared using the CONSTANT keyword, you can not change its value after initializing the variable.