modyfikowanie dwóch tabel jednocześnie

Cześć,

mam dwie tabele, jedna zawiera informacje dotyczące inwestycji (nr_inwestycji, inwestor, adres, data_realizacji itd), druga zawiera dane dot. sieci wodociągowej (nr_rury, nr_inwestycji, średnica, długość, rzędna_początku itd). W momencie kiedy każda z tabel edytowana jest oddzielnie, nie ma problemu. Jednak czasem wygodniej byłoby wprowadzić wszystkie dane za jednym zamachem z poziomu aplikacji gisowej (QGIS).

i teraz pytanie/problem. Chciałem stworzyć nie zmaterializowany widok, który będzie zawierał obie tabele i chciałbym dodać możliwość edytowania poprzez widok tabel z inwestycjami i z sieciami.

dodanie jednej serii danych polegałoby na :

1. zablokowaniu obu tabel na czas wykonywania tranzakcji.
2. dodaniu nowego wiersza inwestycje
3. przesunięcie wskaźnika dodania ostatniego wiersza dla sekwencji, które mogą dodawać wiesze inwestycji nie poprzez ten widok, tylko normalnie przez dodawanie wierszy
4. dodanie wiersza dla tabeli z sieciami (w tym dodanie w kolumnie inwestycje nr wiersza, dodanego jako "inwestycja" w ramach tej tranzakcji)
5. Koniec tranzakcji.

Z SQL wielkim orłem nie jestem, wiem że coś gdzieś dzwoni, ale nie wiem w którym kościele. Wesprzecie mnie pomysłami jak sobie z tym poradzić, ewentualnie jakimiś linkami?? Googluje mi się ciężko, bo nawet do końca nie wiem czego szukam. Liczę na jakąkolwiek podpowiedź.

Pozdrawiam
Piotrek

1. Nie trzeba robić tego w transakcji. Można to zrobić za pomocą jednego zapytania, które jest transakcją.
2. Można to zrobić za pomocą WITH i poszczególnych zagnieżdżonych zapytań. Np:

/*
create table inwestycje(
id serial not null primary key,
kod_inwestycji varchar(10) not null unique,
opis text);

create table sieci(
id serial not null primary key,
kod_sieci varchar(10) not null unique,
opis text);

create table sieci_inwestycje
(
id serial not null primary key,
id_sieci int references sieci(id),
id_inwestycji int references inwestycje(id),
unique (id_sieci, id_inwestycji)
);

insert into sieci(kod_sieci, opis) values('siec1', 'To jest siec inwestycji nr 1');
insert into sieci(kod_sieci, opis) values('siec2', 'To jest siec #2');

insert into inwestycje(kod_inwestycji, opis) values('inw1', 'To jest inwestycja nr 1');
insert into inwestycje(kod_inwestycji, opis) values('inw2', 'To jest inwestycja #2');

insert into sieci_inwestycje(id_sieci, id_inwestycji) values(1,2), (2,1), (2,2);
*/

with 
i as
(
insert into inwestycje(kod_inwestycji, opis) 
values('nowa inw', 'Inwestycja z WITH') 
returning id as id_inwestycji 
)
,
s as
(
insert into sieci(kod_sieci, opis) 
values('nowa siec', 'To jest siec z WITH') 
returning id as id_sieci
)
,
si as
(
insert into sieci_inwestycje(id_sieci, id_inwestycji) 
select s.id_sieci, i.id_inwestycji
from i,s 
returning id
)
select x.id, y.opis, z.opis from sieci_inwestycje x join sieci y on x.id_sieci=y.id join inwestycje z on z.id=x.id_inwestycji 

dzięki za pomoc :) tylko wydaje mi się, ze to mi nie pomoże. Jeżeli jestem w błędzie to proszę wyprowadzić mnie z niego :)

Takie rozwiązanie byłoby dobre w sytuacji, kiedy panowałbym nad sqlem i insertami. Ale QGIS zrobi tempe "insert into()" i tyle go obchodzi. ;) Tzn mógłbym opracować własne zapytanie sql tworząc w pythonie własną wtyczkę do tego i własny formularz, ale niesie to kolejne problemy za sobą tak więc tego chciałbym uniknąć.

Dlatego chciałem stworzyć widok- dzięki czemu z poziomu użytkownika byłaby to zwykła tabela do której wklepuje dane (nie trzeba nic doinstalować, konfigurować itd). I dlatego też nad poprawnością chciałbym zapanować na poziomie bazy danych. Musiałbym to zrobić pewnie przy pomocy sekwencji. Da się tak?? :D Czy jestem w głębokim błędzie i niewiedzy? :)

Nie wiem, czy uda ci się dodać do dwóch tabel z poziomu VIEW. Sądzę, że nie, ale nie sprawdzałem.
Ale możesz to równie dobrze wykonać za pomocą triggerów :)

@Marcin.Miga: A co jest złego w transakcjach? Lepiej mieć transakcje i logikę widoczną na tacy niż triggera i szukać za jakiś czas co się potem gdzieś stało.

