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.
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:
psql someUserName -h 127.0.0.1 -d some_database
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:
CREATE DATABASE database_name;
A specific example is as follows:
CREATE DATABASE mydb;
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:
variable_name DATA TYPE [:= expression];
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
Always set a default value for the variable to avoid
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:
[variable name] [DATA TYPE] := [expression];
RAISE NOTICE '%', [variable_name1], [variable_name2], [variable_name3], [variable_name4];
RAISE NOTICE statement is useful for debugging SQL code as it will print out the value of the variable being declared, as shown here:
student_number INTEGER := 1;
firstname VARCHAR(30) := 'Abel';
lastname VARCHAR(30) := 'Maclead';
balance NUMERIC(10,2) := 230.50;
RAISE NOTICE 'Hello % % you have a student id of % and your total balance is % USD', firstname, lastname, student_number, balance;
END statements are used for wrapping multiple lines of SQL code into a statement block.
The above code should return the following results:
NOTICE: Hello Abel Maclead you have a student id of 1 and your total balance is 230.50 USD
Note the following in the above results:
student_numbervariable has an
INTEGERwith an initial value of 1.
VARCHARand have a string value with a 30 character length.
NUMERICdata 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:
create_time TIME := NOW();
RAISE NOTICE '%', create_time;
RAISE NOTICE '%', create_time;
The results should resemble the following:
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