How to Declare a Variable in a PostgreSQL Query

1. Declare a Variable in a PostgreSQL Query
2. Use WITH Clause to Declare a Variable in a PostgreSQL Query
3. Use PostgreSQL Procedural Language to Declare a Variable in a PostgreSQL Query
4. Use Dynamic Config Settings to Declare a Variable in a PostgreSQL Query

A variable is a temporary allocation of memory in a program to store data that is declared using a particular data type. The data on the variable is discarded once the execution is complete and persistent storage is required to retrieve the data when required.

In this tutorial, we will learn the different ways we can use to create a variable in PostgreSQL and use the variable to execute a query on the database.

Declare a Variable in a PostgreSQL Query

Use the command below to log in to the PostgreSQL server.

david@david-HP-ProBook-6470b:~$ psql -U postgres
Password for user postgres:

Enter your password on the prompt that opens and press the Enter button on your keyboard.

psql (14.2 (Ubuntu 14.2-1.pgdg18.04+1))
Type "help" for help.

Create a database with the name variable_db, which we will use to create a table for testing purposes.

postgres=# create database variable_db;
CREATE DATABASE

Connect to the database we have just created using the following command.

postgres=# \c variable_db;
You are now connected to database "variable_db" as user "postgres".

Connecting to the database, we have just created ensures that any data definition language or manipulation language we execute affects only the variable_db database.

Create a table named vehicle that contains the fields id, vehicle_name, vehicle_type, vehicle_model, and vehicle_price.

The vehicle_name, vehicle_type, and vehicle_model fields are type string, while id and vehicle_price fields are type integer.

variable_db=# create table vehicle(vehicle_name varchar(30), vehicle_type varchar(30), vehicle_model varchar(30), vehicle_price integer, id SERIAL UNIQUE NOT NULL, PRIMARY KEY(id));
CREATE TABLE

Insert three records into the table providing the name, type, model, and price for each instance of a vehicle you add.

Copy and paste the SQL command below on your terminal and press Enter on your keyboard.

variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('Jaguar Range Rover Evoque','4WD','2019 Model',7000000);
INSERT 0 1

variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('Porsche Cayenne','4WD','2019 Model',7000000);
INSERT 0 1

variable_db=# insert into vehicle(vehicle_name, vehicle_type, vehicle_model, vehicle_price) values('BMW 1 Series','4WD','2019 Model',7000000);
INSERT 0 1

We will use the data in the table we have created above to learn the different ways we can create a variable and use the variable to execute queries.

Use WITH Clause to Declare a Variable in a PostgreSQL Query

Copy and paste the following code on your terminal and press the Enter button on your keyboard.

variable_db=# WITH prices AS(SELECT 7000000 as carprices)
SELECT vehicle_name  FROM vehicle, prices WHERE vehicle_price = prices.carprices;

The WITH clause allows us to create temporary tables and add a select query combined with an alias to create a temporary variable of a column.

The alias uses the keyword AS followed by a variable name containing a descriptive name to avoid confusion during execution.

The temporary table prices contain a temporary variable holding the value 7000000; we use the temporary variable to find which vehicles have that price in all the tables. The following is the result of the query.

       vehicle_name
---------------------------
 Jaguar Range Rover Evoque
 Porsche Cayenne
 BMW 1 Series
(3 rows)

Use PostgreSQL Procedural Language to Declare a Variable in a PostgreSQL Query

To create a procedural language, create a file named procedure.sql and write the following procedure into the file. You can copy and paste the code into the file.

DO $$
DECLARE price integer;
BEGIN
       SELECT 7000000 INTO price;
       DROP TABLE IF EXISTS expvehicles;
       CREATE TABLE expvehicles AS
       SELECT vehicle_name FROM vehicle WHERE vehicle_price = price;
END $$;
SELECT * FROM  expvehicles;

The procedure creates a variable named price that holds a value of 70000000. We will use this variable to filter the vehicles priced at that value.

The query result will be stored in a temporary table named expvehicles. The final statement of the procedure executes a select query that returns all the vehicles priced at 7000000.

Copy and paste the command below into your terminal to execute this file, and press the Enter button on your keyboard.

variable_db=# \i /home/david/Documents/work/upwork/jhinku-tutorials/procedure.sql
DO

The above command returns a table containing names of vehicles but not that the table is temporary, and the data will be lost after the execution.

       vehicle_name
---------------------------
 Jaguar Range Rover Evoque
 Porsche Cayenne
 BMW 1 Series
(3 rows)

Use Dynamic Config Settings to Declare a Variable in a PostgreSQL Query

We use the set keyword to declare variables at the session level or local level in dynamic config settings.

A variable declared at the session-level uses the session keyword, while a variable set at the local level uses the local keyword.

Set a session variable named price using the following command. Copy and paste the code into your terminal and press the Enter button.

variable_db=# set session my.vars.price = '7000000';
SET

Execute a query that uses the variable we have declared to find vehicles priced at 7000000. Use the following code to realize the above task.

variable_db=# SELECT vehicle_name FROM vehicle WHERE vehicle_price = current_setting('my.vars.price')::int;

The following is the result of executing the above query.

       vehicle_name
---------------------------
 Jaguar Range Rover Evoque
 Porsche Cayenne
 BMW 1 Series
(3 rows)