Postgres Variables

June 24, 2023 | by Arround The Web | No comments

A variable refers to a named location in the memory with a value that can change during execution. The variable name is a symbolic identifier which is bound to the memory location.

In addition, each variable must be of a given data type that is used to determine the various properties of the variable. In addition, the data type determines the size and layout of the variable in the memory, the range of values, and the operations that we can apply to it.

In this tutorial, we will learn how to define, reference, and use the PSQL variables in PostgreSQL.

PostgreSQL Declare Variable

Before using a PostgreSQL variable, we need to declare the variable within a PL/pgSQL block. The syntax of variable definition in PostgreSQL is as follows:

DO $$
DECLARE
variable_name data_type;
BEGIN
-- code
END $$;

In the given syntax, the variable_name refers to the name of the variable that we wish to declare, while the data_type defines the variable’s data type. The data type must be a valid PostgreSQL data type.

Example:
The following example demonstrates how to declare a variable called “my_var” in PostgreSQL:

DO $$
DECLARE
my_var INTEGER;
BEGIN
-- code block
END $$;

In this case, the “my_var” variable has an INTEGER data type.

We can also assign an initial value to the variable when we declare it as shown in the following example:

DO $$
DECLARE
my_var INTEGER := 100;
BEGIN
-- code block
END $$;

In this case, unless explicitly specified, the variable should have a default value of 100.

Note: We can use either the := or = assignment operator to initialize and assign a value to a variable.

PostgreSQL %TYPE

In PostgreSQL, the %TYPE attribute allows us to define a variable with the same data type as a table column.

The syntax is as follows:

column_name%TYPE

The column_name is the column name from which you wish to copy the data type. An example usage is as follows:

DO $$
DECLARE
my_variable employees.salary%TYPE;
BEGIN
SELECT salary INTO my_variable FROM employees WHERE employee_id = 1;
RAISE NOTICE 'The salary is: %', my_variable;
END $$;

In this case, the “my_variable” is defined with a similar data type as the salary column in the employees’ table. We then use it to store the employee’s salary with an id of 1 and output that salary.

Data type copying can be beneficial when writing the functions or procedures that interact with table data, as if the column’s data type changes in the future, the variable automatically adopts the new data type.

Conclusion

We explored how to work with PostgreSQL variables. We learned how to define the variables in a PL/SQL block and how to use them.

Source: linuxhint.com