SQL функции и процедуры

Ниже приведены примеры функций, процедур и триггеров для PostgreSQL по заданной схеме. У каждой конструкции есть комментарий (на русском языке), описывающий её назначение.

---

1. Скалярная функция

sql
-- Функция, принимающая ID актёра и возвращающая его возраст (целое число).
-- Рассчитывается на основе текущей даты и даты рождения.
CREATE OR REPLACE FUNCTION get_actor_age(actor_id INT)
  RETURNS INT
AS
$$
DECLARE
    actor_age INT;
BEGIN
    SELECT EXTRACT(YEAR FROM age(BirthDate))::INT
      INTO actor_age
    FROM Actors
    WHERE ActorID = actor_id;

    RETURN actor_age;
END;
$$ LANGUAGE plpgsql;

---

2. Подставляемая (inline) табличная функция

sql
-- Функция, возвращающая фильмы конкретного режиссёра по имени.
-- Возвращаемые столбцы: ID фильма, название, дата релиза и жанр.
CREATE OR REPLACE FUNCTION get_movies_by_director(director_name TEXT)
  RETURNS TABLE(movie_id INT, title VARCHAR, release_date DATE, genre VARCHAR)
AS
$$
BEGIN
    RETURN QUERY
    SELECT m.MovieID, m.Title, m.ReleaseDate, m.Genre
      FROM Movies m
      JOIN Directors d ON m.DirectorID = d.DirectorID
     WHERE d.Name = director_name;
END;
$$ LANGUAGE plpgsql;

---

3. Многооператорная табличная функция (multi-statement table function)

sql
-- Функция, возвращающая фильмографию актёра (название фильма и роль),
-- используя цикл FOR для формирования результата.
CREATE OR REPLACE FUNCTION get_actor_filmography(actor_id INT)
  RETURNS TABLE(movie_title VARCHAR(150), role VARCHAR(100))
AS
$$
DECLARE
    play_rec Play%ROWTYPE;
BEGIN
    FOR play_rec IN
        SELECT *
          FROM Play
         WHERE ActorID = actor_id
    LOOP
        RETURN NEXT (
            SELECT m.Title, play_rec.Role
              FROM Movies m
             WHERE m.MovieID = play_rec.MovieID
        );
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

---

4. Рекурсивная функция (пример использования рекурсивного CTE)

sql
-- Функция, формирующая список всех годов, в которые выходили фильмы, 
-- от минимального года релиза до максимального, используя рекурсивный CTE.
CREATE OR REPLACE FUNCTION get_years_of_movies()
  RETURNS SETOF INT
AS
$$
BEGIN
    RETURN QUERY
    WITH RECURSIVE years(y) AS (
         SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
           FROM Movies
         UNION ALL
         SELECT y + 1
           FROM years
          WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
    )
    SELECT y FROM years;
END;
$$ LANGUAGE plpgsql;

---

5. Хранимая процедура (без параметров или с параметрами)

sql
-- Процедура добавления нового режиссёра (с параметрами).
-- Добавляет запись в таблицу Directors.
CREATE OR REPLACE PROCEDURE add_director(
    _name VARCHAR,
    _birthdate DATE,
    _nationality VARCHAR
)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO Directors (Name, BirthDate, Nationality)
    VALUES (_name, _birthdate, _nationality);
END;
$$;

---

6. Рекурсивная хранимая процедура (пример с рекурсивным CTE)

sql
-- Процедура, которая выводит (через RAISE NOTICE) все годы, в которые выходили фильмы,
-- используя внутри себя рекурсивный CTE. 
CREATE OR REPLACE PROCEDURE show_years_of_movies()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    RAISE NOTICE 'Годы выхода фильмов:';

    FOR rec IN
        WITH RECURSIVE years(y) AS (
             SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
               FROM Movies
             UNION ALL
             SELECT y + 1
               FROM years
              WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
        )
        SELECT y FROM years
    LOOP
        RAISE NOTICE '%', rec.y;
    END LOOP;
END;
$$;

---

7. Хранимая процедура с курсором

sql
-- Процедура, которая перечисляет всех режиссёров, используя курсор.
CREATE OR REPLACE PROCEDURE list_directors()
LANGUAGE plpgsql
AS
$$
DECLARE
    cur_directors CURSOR FOR
        SELECT DirectorID, Name, Nationality FROM Directors;
    rec RECORD;
BEGIN
    OPEN cur_directors;
    LOOP
        FETCH cur_directors INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Director -> ID:%, Name:%, Nationality:%',
                     rec.directorid, rec.name, rec.nationality;
    END LOOP;
    CLOSE cur_directors;
END;
$$;

---

8. Хранимая процедура доступа к метаданным

sql
-- Процедура, которая просматривает информацию о таблицах
-- из схемы public с помощью представления information_schema.
CREATE OR REPLACE PROCEDURE show_tables()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name
          FROM information_schema.tables
         WHERE table_schema = 'public'
    LOOP
        RAISE NOTICE 'Таблица: %', rec.table_name;
    END LOOP;
END;
$$;

---

9. Триггер AFTER

Для демонстрации создадим дополнительную таблицу лога:

sql
-- Таблица для логирования операций над фильмами
CREATE TABLE MovieLog (
    LogID SERIAL PRIMARY KEY,
    MovieID INT,
    Action VARCHAR(50),
    ActionDate TIMESTAMP
);

Затем создаём функцию-триггер и сам триггер:

sql
-- Функция-триггер, которая срабатывает после вставки новой записи в Movies
-- и записывает информацию в MovieLog.
CREATE OR REPLACE FUNCTION after_insert_movies()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO MovieLog (MovieID, Action, ActionDate)
    VALUES (NEW.MovieID, 'INSERT', NOW());
    RETURN NEW; -- Возвращаем NEW, чтобы сохранить вставленную запись
