Declare Row Variable for PostgreSQL
Declare a Row Variable in PostgreSQL Introduction
This tutorial will explain how to declare a row variable for a PostgreSQL query or transaction in a PostgreSQL table. The article will cover the techniques used for the DECLARE
statement for SQL and how the variables should be defined before the variables are executed in a PostgreSQL query or transaction.
Prerequisites for using PostgreSQL
- PostgreSQL must be properly installed and configured.
Execute the service postgresql status
command to confirm the status is active
and then press the CTRL + C keys to exit.
- Verify the interactive PSQL command-line for PostgreSQL is installed and working properly by typing
psql -V
. The results should resemble the following.
Accessing PostgreSQL using the ‘psql’ command-line interface
Access the PostgreSQL database on the localhost server, using the psql
command-line interface, by executing the following command in a terminal window:
Enter the password and press “Return” at the system prompt to gain access to the database.
Create a PostgreSQL Database
Create a PostgreSQL database before declaring variables. Using the command prompt on windows, and the terminal on mac or Linux, the following syntax is used to create the database:
A specific example is as follows:
NOTE: To access the database created earlier, enter the command \c
followed by the database name.
Use ‘DECLARE’ for a Row Variable in PostgreSQL
The variable allows for temporarily storing data during code execution. The syntax for declaring variables in PostgreSQL is as follows:
Keep the following in mind when declaring variables:
It is best to assign a variable name and specify the name instead of just using a single letter.
Use an exact data type. Use one of the PostgreSQL data types (eg
INT
,NUMERIC
,CHAR
,VARCHAR
).Always set a default value for the variable to avoid
NULL
values.
The dollar-quoted string constant in PostgreSQL
PostgreSQL uses dollar-quoted string constants ($$
) at the beginning and ending of SQL blocks. An example follows:
[DECLARE SOMETHING HERE]
$$
Following are some examples of declarations in PostgreSQL used to initialize variables:
DECLARE
[variable name] [DATA TYPE] := [expression];
BEGIN
RAISE NOTICE '%', [variable_name1], [variable_name2], [variable_name3], [variable_name4];
END $$;
The RAISE NOTICE
statement is useful for debugging SQL code as it will print out the value of the variable being declared, as shown here:
DECLARE
student_number INTEGER := 1;
firstname VARCHAR(30) := 'Abel';
lastname VARCHAR(30) := 'Maclead';
balance NUMERIC(10,2) := 230.50;
BEGIN
RAISE NOTICE 'Hello % % you have a student id of % and your total balance is % USD', firstname, lastname, student_number, balance;
END $$;
NOTE: The BEGIN
and END
statements are used for wrapping multiple lines of SQL code into a statement block.
The above code should return the following results:
Note the following in the above results:
The
student_number
variable has anINTEGER
with an initial value of 1.The
firstname
andlastname
areVARCHAR
and have a string value with a 30 character length.The
balance
is aNUMERIC
data type, or a number set with two decimal values.
SQL variable declaration when using a block of code
A block in SQL is a code designed to execute a cluster of instructions at once. The example below evaluates and sets the default values of PostgreSQL variables:
DECLARE
create_time TIME := NOW();
BEGIN
RAISE NOTICE '%', create_time;
PERFORM pg_sleep(10);
RAISE NOTICE '%', create_time;
END $$;
The results should resemble the following:
NOTICE: 09:29:13.338604
When executing the above code:
Create a variable to declare the current time value.
Declare the value of the variable and set the execution to pause for 10 seconds.
The results of the variable will be printed out.
Conclusion of using the SQL ‘Declare Variable’ for PostgreSQL Query
This tutorial explained how to declare a row variable for a PostgreSQL query or transaction in a PostgreSQL table. The article covered the prerequisites for using PostgreSQL and how to access PostgreSQL using the ‘psql’ command-line interface. The tutorial also explained how to create a PostgreSQL database, how to use ‘DECLARE’ for a row variable in PostgreSQL, provided an explanation of the dollar-quoted string constant and of SQL variable declaration when using a block of code. Remember to always use an exact data type and to always assign a default value to a variable to avoid a NULL
value to declare a row variable in postgresql.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started