We use variables to store the values that we wish to change later or reference without needing to declare or initialize them again. Even with PostgreSQL, it’s possible to declare and initialize the variables in PL/pgSQL. Besides, you can reference its value from a table. Declaring variables is a straightforward task as we will see in this post. Let’s begin!
How to Declare Variables in PL/PgSQL for PostgreSQL
Anytime you declare a variable in PostgreSQL, it gets assigned with a name in the memory location where you can reference it when you want to use it during code execution. When declaring the variables, you must specify the data type for the variable. The data types could be TEXT, INTEGER, DATE, etc.
To declare and initialize the variables in PostgreSQL, we use the following syntax:
For the variable name, ensure that you keep it meaningful for easy reference, then add its data type. You can choose to declare the variable and initialize it later or declare and initialize it on the same line of code.
Example 1: Declaring Variables in PostgreSQL
In PostgreSQL, we need a code block where we declare and initialize the variables. The first line in our code block starts with “DO $$” followed by the “DECLARE” keyword.
We then declare a variable named “course” of type TEXT. Next, we use the BEGIN statement to start the transaction block where we initialize the variable and use the RAISE NOTICE statement to print the variables. The “END $$” signals the end of the code block. Pressing the “Enter” key prints the variable to the terminal. That’s the simplest way of working with variables in PostgreSQL.
Note that we are using the “%” sign to reference the variable in our print statement. Suppose we want to declare and initialize the variable in the same statement. The process is simple. For this case, we immediately reference it in our RAISE NOTICE statement. The following is an example:
The previous scenario is ideal when you want to work with default values for your variable. If you already know what value to use, there is no need to initialize it in the transaction block. Go for the “declare and initialize” option, and you will get a more straightforward and shorter code.
Example 2: Changing a Variable’s Default Value
Note that even when you have a default value for your variable, it’s possible to initialize it and give it another variable. Maybe you only want to use the default value if none is given. But if you get a different value, you can initialize it to overwrite the default one.
In the following example, we have a variable named “name” which takes a default value. However, in our transaction block, we initialize the variable again to give it another value. When we print the variable in our RAISE NOTICE statement, the value that we added last is what is printed, overwriting the default value. Take a look at the following output:
Example 3: Working with a Constant
You can have a constant variable in your PostgreSQL code block. When you try to change its value, you get an error since the constant value can’t be changed. Using the previous example that we discussed, we get a constant variable if we add the CONSTANT keyword during the variable declaration.
Suppose we try changing its value in the transaction block. Watch how PostgreSQL throws an error:
That’s how you work with a constant variable in PostgreSQL.
Example 4: Working with a Table
All the examples that we discussed so far don’t reference their values from a table. However, maybe you want to declare a variable and fetch its value from an entry in your PostgreSQL table.
If we have the following table, let’s see how we can utilize it.
Our variable declaration syntax will sightly change and appear as follows:
variable_name table_reference%data_type;
For instance, in our first case, we have a variable named “name”. We fetch its value from the “students” table in the “course” column. The “%type” instructs PostgreSQL to use the same data type as the referenced table.
In the transaction block, we use the SELECT statement to fetch a value from the table and insert it INTO the declared variable WHERE a given condition in the table is met. For instance, the first “select” statement takes the “fname” value from the “students” table and uses it as the value for the “name” variable that we declared, provided that the student_id in the table = 1.
Lastly, we print the variables to the screen. The following is the code block that we use to achieve this case:
You can twerk around with the code block to work with any variables in your table using the same logic.
Conclusion
Declaring variables in PL/pgSQL for PostgreSQL is easy. Once you have the code block, you can reference the examples that are presented in this post to understand how you declare and initialize your variables.