END;
$$;

-- Создание триггера AFTER INSERT на таблицу Movies
CREATE TRIGGER trg_after_insert_movies
AFTER INSERT ON Movies
FOR EACH ROW
EXECUTE PROCEDURE after_insert_movies();

---

10. Триггер INSTEAD OF

Для срабатывания INSTEAD OF-триггера необходимо создать представление, в которое можно будет «вставлять» данные. Допустим, мы хотим иметь представление для удобного добавления связи фильм-актёр:

sql
-- Создаём представление, объединяющее фильмы и актёров.
CREATE OR REPLACE VIEW MoviesActorsView AS
SELECT m.MovieID,
       m.Title,
       a.ActorID,
       a.Name AS ActorName
  FROM Movies m
  JOIN Play p ON m.MovieID = p.MovieID
  JOIN Actors a ON p.ActorID = a.ActorID;

Теперь создаём функцию-триггер и триггер INSTEAD OF для вставки:

sql
-- Функция-триггер, которая при попытке вставки новой строки 
-- в представление MoviesActorsView вместо этого делает INSERT в таблицу Play.
CREATE OR REPLACE FUNCTION insert_into_moviesactorsview()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    -- При вставке в представление
    -- записываем данные в таблицу Play (роль, к примеру, 'Unknown')
    INSERT INTO Play(MovieID, ActorID, Role)
    VALUES (NEW.MovieID, NEW.ActorID, 'Unknown');
    RETURN NEW; -- Возвращаем NEW, формально завершая операцию
END;
$$;

-- Создаём триггер INSTEAD OF INSERT на представление
CREATE TRIGGER trg_instead_of_insert_moviesactorsview
INSTEAD OF INSERT ON MoviesActorsView
FOR EACH ROW
EXECUTE PROCEDURE insert_into_moviesactorsview();

---

Итог

Таким образом, у нас есть:

1. Скалярная функция – get_actor_age(actor_id INT).
2. Подставляемая табличная функция – get_movies_by_director(director_name TEXT).
3. Многооператорная табличная функция – get_actor_filmography(actor_id INT).
4. Функция с рекурсивным CTE – get_years_of_movies().
5. Хранимая процедура с параметрами – add_director(...).
6. Рекурсивная хранимая процедура (через рекурсивный CTE) – show_years_of_movies().
7. Хранимая процедура с курсором – list_directors().
8. Хранимая процедура доступа к метаданным – show_tables().
9. AFTER-триггер – trg_after_insert_movies + функция after_insert_movies().
10. INSTEAD OF-триггер – trg_instead_of_insert_moviesactorsview + функция insert_into_moviesactorsview().

Все они продемонстрированы с пояснительными комментариями, описывающими функциональность каждого блока.

Ниже приводится единый SQL-скрипт, в котором:

1. Создаётся схема (таблицы).
2. Создаются функции (скалярная, подставляемая табличная, многооператорная, рекурсивная).
3. Создаются хранимые процедуры (простая, рекурсивная, с курсором, доступ к метаданным).
4. Создаются вспомогательная таблица для лога и триггеры (AFTER и INSTEAD OF).
5. В конце выполняется вставка тестовых данных и демонстрация вызовов для проверки работы.

Внимание: Данный скрипт рассчитан на PostgreSQL. В других СУБД (MS SQL, Oracle и т.д.) могут потребоваться изменения синтаксиса.

---

sql
/*******************************************************************
 * 1. СОЗДАНИЕ ТАБЛИЦ
 *******************************************************************/

-- На всякий случай удаляем таблицы, если они уже существуют
DROP TABLE IF EXISTS Play CASCADE;
DROP TABLE IF EXISTS Actors CASCADE;
DROP TABLE IF EXISTS Movies CASCADE;
DROP TABLE IF EXISTS Directors CASCADE;
DROP TABLE IF EXISTS MovieLog CASCADE;
DROP VIEW IF EXISTS MoviesActorsView CASCADE;

-- Таблица режиссеров
CREATE TABLE Directors (
    DirectorID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    BirthDate DATE,
    Nationality VARCHAR(50)
);

-- Таблица фильмов
CREATE TABLE Movies (
    MovieID SERIAL PRIMARY KEY,
    Title VARCHAR(150),
    ReleaseDate DATE,
    Genre VARCHAR(50),
    DirectorID INT
);

-- Таблица актеров
CREATE TABLE Actors (
    ActorID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    BirthDate DATE,
    Nationality VARCHAR(50)
);

-- Таблица связи фильмов и актеров (многие ко многим)
CREATE TABLE Play (
    PlayID SERIAL PRIMARY KEY,
    MovieID INT,
    ActorID INT,
    Role VARCHAR(100)
);

-- Дополнительная таблица для логирования операций над фильмами
CREATE TABLE MovieLog (
    LogID SERIAL PRIMARY KEY,
    MovieID INT,
    Action VARCHAR(50),
    ActionDate TIMESTAMP
);

/*******************************************************************
 * 2. ФУНКЦИИ
 *******************************************************************/

--------------------------------------------------------------
-- 2.1 Скалярная функция: Возвращает возраст актёра по его ID
--------------------------------------------------------------
/*
   Функция get_actor_age(actor_id INT)
   Принимает: ID актёра
   Возвращает: целое число (возраст)
*/
CREATE OR REPLACE FUNCTION get_actor_age(actor_id INT)
  RETURNS INT
AS
$$
DECLARE
    actor_age INT;
BEGIN
    SELECT EXTRACT(YEAR FROM age(BirthDate))::INT
      INTO actor_age
    FROM Actors
    WHERE ActorID = actor_id;

    RETURN actor_age;
