How to Use Variables in PostgreSQL
-
Use
DECLARE
to Declare Variables in PostgreSQL -
Use
RETURNING
to Assign Value to 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.