Блог

Я пишу запрос в PostgreSQL, который я использую в своем php-коде, где пользователь должен иметь возможность добавлять участников в список участников, введя имя участников. Я использую модифицирующий CTE для этого, потому что ему необходимо обновить три таблицы в базе данных:

• Person(Person_ID:Serial, Name: Varchar, … другая не относящаяся к делу информация, можно оставить null)
• Персонал (Personnel_ID:Serial, Роль: Varchar, Person_ID:Int (FK)) FilmPeople(Personnel_ID (FK), Media_ID (FK))
• Таким образом, актер как person находится в таблице person, затем подключается к роли в personal, а затем роль подключается к media_ID .

Запрос до сих пор:

 WITH person_cte AS (
     INSERT INTO Person(Name)
     SELECT :actorname
     WHERE NOT EXISTS (
         SELECT 1 FROM person WHERE name = :actorname2)
     RETURNING person_id
     ),
     personnel_cte AS (
     INSERT INTO Personnel(role, person_id)
     SELECT 'Actor', person_id FROM person_cte
     RETURNING personnel_id
     )
     INSERT INTO FilmPeople(Personnel_ID, Media_ID)
     SELECT personnel_id, :id from personnel_cte¨
 

Итак, проблема, как вы, ребята, вероятно, видите, заключается в том, что мой запрос пока работает только в том случае, если субъект вообще не существует в базе данных. Сбой, если он находит субъекта в таблице Person. На самом деле я хочу, чтобы, если актер существует лично, ничего не вставлял, а возвращал уже существующий person_id следующему cte. И затем то же самое, если персонал (роль) уже существует для этого актера, затем перейдите к personnel_id и добавьте актера только в последнюю таблицу, чтобы связать его / ее с фильмом. И даже здесь, если оно уже существует, ничего не делайте.

РЕДАКТИРОВАТЬ: так создаются таблицы на данный момент:

 CREATE TABLE Person(
Person_ID serial,
Name varchar(255),
DateOfBirth date,
Gender varchar(45),
CONSTRAINT PK_Person PRIMARY KEY (Person_ID)
);

CREATE TABLE Personnel(
Personnel_ID serial,
Role varchar(255),
Person_ID bigint,
CONSTRAINT PK_Personnel PRIMARY KEY (Personnel_ID),
CONSTRAINT FK_PersonPersonnel FOREIGN KEY (Person_ID)
REFERENCES Person(Person_ID)
);

CREATE TABLE FilmPeople(
Personnel_ID bigint,
Media_ID int,
CONSTRAINT PK_FilmPeople PRIMARY KEY (Personnel_ID, Media_ID),
CONSTRAINT FK_PersonnelFilmPeople FOREIGN KEY (Personnel_ID)
REFERENCES Personnel(Personnel_ID),
CONSTRAINT FK_MediaFilmPeople FOREIGN KEY (Media_ID)
REFERENCES Media(Media_ID)
);
 

Когда ничего не вставлено, returning предложение возвращается пустым — так что это не тот подход, который вам нужен здесь. Вместо этого я бы рекомендовал объединить таблицы. Чтобы это работало правильно, я бы настоятельно рекомендовал иметь уникальные ключи в каждой таблице, например:

 person(name)
personnel(role,person_id)
filmpeople(personnel_id, media_id)
 

Затем вы можете использовать on conflict :

 with 
    person_cte as (
        insert into person(name)
        values (:actorname)
        on conflict (name) do nothing
    ),
    personnel_cte as (
        insert into personnel(role, person_id)
        select 'actor', p.person_id 
        from person p
        where p.name = :actorname
        on conflict (role, person_id) do nothing
     )
insert into filmpeople(personnel_id, media_id)
select personnel_id, :id 
from pl.personnel pl
inner person p on p.person_id = pl.person_id
where p.name = :actorname and pl.role = 'actor'
on conflict (personnel_id, media_id) do nothing