END;
$$ LANGUAGE plpgsql;

----------------------------------------------------------------
-- 2.2 Подставляемая (inline) табличная функция: 
--     Возвращает фильмы по имени режиссёра
----------------------------------------------------------------
/*
   Функция get_movies_by_director(director_name TEXT)
   Принимает: имя режиссёра
   Возвращает: набор строк (MovieID, Title, ReleaseDate, Genre)
*/
CREATE OR REPLACE FUNCTION get_movies_by_director(director_name TEXT)
  RETURNS TABLE(movie_id INT, title VARCHAR, release_date DATE, genre VARCHAR)
AS
$$
BEGIN
    RETURN QUERY
    SELECT m.MovieID, m.Title, m.ReleaseDate, m.Genre
      FROM Movies m
      JOIN Directors d ON m.DirectorID = d.DirectorID
     WHERE d.Name = director_name;
END;
$$ LANGUAGE plpgsql;

-------------------------------------------------------------------
-- 2.3 Многооператорная табличная функция (multi-statement): 
--     Возвращает фильмографию актёра: название фильма + роль
-------------------------------------------------------------------
/*
   Функция get_actor_filmography(actor_id INT)
   Принимает: ID актёра
   Возвращает: таблицу (movie_title, role)
*/
CREATE OR REPLACE FUNCTION get_actor_filmography(actor_id INT)
  RETURNS TABLE(movie_title VARCHAR(150), role VARCHAR(100))
AS
$$
DECLARE
    play_rec Play%ROWTYPE;
BEGIN
    FOR play_rec IN
        SELECT *
          FROM Play
         WHERE ActorID = actor_id
    LOOP
        RETURN NEXT (
            SELECT m.Title, play_rec.Role
              FROM Movies m
             WHERE m.MovieID = play_rec.MovieID
        );
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------
-- 2.4 Рекурсивная функция (с рекурсивным CTE):
--     Возвращает все годы релизов (от мин. года до макс.)
------------------------------------------------------
/*
   Функция get_years_of_movies()
   Принимает: ничего
   Возвращает: набор лет (SETOF INT),
               полученный рекурсивным CTE на основе дат релизов.
*/
CREATE OR REPLACE FUNCTION get_years_of_movies()
  RETURNS SETOF INT
AS
$$
BEGIN
    RETURN QUERY
    WITH RECURSIVE years(y) AS (
         SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
           FROM Movies
         UNION ALL
         SELECT y + 1
           FROM years
          WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
    )
    SELECT y FROM years;
END;
$$ LANGUAGE plpgsql;

  
/*******************************************************************
 * 3. ХРАНИМЫЕ ПРОЦЕДУРЫ
 *******************************************************************/

-------------------------------------------------------
-- 3.1 Процедура add_director (с параметрами):
--     Добавляет нового режиссёра в таблицу Directors.
-------------------------------------------------------
/*
   Процедура add_director(_name, _birthdate, _nationality)
   Параметры:
     _name - имя режиссёра,
     _birthdate - дата рождения,
     _nationality - национальность
   Действие:
     Выполняет INSERT в таблицу Directors.
*/
CREATE OR REPLACE PROCEDURE add_director(
    _name VARCHAR,
    _birthdate DATE,
    _nationality VARCHAR
)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO Directors (Name, BirthDate, Nationality)
    VALUES (_name, _birthdate, _nationality);
END;
$$;

-------------------------------------------------------------------
-- 3.2 Рекурсивная процедура show_years_of_movies (через CTE):
--     Выводит (RAISE NOTICE) все годы релизов фильмов
-------------------------------------------------------------------
/*
   Процедура show_years_of_movies()
   Параметры: нет
   Действие:
     С помощью рекурсивного CTE находит все годы, в которые выходили фильмы,
     и выводит их через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE show_years_of_movies()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    RAISE NOTICE 'Годы выхода фильмов:';

    FOR rec IN
        WITH RECURSIVE years(y) AS (
             SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
               FROM Movies
             UNION ALL
             SELECT y + 1
               FROM years
              WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
        )
        SELECT y FROM years
    LOOP
        RAISE NOTICE '%', rec.y;
    END LOOP;
END;
$$;

-------------------------------------------------------
-- 3.3 Процедура list_directors (с курсором):
--     Выводит (RAISE NOTICE) всех режиссёров
-------------------------------------------------------
/*
   Процедура list_directors()
   Параметры: нет
   Действие:
     Использует курсор для прохода по всем режиссёрам в таблице Directors.
     Каждая запись выводится через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE list_directors()
LANGUAGE plpgsql
AS
$$
DECLARE
    cur_directors CURSOR FOR
        SELECT DirectorID, Name, Nationality FROM Directors;
    rec RECORD;
BEGIN
    OPEN cur_directors;
    LOOP
        FETCH cur_directors INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Director -> ID:%, Name:%, Nationality:%',
                     rec.directorid, rec.name, rec.nationality;
    END LOOP;
    CLOSE cur_directors;
END;
$$;

------------------------------------------------------------------
-- 3.4 Процедура show_tables (доступ к метаданным):
--     Показывает таблицы из схемы public (RAISE NOTICE)
------------------------------------------------------------------
/*
   Процедура show_tables()
   Параметры: нет
   Действие:
     Считывает из information_schema.tables названия всех таблиц
     в схеме public, выводит через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE show_tables()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name
          FROM information_schema.tables
         WHERE table_schema = 'public'
    LOOP
        RAISE NOTICE 'Таблица: %', rec.table_name;
    END LOOP;
END;
$$;

  
/*******************************************************************
 * 4. ТРИГГЕРЫ
 *******************************************************************/

