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.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

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 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:

DO $$
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:

DO $$
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:

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:

  • The student_number variable has an INTEGER with an initial value of 1.

  • The firstname and lastname are VARCHAR and have a string value with a 30 character length.

  • The balance is a NUMERIC 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:

DO $$
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
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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.