Czy mogę użyć wartości zwrotnej INSERT… RETURNING w innym INSERT?


86

Czy coś takiego jest możliwe?

INSERT INTO Table2 (val)
VALUES ((INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id));

jak użycie wartości zwracanej jako wartości do wstawienia wiersza w drugiej tabeli z odniesieniem do pierwszej tabeli?

Odpowiedzi:


103

Możesz to zrobić, zaczynając od Postgres 9.1:

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val)
SELECT id
FROM rows

W międzyczasie, jeśli interesuje Cię tylko identyfikator, możesz to zrobić za pomocą wyzwalacza:

create function t1_ins_into_t2()
  returns trigger
as $$
begin
  insert into table2 (val) values (new.id);
  return new;
end;
$$ language plpgsql;

create trigger t1_ins_into_t2
  after insert on table1
for each row
execute procedure t1_ins_into_t2();

1
Jak wstawić wartości obok zwracanego identyfikatora? na przykład: INSERT INTO TABLE2 (wart1, wart2, wart3) (1, 2, SELECT id FROM wierszy)
Mahmoud Hanafy

@MahmoudHanafy: wymianie rowsz (some_query returning ...)pracy może w dzisiejszych czasach (nie próbowałem).
Denis de Bernardy

2
@MahmoudHanafy: Aby wstawić wartości obok id powracającego można zrobić coś takiego: INSERT INTO Tabela 2 (val1, wart2, val3) SELECT id, 1, 2 od wierszy
Bhindi

przegłosowano! Czy to atomowe znaczenie, jeśli pierwsze wstawienie powiedzie się, a drugie nie, co się wtedy stanie?
PirateApp

2
@PirateApp Właśnie przetestowane! v12.4. Pierwsza INSERT jest rzeczywiście wycofywana, jeśli druga zawiedzie, ale seria / autoinkrementacja pierwszej INSERT jest pomijana
Madacol


13

Zgodnie z odpowiedzią udzieloną przez Denisa de Bernardy.

Jeśli chcesz, aby identyfikator został również zwrócony później i chcesz wstawić więcej rzeczy do Tabeli2:

with rows as (
INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id
)
INSERT INTO Table2 (val, val2, val3)
SELECT id, 'val2value', 'val3value'
FROM rows
RETURNING val

10
DO $$
DECLARE tableId integer;
BEGIN
  INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id INTO tableId;
  INSERT INTO Table2 (val) VALUES (tableId);
END $$;

Testowane z psql (10.3, serwer 9.6.8)


8

Możesz użyć lastval()funkcji:

Zwracana wartość ostatnio uzyskana nextvaldla dowolnej sekwencji

Więc coś takiego:

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (lastval());

Będzie to działać dobrze, o ile nikt nie wywoła nextval()żadnej innej sekwencji (w bieżącej sesji) między Twoimi INSERTami.

Jak Denis zauważył poniżej, a ja ostrzegałem powyżej, użycie lastval()może nextval()spowodować kłopoty, jeśli uzyskasz dostęp do innej sekwencji, używając między swoimi INSERTami. Mogłoby się to zdarzyć, gdyby istniał wyzwalacz INSERT na Table1tym, który ręcznie wywołał nextval()sekwencję, lub, co bardziej prawdopodobne, wykonał INSERT na tabeli z kluczem podstawowym SERIALlubBIGSERIAL . Jeśli chcesz być naprawdę paranoikiem (dobrze, w końcu oni naprawdę są tobą, aby cię dopaść), możesz użyć, currval()ale musisz znać nazwę odpowiedniej sekwencji:

INSERT INTO Table1 (name) VALUES ('a_title');
INSERT INTO Table2 (val)  VALUES (currval('Table1_id_seq'::regclass));

Sekwencja generowana automatycznie jest zwykle nazywana, t_c_seqgdzie tjest nazwą tabeli i cjest nazwą kolumny, ale zawsze możesz się tego dowiedzieć, wchodząc do psqli mówiąc:

=> \d table_name;

a następnie spojrzeć na domyślną wartość danej kolumny, na przykład:

id | integer | not null default nextval('people_id_seq'::regclass)

FYI: lastval()jest mniej więcej wersją MySQL dla PostgreSQL LAST_INSERT_ID. Wspominam o tym tylko dlatego, że wiele osób lepiej zna MySQL niż PostgreSQL, więc odsyłanie lastval()do czegoś znajomego może wyjaśnić sprawę.


2
Lepiej jednak użyj currval () na wypadek, gdyby wyzwalacz z tabeli1 wykonywał kolejne wstawienia.
Denis de Bernardy

@Denis: Prawda, ale wtedy potrzebujesz nazwy sekwencji. Dodam małą aktualizację tego efektu, żeby objąć wszystkie podstawy.
mu jest za krótkie

LASTVAL () i CURRVAL () działają dla bieżącego połączenia z bazą danych, a nie dla innych połączeń. Inni użytkownicy mogą aktualizować sekwencję w tym samym czasie, co nie zmieni wyników. Nie martw się o innych, oni nigdy nie zmienią Twoich wyników dla LASTVAL i / z CURRVAL. LASTVAL i CURRVAL nie mogą być w ogóle używane podczas korzystania z puli połączeń bez TRANSACTION, wtedy coś idzie nie tak: nie kontrolujesz połączenia z bazą danych.
Frank Heikens,

1
@Frank: Tak, wszystkie są specyficzne dla sesji, ale problem lastvalpolega na tym, że za plecami może znajdować się sekwencja oparta na INSERT z wyzwalacza AFTER INSERT w Table1. Byłoby to w bieżącej sesji i prawdopodobnie zmieniłoby się, lastval()gdy się tego nie spodziewasz.
mu jest za krótkie

1

table_ex

id default nextval ('table_id_seq' :: regclass),

camp1 varchar

camp2 varchar

INSERT INTO table_ex(camp1,camp2) VALUES ('xxx','123') RETURNING id 
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.