Postgre lubi mnie zaskakiwać, da się to zrobić albo za pomocą RULE, albo INSTEAD OF Trigger (od wersji 9.1):

https://vibhorkumar.blog/2011/10/28/instead-of-trigger/

Panczo napisał(a):

Postgre lubi mnie zaskakiwać, da się to zrobić albo za pomocą RULE, albo INSTEAD OF Trigger (od wersji 9.1):

https://vibhorkumar.blog/2011/10/28/instead-of-trigger/

no właśnie staram się to jakoś sensownie napisać korzystając z INSTEAD OF ;) na chwilę obecną słabo mi to idzie :( ale walczę :D

drop  table siec cascade;
drop table inw cascade;

create table inw
(
id_inw serial primary key,
dane_inw text
);

create table siec
(
id_siec serial primary key,
inw integer,
dane_siec text,
foreign key(inw) references inw(id_inw)
);

insert into inw(dane_inw) values ('spoko dane inw 1');
insert into inw(dane_inw) values ('spoko dane inw 2');

insert into siec(inw, dane_siec) values (1,'spoko dane siec 1');
insert into siec(inw, dane_siec) values (2,'spoko dane siec 2');

create view widok as
select a.*, b.* from inw a, siec b 
where a.id_inw = b.inw;


CREATE RULE widok_vw_INSERT AS ON INSERT TO widok DO INSTEAD (
       INSERT INTO inw(dane_inw) VALUES(NEW.dane_inw);
       INSERT INTO  siec(dane_siec, inw) VALUES(NEW.dane_siec, currval('inw_id_inw_seq'))
      );

insert into inw(dane_inw) values('f2');
insert into inw(dane_inw) values('f3');
insert into inw(dane_inw) values('f4');
insert into widok values(4,'dddd',3,3,'gg');
insert into widok values(4,'sss',3,3,'dddddd');
insert into widok values(4,'aas',3,3,'a');

i niby wszystko działa, ale qgis ma focha:

Nie można zatwierdzić zmian dla warstwy widok

Błędy: BŁĄD: nie dodano 1 obiektu.

Błędy źródła danych:
Błąd PostGIS podczas dodawania obiektów: BŁĄD: nie można wykonać INSERT RETURNING na relacji "widok"
HINT: Potrzebujesz bezwarunkowej reguły ON INSERT DO INSTEAD z klauzulą RETURNING

No to za dokumentacją: https://www.postgresql.org/docs/9.0/static/sql-createrule.html

If you want to support INSERT RETURNING and so on, then be sure to put a suitable RETURNING clause into each of these rules.

Przykład:

https://www.postgresql.org/message-id/21167.1243881352%40sss.pgh.pa.us

@mr_jaro: W transakcjach nie ma nic złego. Są dobre. Tylko, że nie ma sensu ich używać tam, gdzie nie są potrzebne. Np. do jednego INSERT, do każdego SELECT itp...
Przewaga triggera nad transakcją jest taka, że zadziała zawsze - niezależnie skąd dodajemy/modyfikujemy/usuwamy dane. Poza tym @Xchmielu1986 chciał to robić za pomocą sekwencji :)

i tak skończyło się triggerem :) ale gra, buczy i śmiga. Może dla potomności się przyda (wrzucam łącznie z testowymi bazami danych).

dane można wprowadzać ręcznie sqlem ale i przez QGIS.

drop view widok;
drop  table siec cascade;
drop table inw cascade;
drop function MyView_on_insert();

create table inw
(
id_inw serial primary key,
dane_inw text
);

create table siec
(
id_siec serial primary key,
inw integer,
dane_siec text,
foreign key(inw) references inw(id_inw)
);

insert into inw(dane_inw) values ('spoko dane inw 1');
insert into inw(dane_inw) values ('spoko dane inw 2');

insert into siec(inw, dane_siec) values (1,'spoko dane siec 1');
insert into siec(inw, dane_siec) values (2,'spoko dane siec 2');

create view widok as
select a.*, b.* from inw a, siec b 
where a.id_inw = b.inw;

CREATE FUNCTION MyView_on_insert() RETURNS trigger AS $$
DECLARE
  id integer;
BEGIN
  INSERT INTO inw(dane_inw) VALUES(NEW.dane_inw);
  id = currval('inw_id_inw_seq');
  INSERT INTO  siec(dane_siec, inw) VALUES(NEW.dane_siec, id);
  RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER MyView_on_insert_trig INSTEAD OF INSERT ON widok
  FOR EACH ROW EXECUTE PROCEDURE MyView_on_insert();


insert into inw(dane_inw) values('f2');
insert into inw(dane_inw) values('f3');
insert into inw(dane_inw) values('f4');
insert into widok values(4,'dddd',3,3,'gg');
insert into widok values(4,'sss',3,3,'dddddd');
insert into widok values(4,'aas',3,3,'a');

Dziękuję wszystkim za pomoc.

Pozdrawiam!