#php #sql #postgresql #sql-insert #common-table-expression
#php #sql #postgresql #sql-вставка #common-table-expression
Вопрос:
Я пишу запрос в 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)
);
Ответ №1:
Когда ничего не вставлено, 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
Комментарии:
1. Спасибо! Я немного смущен первым абзацем об уникальных ключах, ваш пример точно такой, как таблицы, или я что-то упускаю? У Person есть Person_ID, у персонала есть Personnel_ID, а затем filmpeople объединяет персонал с медиа-ключом.
2. @SanjinRedzepagic: я имею в виду, что у вас должны быть уникальные ограничения, как описано в первом абзаце (возможно, это уже так). Например, в таблице
filmpeople
этот кортеж столбцов должен быть определен какunique
:(personnel_id, media_id)
3. Прошу прощения за то, что я немного глуп, я добавил, как создаются таблицы на данный момент в исходном сообщении. Не могли бы вы взглянуть на это и сказать мне, какие изменения, по вашему мнению, необходимо внести?
4. Что предлагает @GMB, так это то, что вы добавляете уникальный бизнес-ключ к каждой таблице, затем используете этот ключ в каждом последующем cte или основном запросе, для таблицы Person вам нужно «Изменить таблицу добавить ограничение person_bk уникальное имя»; Аналогичная инструкция alter table применяется к другим таблицам.