PostgreSQL — создание рекурсивных представлений

Представление в PostgreSQL можно определить как виртуальную таблицу, полученную из базовых базовых таблиц или ранее определенных представлений. Представление — это не что иное, как оператор SQL, который хранится в базе данных с соответствующим именем. Мы рассмотрели, что такое рекурсивный запрос. Чтобы сохранить рекурсивный запрос или создать представление из рекурсивного запроса, PostgreSQL предоставляет рекурсивные представления. Итак, рекурсивные представления — это не что иное, как сохраненные рекурсивные запросы.

В этой статье мы узнаем о рекурсивных представлениях в PostgreSQL. Мы познакомим вас с концепцией рекурсивных представлений, покажем, как создаются рекурсивные представления, и самое главное, мы также увидим, как рекурсивные представления могут быть реализованы в PostgreSQL.

Создание рекурсивных представлений PostgreSQL:

PostgreSQL предоставляет два типа синтаксиса для создания рекурсивного представления.

Синтаксис:

CREATE RECURSIVE VIEW view_name(columns) 

AS

SELECT …; — SQL query

Что эквивалентно:

CREATE VIEW view_name AS 

WITH RECURSIVE view_name (column_names)

— recursive sql query

AS 

SELECT …)

SELECT column_names

FROM view_name;

Давайте рассмотрим несколько примеров рекурсивных представлений.

Запрос:

CREATE RECURSIVE VIEW fact(n, factorial) AS 
(
 SELECT 1 as n, 5 as factorial
   union all
 SELECT n+1, factorial*n FROM fact where n < 5
);
select * from fact;

Этот код создаст представление факториала натурального числа. что то же самое, что:

Запрос:

CREATE VIEW fact AS 
with recursive fact(n, factorial) as
(
SELECT 1 as n, 5 as factorial
 union all
SELECT n+1, factorial*n FROM fact where n < 5
)
select * from fact;

Оба дадут вам одинаковый результат.

Здесь, в этом примере, мы рассматриваем только n=5; Мы можем напечатать только последнюю строку, но здесь мы можем видеть, как происходит итерация и вычисление.

Выход:

CREATE VIEW
 n | factorial 
---+-----------
 1 |         5
 2 |         5
 3 |        10
 4 |        30
 5 |       120
(5 rows)

Давайте разберемся и поймем, что делает этот код:

• Самая первая строка «CREATE VIEW fact AS» включает ключевые слова, указывающие на то, что мы создаем представление « fact».
• Ключевые слова WITH RECURSIVE указывают, что это рекурсивный запрос, за которым следует общее табличное выражение (CTE), а в скобках мы указали список столбцов, которые мы ожидаем в нашем результате.
• Теперь есть два оператора SELECT, первый нерекурсивный, а второй рекурсивно вызывает « fact ».
• И, наконец, набор результатов из двух операторов SELECT был объединен с ключевым словом « UNION ALL» .

Давайте взглянем на другой пример, который рекурсивно производит от 1 до 10 чисел.

Запрос:

CREATE RECURSIVE VIEW tens(n) AS 
(
    SELECT 1 as n
 UNION ALL
   SELECT n+1 FROM tens
);
select * from tens limit 10;

Выход:

Теперь давайте посмотрим на некоторые реальные примеры рекурсивного представления, с помощью рекурсивного представления мы можем найти организационную иерархию.

Шаг 1: Создайте стол.

Запрос:

create table emp(
emp_id int, 
emp_name varchar(15),
manager_id int);

Шаг 2: Вставьте данные.

INSERT INTO emp (
emp_id,
emp_name,
manager_id
)
VALUES
(1, "Onkar", NULL),
(2, "Isaac", 1),
(3, "Jack", 1),
(4, "Aditya", 1),
(5, "Albert", 1),
(6, "Alex", 2),
(7, "Beain", 2),
(8, "Harry", 3),
(9, "Paul", 3),
(10, "Kunal", 4),
(11, "Pranav", 5);

Шаг 3: Эти данные представляют собой иерархию, например, Исаак работает под руководством Онкара, а Алекс работает под руководством Исаака. Таким образом, Онкар находится на высшем уровне. Этот пример даст нам результаты того, какой сотрудник работает на каком уровне.

Запрос:

create recursive view subordinates(emp_id, 
manager_id,emp_name, level) as
( 
 SELECT emp_id, manager_id, emp_name, 0 as level
 FROM emp WHERE manager_id IS NULL
  UNION ALL
 SELECT e.emp_id, e.manager_id, e.emp_name, level+1
 FROM emp e INNER JOIN subordinates s ON 
 s.emp_id = e.manager_id
);

SELECT * FROM subordinates;

Выход: