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

Question

[Thymomenos Gata]

Всем привет, подскажите, кто знает, пересел с 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

Что не так, не догоняю, помогите пожалуйста.

Answer 1

[Melkij]

Вы в after insert триггере на табличке со странным названием lechenie выполняете безусловный insert в эту же самую таблицу. Вопрос к вам: почему вы в результате ожидаете что-то кроме бесконечной рекурсии?

Comments

[Thymomenos Gata]

я до этого додумался, да, затупил :D
вопрос теперь в другом
функцию изменил вот так:

CREATE OR REPLACE FUNCTION lech()
RETURNS TRIGGER
AS $$
DECLARE
    sum1 int;
    sum2 int;
    sum3 int;
BEGIN
SELECT uslugi.stoim_usl INTO sum1
        FROM uslugi WHERE uslugi.id_usl = new.id_usl;
SELECT analiz.stoim_an INTO sum2
        FROM analiz WHERE analiz.id_an = new.id_an;
SELECT proc.stoim_proc INTO sum3
        FROM proc WHERE proc.id_proc = new.id_proc;
new.sum := sum1+sum2+sum3;
RETURN new;
END;
$$ LANGUAGE plpgsql;

при добавлении строки sum не добавляется, не подскажите, в чем проблема?

[Thymomenos Gata]

триггер нужно сделать BEFORE?

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

[Melkij]

А как after trigger'у менять уже записанное в табличку значение? Ну разве только ещё один update дёргать.
Если хотите изменить вставляемую строку - то это надо делать перед записью, before триггер конечно.

[Thymomenos Gata]

последний вопрос,
почему запись "new" не назначена?

ERROR: record "new" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: SQL statement "SELECT uslugi.stoim_usl FROM uslugi WHERE uslugi.id_usl = new.id_usl"
PL/pgSQL function lech() line 7 at SQL statement

[Thymomenos Gata]

я поменял на before и вот такой результат

[Thymomenos Gata]

Все, разобрался, огромное спасибо)