--------------------------------------------
-- 4.1 AFTER-триггер на таблицу Movies
--------------------------------------------
/*
   Логика:
   - После вставки фильма (AFTER INSERT)
   - Записываем в MovieLog информацию об операции
*/
CREATE OR REPLACE FUNCTION after_insert_movies()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO MovieLog (MovieID, Action, ActionDate)
    VALUES (NEW.MovieID, 'INSERT', NOW());
    RETURN NEW; -- возвращаем NEW, чтобы сохранить вставленную запись
END;
$$;

CREATE TRIGGER trg_after_insert_movies
AFTER INSERT ON Movies
FOR EACH ROW
EXECUTE PROCEDURE after_insert_movies();

--------------------------------------------
-- 4.2 INSTEAD OF-триггер на представление
--------------------------------------------
/*
   1) Создаём представление MoviesActorsView, 
      которое объединяет фильмы и актёров через таблицу Play.
   2) Создаём INSTEAD OF-триггер, чтобы при вставке в это представление
      фактически делать INSERT в таблицу Play.
*/
CREATE OR REPLACE VIEW MoviesActorsView AS
SELECT m.MovieID,
       m.Title,
       a.ActorID,
       a.Name AS ActorName
  FROM Movies m
  JOIN Play p ON m.MovieID = p.MovieID
  JOIN Actors a ON p.ActorID = a.ActorID;

CREATE OR REPLACE FUNCTION insert_into_moviesactorsview()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    -- При вставке в представление "MoviesActorsView"
    -- записываем фактические данные в таблицу Play
    INSERT INTO Play(MovieID, ActorID, Role)
    VALUES (NEW.MovieID, NEW.ActorID, 'Unknown');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_instead_of_insert_moviesactorsview
INSTEAD OF INSERT ON MoviesActorsView
FOR EACH ROW
EXECUTE PROCEDURE insert_into_moviesactorsview();

  
/*******************************************************************
 * 5. ТЕСТОВЫЕ ДАННЫЕ И ВЫЗОВЫ (ДЕМОНСТРАЦИЯ)
 *******************************************************************/

------------------------------
-- 5.1 Вставка тестовых данных
------------------------------
-- Режиссеры
INSERT INTO Directors (Name, BirthDate, Nationality)
VALUES
  ('Steven Spielberg', '1946-12-18', 'USA'),
  ('Christopher Nolan', '1970-07-30', 'UK'),
  ('James Cameron', '1954-08-16', 'Canada');

-- Фильмы
INSERT INTO Movies (Title, ReleaseDate, Genre, DirectorID)
VALUES
  ('Jaws',         '1975-06-20', 'Thriller', 1),  -- Spielberg
  ('Inception',    '2010-07-16', 'Sci-Fi',   2),  -- Nolan
  ('Avatar',       '2009-12-18', 'Sci-Fi',   3);  -- Cameron

-- Актёры
INSERT INTO Actors (Name, BirthDate, Nationality)
VALUES
  ('Leonardo DiCaprio', '1974-11-11', 'USA'),
  ('Sam Worthington',   '1976-08-02', 'Australia'),
  ('Roy Scheider',      '1932-11-10', 'USA');

-- Привязка актёров к фильмам
INSERT INTO Play (MovieID, ActorID, Role)
VALUES
  (1, 3, 'Police Chief Martin Brody'),  -- Jaws, Roy Scheider
  (2, 1, 'Dom Cobb'),                   -- Inception, Leo DiCaprio
  (3, 2, 'Jake Sully');                 -- Avatar, Sam Worthington

-- Проверяем, что AFTER-триггер сработает для новой вставки в Movies
-- (создаём новый фильм)
INSERT INTO Movies (Title, ReleaseDate, Genre, DirectorID)
VALUES ('E.T.', '1982-06-11', 'Sci-Fi', 1);

-- Проверяем вставку через представление (INSTEAD OF триггер)
INSERT INTO MoviesActorsView (MovieID, Title, ActorID, ActorName)
VALUES (2, 'Inception', 3, 'Roy Scheider');  -- Добавляем нового актёра в фильм через VIEW

------------------------------
-- 5.2 Проверка функций (SELECT)
------------------------------
-- Скалярная функция: возраст актёра (ActorID=1 -> Leonardo DiCaprio)
SELECT get_actor_age(1) AS leo_age;

-- Подставляемая табличная функция: фильмы режиссёра
SELECT * FROM get_movies_by_director('Steven Spielberg');

-- Многооператорная табличная функция: фильмография актёра
SELECT * FROM get_actor_filmography(1);  -- Filmography Leo DiCaprio

-- Рекурсивная функция (годы релизов):
SELECT * FROM get_years_of_movies();

------------------------------
-- 5.3 Проверка хранимых процедур (CALL)
------------------------------

-- 5.3.1 Добавим нового режиссёра
CALL add_director('Ridley Scott', '1937-11-30', 'UK');

-- 5.3.2 Рекурсивная процедура: вывод всех годов релизов
CALL show_years_of_movies();

-- 5.3.3 Процедура с курсором: вывод всех режиссёров
CALL list_directors();

-- 5.3.4 Процедура доступа к метаданным
CALL show_tables();

------------------------------
-- 5.4 Просмотр результатов логирования (MovieLog)
------------------------------
SELECT * FROM MovieLog;

------------------------------
-- 5.5 Проверка, что новая запись в Play появилась
--     через INSTEAD OF-триггер (MovieID=2, ActorID=3)
------------------------------
SELECT * FROM Play
WHERE MovieID = 2 AND ActorID = 3;

---

Краткое описание:

1. Создаются таблицы: Directors, Movies, Actors, Play, а также MovieLog для логирования.
2. Создаются функции:
   • Скалярная: get_actor_age(actor_id INT)
   • Подставляемая табличная: get_movies_by_director(director_name TEXT)
   • Многооператорная табличная: get_actor_filmography(actor_id INT)
   • Рекурсивная через CTE: get_years_of_movies()
3. Создаются процедуры:
   • add_director(...) — добавляет запись в Directors.
   • show_years_of_movies() — выводит все годы релизов через RAISE NOTICE.
   • list_directors() — выводит всех режиссёров через курсор.
   • show_tables() — выводит названия таблиц (метаданные).
4. Триггеры:
   • AFTER INSERT на Movies → пишет лог в MovieLog.
   • INSTEAD OF INSERT на представление MoviesActorsView → записывает данные в таблицу Play.
5. Тестовые данные вставляются для наглядности, а затем выполняются SELECT-запросы для функций и CALL-запросы для процедур.
6. В конце можно проверить, что лог в MovieLog и новые записи в Play появились в соответствии с триггерами.

Скопируйте этот скрипт и выполните его целиком в среде PostgreSQL (например, psql или другой клиент).

В PostgreSQL при написании многооператорной табличной функции (то есть функции с RETURNS TABLE(...) и телом BEGIN ... END;) нельзя использовать конструкцию вида:

sql
RETURN NEXT (SELECT ...);

по двум причинам:

1. Функция с RETURNS TABLE(...) предполагает, что вы будете «заполнять» выходные переменные (Out-поля), а не возвращать значение напрямую, как в RETURN NEXT (record).
2. С синтаксической точки зрения, в многооператорных (multi-statement) табличных функциях с объявленными OUT-параметрами используется либо:
   • RETURN NEXT; (без параметров), где значения предварительно присваиваются OUT-полям,
   • RETURN QUERY SELECT ...; (целиком запрос),
   • Либо комбинация цикла FOR rec IN SELECT ... LOOP RETURN NEXT; END LOOP;, внутри которой поля OUT (колонки, объявленные в RETURNS TABLE(...)) заполняются.

Ниже приведено два способа, как правильно переписать функцию.

---

1) С использованием RETURN QUERY SELECT ...

Это самый простой способ, если вам не нужен сложный цикл или дополнительная логика внутри:

sql
CREATE OR REPLACE FUNCTION get_actor_filmography(actor_id INT)
  RETURNS TABLE(movie_title VARCHAR(150), role VARCHAR(100))
AS
$$
BEGIN
    -- Просто выполняем SELECT, "отдаём" результат через RETURN QUERY
    RETURN QUERY
    SELECT m.Title AS movie_title,
           p.Role  AS role
      FROM Play p
      JOIN Movies m ON p.MovieID = m.MovieID
     WHERE p.ActorID = actor_id;

    -- RETURN завершает функцию (можно вообще не писать, если это последнее действие)
    RETURN;
END;
$$ LANGUAGE plpgsql;

В этом случае не нужны циклы и дополнительные переменные. Функция вернёт результат запроса как таблицу.

---

2) С использованием цикла FOR rec IN ... LOOP

Если вам нужна дополнительная логика или циклы, можно записать так:

sql
CREATE OR REPLACE FUNCTION get_actor_filmography(actor_id INT)
  RETURNS TABLE(movie_title VARCHAR(150), role VARCHAR(100))
AS
$$
DECLARE
    rec RECORD;  -- или rec может быть Play%ROWTYPE, если нужно
BEGIN
    FOR rec IN 
        SELECT m.Title AS t, p.Role AS r
          FROM Play p
          JOIN Movies m ON p.MovieID = m.MovieID
         WHERE p.ActorID = actor_id
    LOOP
        -- Заполняем "выходные поля" (те, что объявлены в RETURNS TABLE)
        movie_title := rec.t;
        role        := rec.r;
        
        -- Возвращаем очередную строку
        RETURN NEXT;
    END LOOP;
    
    -- Указываем, что возврат закончен
    RETURN;
END;
$$ LANGUAGE plpgsql;

Здесь важна схема:

1. В секции RETURNS TABLE (movie_title ..., role ...) мы фактически объявляем OUT-переменные movie_title и role.
2. Внутри цикла FOR rec IN ... из каждой записи rec мы берём поля и присваиваем их этим переменным.
3. Затем говорим RETURN NEXT; (без параметров!) — тогда PostgreSQL автоматически «складывает» их в результирующую «таблицу», строка за строкой.
4. В конце указываем простое RETURN;, чтобы сообщить, что все строки возвращены.

---

Итог

Чтобы устранить ошибку:

ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters,

нужно либо:

• Использовать RETURN QUERY SELECT ...;,
• Либо в цикле присваивать значения OUT-переменным и делать RETURN NEXT; без параметров.

Это два канонических способа вернуть набор строк в многооператорной табличной функции PostgreSQL.

Ниже приведён единый SQL-скрипт для PostgreSQL, который:

1. Создаёт таблицы согласно вашей схеме.
2. Добавляет необходимые ограничения (NOT NULL, внешние ключи, проверка возраста режиссёров).
3. Создаёт функции (скалярная, табличные, рекурсивная) с учётом правил написания многооператорных табличных функций в PostgreSQL.
4. Создаёт процедуры (включая рекурсивную, с курсором, доступ к метаданным).
5. Создаёт триггеры (AFTER и INSTEAD OF).
6. Вставляет тестовые данные и демонстрирует вызовы функций/процедур.

Обратите внимание на исправленную функцию get_actor_filmography — теперь она не использует RETURN NEXT (SELECT ...), а либо делает RETURN QUERY SELECT ..., либо заполняет выходные поля и делает RETURN NEXT;.

Просто скопируйте и выполните этот скрипт целиком в среде PostgreSQL:

sql
/*******************************************************************
 * 1. СОЗДАНИЕ ТАБЛИЦ
 *******************************************************************/

