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

Crafting SQL queries

I’m creating SQL queries for a PostgreSQL database schema, including scalar functions and stored procedures with explanations. This involves detailing functions like computing an actor's age from an actor ID.

Crafting SQL examples

I’m detailing SQL examples for scalar and table-valued functions in PostgreSQL, including a function for calculating an actor's age and a function for retrieving movies by a director's name.

Generating a filmography

I’m creating an SQL function to return a detailed filmography by actor ID, including movie titles and roles, using PostgreSQL plpgsql. Cursor-based iteration is employed to gather data.

Mapping out SQL strategies

I'm considering SQL functions using recursive CTEs to generate date ranges or filmographies. Exploring connections between directors and nationalities to enrich data retrieval.

Crafting SQL functions

I’m piecing together SQL functions with recursive CTEs to extract movie release years and creating a stored procedure to insert new director records into the Directors table using plpgsql.

Crafting SQL procedures

I’m designing SQL procedures to facilitate the collection and display of movie data from the earliest to the latest years, incorporating recursive CTEs for comprehensive range extraction.

Constructing SQL procedures

I’m detailing SQL procedures to display movie years and a cursor-based example to list directors by iterating and printing their details. This approach ensures efficient data handling and presentation.

Setting up logging mechanisms

I’m detailing an SQL setup to log movie inserts into a MovieLog table with an AFTER INSERT trigger, ensuring all actions are captured with precise timestamps.

Crafting complex SQL

I’m exploring SQL views and INSTEAD OF triggers to manage insert operations into a MoviesActorsView, ensuring seamless movie and actor data handling within the Play table.

Taking note

I’m ensuring SQL examples are provided in separate code blocks, with each block featuring a concise Russian comment that explains its functionality clearly. This approach enhances readability and comprehension.

Thought about SQL запросы и функции for 24 seconds

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

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

-- Функция, принимающая 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) табличная функция

-- Функция, возвращающая фильмы конкретного режиссёра по имени.
-- Возвращаемые столбцы: 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)

-- Функция, возвращающая фильмографию актёра (название фильма и роль),
-- используя цикл 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)

-- Функция, формирующая список всех годов, в которые выходили фильмы, 
-- от минимального года релиза до максимального, используя рекурсивный 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. Хранимая процедура (без параметров или с параметрами)

-- Процедура добавления нового режиссёра (с параметрами).
-- Добавляет запись в таблицу 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)

-- Процедура, которая выводит (через 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. Хранимая процедура с курсором

-- Процедура, которая перечисляет всех режиссёров, используя курсор.
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. Хранимая процедура доступа к метаданным

-- Процедура, которая просматривает информацию о таблицах
-- из схемы 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

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

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

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

-- Функция-триггер, которая срабатывает после вставки новой записи в 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-триггера необходимо создать представление, в которое можно будет «вставлять» данные. Допустим, мы хотим иметь представление для удобного добавления связи фильм-актёр:

-- Создаём представление, объединяющее фильмы и актёров.
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 для вставки:

-- Функция-триггер, которая при попытке вставки новой строки 
-- в представление 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().

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

Converting SQL script to Russian

I’m gathering info on converting an SQL script into Russian, focusing on combining schema creation, functions, triggers, and testing data all into one file for efficient execution.

Setting up and testing databases

I’m working on creating tables for Directors, Movies, Actors, and Play, then the log table. Next, I’ll set up triggers and provide example calls for testing functions and procedures.

Thought about SQL functions and procedures for 4 seconds

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

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

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

/*******************************************************************
 * 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 или другой клиент).

Crafting a function

I’m working through a multi-statement table function in Postgres. The aim is to return a table with columns for movie title and role, utilizing a loop to iterate over records and another select statement to fetch related movie titles.

Mapping the process

I’m noting the use of OUT parameters or local variables to fetch movie titles and roles for each play record, with a RETURN NEXT statement to output each row.

Refining the function

I’m detailing the use of RETURN QUERY SELECT ... to retrieve movie titles and roles for each play record, and also refining the pattern with FOR movie_title, role in ... within a loop for multi-statement table functions.

Presenting options

I explore two approaches: using RETURN QUERY to directly retrieve data, or a multi-statement loop with RETURN NEXT to iterate over records and return each row.

Thought about get_actor_filmography function error for 7 seconds

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

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 ...

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

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

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

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.

Crafting the script

I’m piecing together a script to update table structures in a database, ensuring constraints for NOT NULL, adding foreign key constraints with specified behaviors, and including new definitions for functions and triggers.

Consolidating scripts

I’m merging constraints and triggers into a single script, using ALTER TABLE statements for simplicity after initial creation, emphasizing correct references and multi-statement table function adjustments.

Constructing and refining

I’m setting up tables and adding constraints. Progressing through, I’ll refine the get_actor_filmography function with RETURN QUERY SELECT ... for precise results.

Refining the function

I’m detailing the get_actor_filmography function with a multi-step approach, ensuring it iterates through actor roles and movies, and updating the script to incorporate all specified constraints reliably.

Ensuring data consistency

I’m thinking through how deleting a director updates Movies with DirectorID set to null, all while upholding the integrity of the BirthDate constraint for those over 18.

Thought for 8 seconds

Ниже приведён единый 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:

/*******************************************************************
 * 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).

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