JIYIK CN >

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

In PostgreSQL, DECLAREvariables are declared using

Often you will need variables in your PL/SQL scripts. In DECLAREthe section called , you need to tell the script what your variable is and what its type is.

In PL/SQL, there are two parts. One is the declaration and the other is the script part where the standard SQL is written. The format is as follows.

DO $$
DECLARE variable_name <TYPE>

BEGIN
    SQL Commands/Scripts
END $$

Now, we have a table of students and a table of their tasks. Our job is to find students that meet certain criteria and send out a notification for that student.

StudentsThe table looks like this:

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

Suppose you want to store student names and task information, where id is equal to 3. Now, it is important to mention here that we do not know the data type of id, name, and task.

If the types don't match, an error may occur. To fix 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 are running this PL/SQL for the first time, RAISEit will not work, which means nothing will be displayed after executing the SQL script. To enable this feature, you need to execute the following command in psql shell.

SET client_min_messages TO NOTICE;

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

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

This is another keyword, INTO. It places the data of the selected column into the corresponding variable.

RETURNINGAssign values ​​to variables in PostgreSQL using

As you have seen, ID is a SERIAL type data in the above table. Therefore, it will be incremented by one after each insert.

But during insert we never know which id is assigned to the current row.

So, suppose you want to see the ID after inserting into the student table. The command would be:

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 in the PL/SQL begin to part. Then, you can use the variable to check some conditions and do some CRUD operations.