-- На всякий случай удаляем объекты, если они уже существуют
DROP VIEW IF EXISTS MoviesActorsView CASCADE;
DROP TABLE IF EXISTS MovieLog CASCADE;
DROP TABLE IF EXISTS Play CASCADE;
DROP TABLE IF EXISTS Actors CASCADE;
DROP TABLE IF EXISTS Movies CASCADE;
DROP TABLE IF EXISTS Directors CASCADE;

-- Таблица режиссёров
CREATE TABLE Directors (
    DirectorID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    BirthDate DATE,
    Nationality VARCHAR(50)
);

-- Таблица фильмов
CREATE TABLE Movies (
    MovieID SERIAL PRIMARY KEY,
    Title VARCHAR(150),
    ReleaseDate DATE,
    Genre VARCHAR(50),
    DirectorID INT
);

-- Таблица актёров
CREATE TABLE Actors (
    ActorID SERIAL PRIMARY KEY,
    Name VARCHAR(100),
    BirthDate DATE,
    Nationality VARCHAR(50)
);

-- Таблица связей (многие ко многим) между фильмами и актёрами
CREATE TABLE Play (
    PlayID SERIAL PRIMARY KEY,
    MovieID INT,
    ActorID INT,
    Role VARCHAR(100)
);

/*******************************************************************
 * 2. ДОБАВЛЕНИЕ/ИЗМЕНЕНИЕ ОГРАНИЧЕНИЙ
 *******************************************************************/

-- Требование: имя режиссёра, фильма, актёра не может быть NULL
ALTER TABLE Directors
    ALTER COLUMN Name SET NOT NULL;

ALTER TABLE Movies
    ALTER COLUMN Title SET NOT NULL;

ALTER TABLE Actors
    ALTER COLUMN Name SET NOT NULL;

-- У связки Play поля MovieID и ActorID должны быть NOT NULL
ALTER TABLE Play
    ALTER COLUMN MovieID SET NOT NULL,
    ALTER COLUMN ActorID SET NOT NULL;

-- Внешний ключ на поле DirectorID (Movies -> Directors),
-- При удалении режиссёра – DirectorID у фильма обнуляется (SET NULL),
-- При изменении ID режиссёра – затрагиваемые фильмы тоже обновляются (CASCADE).
ALTER TABLE Movies
    ADD CONSTRAINT fk_director
    FOREIGN KEY (DirectorID)
    REFERENCES Directors(DirectorID)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

-- Ограничения внешних ключей для таблицы Play
-- (MovieID -> Movies, ActorID -> Actors):
-- При удалении фильма/актёра – записи в Play удаляются (CASCADE),
-- При изменении ID фильма/актёра – обновляются соответствующие строки (CASCADE).
ALTER TABLE Play
    ADD CONSTRAINT fk_movie
    FOREIGN KEY (MovieID)
    REFERENCES Movies(MovieID)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

ALTER TABLE Play
    ADD CONSTRAINT fk_actor
    FOREIGN KEY (ActorID)
    REFERENCES Actors(ActorID)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- Ограничение на возраст режиссёров (старше 18 лет).
ALTER TABLE Directors
    ADD CONSTRAINT chk_director_age 
    CHECK (BirthDate <= CURRENT_DATE - INTERVAL '18 years');


/*******************************************************************
 * 3. ДОПОЛНИТЕЛЬНАЯ ТАБЛИЦА ДЛЯ ЛОГИРОВАНИЯ
 *******************************************************************/
CREATE TABLE MovieLog (
    LogID SERIAL PRIMARY KEY,
    MovieID INT,
    Action VARCHAR(50),
    ActionDate TIMESTAMP
);


/*******************************************************************
 * 4. ФУНКЦИИ
 *******************************************************************/

--------------------------------------------------------------
-- 4.1 Скалярная функция: Возвращает возраст актёра по его ID
--------------------------------------------------------------
/*
   Функция get_actor_age(actor_id INT)
   Принимает: ID актёра
   Возвращает: целое число (возраст)
*/
CREATE OR REPLACE FUNCTION get_actor_age(actor_id INT)
  RETURNS INT
AS
$$
DECLARE
    actor_age INT;
BEGIN
    SELECT EXTRACT(YEAR FROM age(BirthDate))::INT
      INTO actor_age
    FROM Actors
    WHERE ActorID = actor_id;

    RETURN actor_age;
END;
$$ LANGUAGE plpgsql;

----------------------------------------------------------------
-- 4.2 Подставляемая (inline) табличная функция:
--     Возвращает фильмы по имени режиссёра
----------------------------------------------------------------
/*
   Функция get_movies_by_director(director_name TEXT)
   Принимает: имя режиссёра
   Возвращает: набор строк (MovieID, Title, ReleaseDate, Genre)
*/
CREATE OR REPLACE FUNCTION get_movies_by_director(director_name TEXT)
  RETURNS TABLE(movie_id INT, title VARCHAR, release_date DATE, genre VARCHAR)
AS
$$
BEGIN
    RETURN QUERY
    SELECT m.MovieID, m.Title, m.ReleaseDate, m.Genre
      FROM Movies m
      JOIN Directors d ON m.DirectorID = d.DirectorID
     WHERE d.Name = director_name;
END;
$$ LANGUAGE plpgsql;

-------------------------------------------------------------------
-- 4.3 Многооператорная табличная функция (multi-statement):
--     Возвращает фильмографию актёра: название фильма + роль
-------------------------------------------------------------------
/*
   Функция get_actor_filmography(actor_id INT)
   Принимает: ID актёра
   Возвращает: таблицу (movie_title, role)

   Важно: используем RETURN NEXT без параметров, 
   а значения присваиваем OUT-полю, объявленному в RETURNS TABLE.
*/
CREATE OR REPLACE FUNCTION get_actor_filmography(actor_id INT)
  RETURNS TABLE(movie_title VARCHAR(150), role VARCHAR(100))
AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT m.Title AS t, p.Role AS r
          FROM Play p
          JOIN Movies m ON p.MovieID = m.MovieID
         WHERE p.ActorID = actor_id
    LOOP
        movie_title := rec.t;  -- Заполняем OUT-поле
        role        := rec.r;  -- Заполняем OUT-поле
        RETURN NEXT;           -- Добавляем строчку в результат
    END LOOP;
    RETURN;  -- Завершение
END;
$$ LANGUAGE plpgsql;

------------------------------------------------------
-- 4.4 Рекурсивная функция (с рекурсивным CTE):
--     Возвращает все годы релизов (от мин. года до макс.)
------------------------------------------------------
/*
   Функция get_years_of_movies()
   Принимает: ничего
   Возвращает: набор лет (SETOF INT),
               полученный рекурсивным CTE на основе дат релизов.
*/
CREATE OR REPLACE FUNCTION get_years_of_movies()
  RETURNS SETOF INT
AS
$$
BEGIN
    RETURN QUERY
    WITH RECURSIVE years(y) AS (
         SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
           FROM Movies
         UNION ALL
         SELECT y + 1
           FROM years
          WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
    )
    SELECT y FROM years;
END;
$$ LANGUAGE plpgsql;


/*******************************************************************
 * 5. ХРАНИМЫЕ ПРОЦЕДУРЫ
 *******************************************************************/

-------------------------------------------------------
-- 5.1 Процедура add_director (с параметрами):
--     Добавляет нового режиссёра в таблицу Directors.
-------------------------------------------------------
/*
   Процедура add_director(_name, _birthdate, _nationality)
   Параметры:
     _name - имя режиссёра,
     _birthdate - дата рождения,
     _nationality - национальность
*/
CREATE OR REPLACE PROCEDURE add_director(
    _name VARCHAR,
    _birthdate DATE,
    _nationality VARCHAR
)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO Directors (Name, BirthDate, Nationality)
    VALUES (_name, _birthdate, _nationality);
END;
$$;

-------------------------------------------------------------------
-- 5.2 Рекурсивная процедура show_years_of_movies (через CTE):
--     Выводит (RAISE NOTICE) все годы релизов фильмов
-------------------------------------------------------------------
/*
   Процедура show_years_of_movies()
   Параметры: нет
   Действие:
     С помощью рекурсивного CTE находит все годы, в которые выходили фильмы,
     и выводит их через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE show_years_of_movies()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    RAISE NOTICE 'Годы выхода фильмов:';

    FOR rec IN
        WITH RECURSIVE years(y) AS (
             SELECT EXTRACT(YEAR FROM MIN(ReleaseDate))::INT
               FROM Movies
             UNION ALL
             SELECT y + 1
               FROM years
              WHERE y < (SELECT EXTRACT(YEAR FROM MAX(ReleaseDate))::INT FROM Movies)
        )
        SELECT y FROM years
    LOOP
        RAISE NOTICE '%', rec.y;
    END LOOP;
END;
$$;

-------------------------------------------------------
-- 5.3 Процедура list_directors (с курсором):
--     Выводит (RAISE NOTICE) всех режиссёров
-------------------------------------------------------
/*
   Процедура list_directors()
   Параметры: нет
   Действие:
     Использует курсор для прохода по всем режиссёрам в таблице Directors.
     Каждая запись выводится через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE list_directors()
LANGUAGE plpgsql
AS
$$
DECLARE
    cur_directors CURSOR FOR
        SELECT DirectorID, Name, Nationality FROM Directors;
    rec RECORD;
BEGIN
    OPEN cur_directors;
    LOOP
        FETCH cur_directors INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Director -> ID:%, Name:%, Nationality:%',
                     rec.directorid, rec.name, rec.nationality;
    END LOOP;
    CLOSE cur_directors;
END;
$$;

------------------------------------------------------------------
-- 5.4 Процедура show_tables (доступ к метаданным):
--     Показывает таблицы из схемы public (RAISE NOTICE)
------------------------------------------------------------------
/*
   Процедура show_tables()
   Параметры: нет
   Действие:
     Считывает из information_schema.tables названия всех таблиц
     в схеме public, выводит через RAISE NOTICE.
*/
CREATE OR REPLACE PROCEDURE show_tables()
LANGUAGE plpgsql
AS
$$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT table_name
          FROM information_schema.tables
         WHERE table_schema = 'public'
    LOOP
        RAISE NOTICE 'Таблица: %', rec.table_name;
    END LOOP;
END;
$$;


/*******************************************************************
 * 6. ТРИГГЕРЫ
 *******************************************************************/

--------------------------------------------
-- 6.1 AFTER-триггер на таблицу Movies
--------------------------------------------
/*
   Логика:
   - После вставки фильма (AFTER INSERT)
   - Записываем в MovieLog информацию об операции
*/
CREATE OR REPLACE FUNCTION after_insert_movies()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO MovieLog (MovieID, Action, ActionDate)
    VALUES (NEW.MovieID, 'INSERT', NOW());
    RETURN NEW; -- возвращаем NEW, чтобы сохранить вставленную запись
END;
$$;

CREATE TRIGGER trg_after_insert_movies
AFTER INSERT ON Movies
FOR EACH ROW
EXECUTE PROCEDURE after_insert_movies();

