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:

Following are some examples of declarations in PostgreSQL used to initialize variables:

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:

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

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.