Вставка записей в несколько таблиц с использованием результатов первой вставки

Я довольно плохо разбираюсь в PostgreSQL и имею некоторый опыт работы с SQL Server. Этот вопрос о практике, потому что я много чего читал.

Я хочу вставить запись (пользователя), а затем взять этот userID и вставить еще пару записей в еще пару таблиц.

Традиционно я бы использовал для этого что-то вроде хранимой процедуры или триггера. Я видел, что в postgreSQL существуют функции (без хранимой процедуры?), но я также видел, что они не являются транзакционными.

Я надеюсь, что смогу найти транзакционное решение с помощью триггера или хранимой процедуры.

Внешние ключи в настоящее время не добавляются (будут существовать в какой-то момент, просто не на данном этапе разработки):

Пример (передайте $name, $password, $address, $group_id, $interestsJson):

insert into user (name, pwd, address) 
values ($name, $password, $address)

insert into user_group (user_id, group_id) 
values (NEW_USER_ID_ABOVE, $group_id)

insert into user_interests (user_id, interests) 
value (NEW_USER_ID_ABOVE, $interestsJson)

postgresql
person StaticMaine    schedule 05.06.2018    source источник
comment
О внешних ключах. Мы находимся на стадии тестирования наших моделей, поэтому я их еще не добавлял. Но не пойдет добавление данных без них.   -  person StaticMaine    schedule 05.06.2018

Ответы (1)


arrow_upward
0
arrow_downward

Функции являются транзакционными — они участвуют в транзакции вызывающего объекта. Вы можете поместить все три вставки в одну функцию и вызвать ее. Однако вызывающий должен зафиксировать транзакцию.

create or replace function do_inserts(p_name text, p_password text, p_address text, p_group_id integer, p_interest jsonb)
as
$$
declare 
  l_user_id integer;
begin 
   insert into "user" (name, pwd, address) 
   values (p_name, p_password, p_address)
   returning id into l_user_id;

   insert into "group" (user_id, group_id) 
   values (l_user_id, p_group_id);

   insert into user_interests (user_id, interests)
   values (l_user_id, p_interests);
end;
$$
language plpgsql;

Затем вы можете сделать:

begin;
select do_inserts(...);
commit;

В качестве альтернативы вы можете сделать все в одном выражении:

with new_user as (
   insert into "user" (name, pwd, address) 
   values (..., ..., ....)
   returning
), new_group as (
   insert into "group" (user_id, group_id) 
   values ((select id from new_user), ...);
)
insert into user_interests (user_id, interests)
values ((select id from new_user), ...);

С Postgres 11 (который будет выпущен в четвертом квартале 2018 г.) вы сможете использовать хранимые процедуры, где вы можете использовать commit

person a_horse_with_no_name    schedule 05.06.2018