How to Use Variables in PostgreSQL

Shihab Sikder Feb 02, 2024
  1. Use DECLARE to Declare Variables in PostgreSQL
  2. Use RETURNING to Assign Value to Variables in PostgreSQL
How to Use Variables in PostgreSQL

This article will demonstrate how we can declare and assign values to variables in PostgreSQL.

Use DECLARE to Declare Variables in PostgreSQL

Usually, you’ll need variables in PL/SQL script. In the section called DECLARE, you need to tell the script what your variable is and what was its type.

In PL/SQL, there are two parts. One is the declaration, and another is the script part, where standard SQL is written. The format is like the following.

DO $$
DECLARE variable_name <TYPE>

BEGIN
    SQL Commands/Scripts
END $$

Now, we have a table of students and their tasks. Our job is to find a student that matches some condition and raise notice for the student.

The table Students is like the following:

CREATE TABLE Students(
    ID SERIAL,
    Student_Name VARCHAR NOT NULL,
    Task VARCHAR,
    Marks INT
);

Suppose you want to store the student name and task information, where id equals 3. Now, here’s a thing to mention, we don’t know the data type of the id, name, and task.

If the type is a mismatch, then an error might occur. To resolve this, we need to use <column_name>%type.

Example:

do $$
DECLARE
    _name students.student_name%type;
    _task students.task%type;
BEGIN
    SELECT student_name, task
    FROM students
    INTO _name,_task
    WHERE id=3;

    RAISE NOTICE '% got task %', _name,_task;

end; $$;

Output:

postgres=# select * from students;
 id | student_name | task | marks
----+--------------+------+-------
  1 | Alice        | HW1  |    10
  2 | Alice        | HW2  |     9
  3 | Alice        | HW3  |     0
  4 | Alice        | HW4  |     6
  5 | Bob          | HW1  |     6
  6 | Bob          | HW2  |    10
  7 | Bob          | HW3  |     8
  8 | Bob          | HW4  |     7
(8 rows)

If you’re running this sort of PL/SQL for the first time, RAISE will not work, meaning nothing will be shown after the execution of the SQL script. To enable this, you need to perform the following command in your psql shell.

SET client_min_messages TO NOTICE;

After setting this, you can see the output like this (after executing the PL/SQL command):

postgres=# do $$
postgres$# DECLARE
postgres$#     _name students.student_name%type;
postgres$#     _task students.task%type;
postgres$# BEGIN
postgres$#     SELECT student_name, task
postgres$#     FROM students
postgres$#     INTO _name,_task
postgres$#     WHERE id=3;
postgres$#
postgres$#     RAISE NOTICE '% got task %', _name,_task;
postgres$#
postgres$# end; $$;
NOTICE:  Alice got task HW3
DO

Here’s another keyword, INTO. It places the data of your selected columns to the respective variables.

Use RETURNING to Assign Value to Variables in PostgreSQL

You’ve seen that the ID is the SERIAL type data from the above table. So, it will increase by one after every insertion.

But during the insertion, we never know which id is being assigned to the current row.

So, let’s say you want to see the ID after the insert command to the student table. The command will be as follows:

do $$
DECLARE
    _id students.id%type;
BEGIN
    INSERT INTO Students(Student_Name, Task, Marks) 
    VALUES ('Trude','HW1',6)
    RETURNING id INTO _id;

    RAISE NOTICE 'Last insert has id: %', _id;

end; $$;

Output:

NOTICE:  Last insert has id: 9
postgres=# SELECT * FROM STUDENTS WHERE ID=9;
 id | student_name | task | marks
----+--------------+------+-------
  9 | Trude        | HW1  |     6
(1 row)

Also, you can use multiple queries inside the PL/SQL begin to part. Then, you can use the variable to check some conditions and do some CRUD operations.

More information is available here in the official documentation.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Variable