--------------------------------------------
-- 6.2 INSTEAD OF-триггер на представление
--------------------------------------------
/*
   1) Создаём представление MoviesActorsView, 
      которое объединяет фильмы и актёров через таблицу Play.
   2) Создаём INSTEAD OF-триггер, чтобы при вставке в это представление
      фактически делать INSERT в таблицу Play.
*/
CREATE OR REPLACE VIEW MoviesActorsView AS
SELECT m.MovieID,
       m.Title,
       a.ActorID,
       a.Name AS ActorName
  FROM Movies m
  JOIN Play p ON m.MovieID = p.MovieID
  JOIN Actors a ON p.ActorID = a.ActorID;

CREATE OR REPLACE FUNCTION insert_into_moviesactorsview()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
    -- При вставке в представление "MoviesActorsView"
    -- записываем фактические данные в таблицу Play
    INSERT INTO Play(MovieID, ActorID, Role)
    VALUES (NEW.MovieID, NEW.ActorID, 'Unknown');
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_instead_of_insert_moviesactorsview
INSTEAD OF INSERT ON MoviesActorsView
FOR EACH ROW
EXECUTE PROCEDURE insert_into_moviesactorsview();


/*******************************************************************
 * 7. ТЕСТОВЫЕ ДАННЫЕ И ВЫЗОВЫ (ДЕМОНСТРАЦИЯ)
 *******************************************************************/

------------------------------
-- 7.1 Вставка тестовых данных
------------------------------
-- Директора (учтём ограничение: они должны быть старше 18 лет)
INSERT INTO Directors (Name, BirthDate, Nationality)
VALUES
  ('Steven Spielberg', '1946-12-18', 'USA'),
  ('Christopher Nolan', '1970-07-30', 'UK'),
  ('James Cameron', '1954-08-16', 'Canada');

-- Фильмы (DirectorID ссылается на соответствующих режиссёров)
INSERT INTO Movies (Title, ReleaseDate, Genre, DirectorID)
VALUES
  ('Jaws',         '1975-06-20', 'Thriller', 1),  -- Spielberg
  ('Inception',    '2010-07-16', 'Sci-Fi',   2),  -- Nolan
  ('Avatar',       '2009-12-18', 'Sci-Fi',   3);  -- Cameron

-- Актёры
INSERT INTO Actors (Name, BirthDate, Nationality)
VALUES
  ('Leonardo DiCaprio', '1974-11-11', 'USA'),
  ('Sam Worthington',   '1976-08-02', 'Australia'),
  ('Roy Scheider',      '1932-11-10', 'USA');

-- Привязка актёров к фильмам (Play)
INSERT INTO Play (MovieID, ActorID, Role)
VALUES
  (1, 3, 'Police Chief Martin Brody'),  -- Jaws, Roy Scheider
  (2, 1, 'Dom Cobb'),                   -- Inception, Leo DiCaprio
  (3, 2, 'Jake Sully');                 -- Avatar, Sam Worthington

-- Проверяем, что AFTER-триггер сработает для новой вставки в Movies
-- (создаём новый фильм)
INSERT INTO Movies (Title, ReleaseDate, Genre, DirectorID)
VALUES ('E.T.', '1982-06-11', 'Sci-Fi', 1);

-- Проверяем вставку через представление (INSTEAD OF триггер)
-- Попытаемся в фильм "Inception" (MovieID=2) добавить актёра Roy Scheider (ActorID=3) через VIEW:
INSERT INTO MoviesActorsView (MovieID, Title, ActorID, ActorName)
VALUES (2, 'Inception', 3, 'Roy Scheider');

------------------------------
-- 7.2 Проверка функций (SELECT)
------------------------------
-- 1) Скалярная функция: возраст актёра (ActorID=1 -> Leonardo DiCaprio)
SELECT get_actor_age(1) AS leo_age;

-- 2) Подставляемая табличная функция: фильмы режиссёра
SELECT * FROM get_movies_by_director('Steven Spielberg');

-- 3) Многооператорная табличная функция: фильмография актёра (Leo = ActorID=1)
SELECT * FROM get_actor_filmography(1);

-- 4) Рекурсивная функция (годы релизов)
SELECT * FROM get_years_of_movies();

------------------------------
-- 7.3 Проверка хранимых процедур (CALL)
------------------------------

-- 7.3.1 Добавим нового режиссёра
CALL add_director('Ridley Scott', '1937-11-30', 'UK');

-- 7.3.2 Рекурсивная процедура: вывод всех годов релизов
CALL show_years_of_movies();

-- 7.3.3 Процедура с курсором: вывод всех режиссёров
CALL list_directors();

-- 7.3.4 Процедура доступа к метаданным
CALL show_tables();

------------------------------
-- 7.4 Просмотр результатов логирования (MovieLog)
------------------------------
SELECT * FROM MovieLog;

------------------------------
-- 7.5 Проверка, что новая запись в Play появилась
--     через INSTEAD OF-триггер (MovieID=2, ActorID=3)
------------------------------
SELECT * FROM Play
WHERE MovieID = 2 AND ActorID = 3;

Ключевые моменты:

1. NOT NULL добавлено к Directors.Name, Movies.Title, Actors.Name, а также к Play.MovieID, Play.ActorID.
2. Внешние ключи:
   • Movies.DirectorID → Directors(DirectorID) с ON DELETE SET NULL и ON UPDATE CASCADE.
   • Play.MovieID → Movies(MovieID) с ON DELETE CASCADE и ON UPDATE CASCADE.
   • Play.ActorID → Actors(ActorID) с ON DELETE CASCADE и ON UPDATE CASCADE.
3. Проверка возраста режиссёра: chk_director_age (должен быть старше 18 лет).
4. Исправленная многооператорная табличная функция get_actor_filmography: используется либо RETURN QUERY, либо мы задаём OUT-поля и используем RETURN NEXT;.
5. Все триггеры и все процедуры остались с прежней логикой.
6. В конце — вставка тестовых данных и демонстрация вызовов (SELECT и CALL).

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