prumin
@prumin
Изучаю Java и мобильную разработку

В чем проблема в этом триггере?

Всем привет, подскажите, кто знает, пересел с oracle на postgres, не могу понять в чем ошибка.

вот таблицы:

create table sotr(
id_sotr int primary key,
FIO_sotr varchar(100) NOT NULL,
data_sotr date not null,
pasp_sotr int not null,
adres_sotr varchar(100) not null,
tel_setr int not null,
login varchar(20) not null,
hesh text not null);

create table pac(
id_pac int primary key,
fio_pac varchar(100) not null,
pol varchar(10) not null,
date_pac date not null,
adres_pac varchar(100) not null,
tel_pac int not null);

create table raspis(
id_rasp int primary key,
id_sotr int references sotr(id_sotr),
id_pac int references pac(id_pac),
data_rasp date NOT NULL,
time_rasp varchar(5) not null);

create table special(
id_spec int primary key,
spec varchar(100));

create table spec_sotr(
id_spsotr int primary key,
id_sotr int references sotr(id_sotr),
id_spec int references special(id_spec));

create table diang(
id_diag int primary key,
diagnoz varchar(200) not null);

create table analiz(
id_an int primary key,
an varchar(200) not null,
stoim_an int not null);

create table proc(
id_proc int primary key,
proc varchar(100) not null,
stoim_proc int not null);

create table uslugi(
id_usl int primary key,
usl varchar(200) not null,
stoim_usl int not null);

create table lechenie(
id_lech int primary key,
id_diag int references diang(id_diag),
id_an int references analiz(id_an),
id_usl int references uslugi(id_usl),
id_proc int references proc(id_proc),
id_pac int references pac(id_pac),
sum int null);


вот триггер:

CREATE OR REPLACE FUNCTION lech()
RETURNS TRIGGER
AS $$
DECLARE endSum int;
BEGIN
new.sum := get_sum(new.id_an, new.id_usl, new.id_proc);
insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
	values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum);
	RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION get_sum(id int, id2 int, id3 int) RETURNS int
AS $$
#print_strict_params on
DECLARE
    sum1 int;
    sum2 int;
    sum3 int;
BEGIN
    SELECT uslugi.stoim_usl INTO STRICT sum1
        FROM uslugi WHERE uslugi.id_usl = id;
    SELECT analiz.stoim_an INTO STRICT sum2
        FROM analiz WHERE analiz.id_an = id2;
    SELECT proc.stoim_proc INTO STRICT sum3
        FROM proc WHERE proc.id_proc = id3;
    RETURN sum1+sum2+sum3;
END
$$ LANGUAGE plpgsql;


CREATE TRIGGER On_lech_Insert
	AFTER INSERT ON lechenie
	FOR EACH ROW
	EXECUTE PROCEDURE lech();


и когда я вставляю строку
insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac)
	values(nextVal('idLech'),'1','1','1','2','1');


он мне вот что выдает:

kursow=# insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac)
values(nextVal('idLech'),'1','1','1','2','1');
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL statement "SELECT uslugi.stoim_usl FROM uslugi WHERE uslugi.id_usl = id"
PL/pgSQL function get_userid(integer,integer,integer) line 8 at SQL statement
PL/pgSQL function lech() line 4 at assignment
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement
SQL statement "insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac, sum)
values(nextVal('idLech'),new.id_diag, new.id_an, new.id_usl, new.id_proc,new.id_pac, new.sum)"
PL/pgSQL function lech() line 5 at SQL statement


Что не так, не догоняю, помогите пожалуйста.
  • Вопрос задан
  • 65 просмотров
Решения вопроса 1
Melkij
@Melkij
PostgreSQL DBA
Вы в after insert триггере на табличке со странным названием lechenie выполняете безусловный insert в эту же самую таблицу. Вопрос к вам: почему вы в результате ожидаете что-то кроме бесконечной рекурсии?
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы