What is the problem with this trigger?

Question

PostgreSQL

Hello everyone, tell me, who knows, I moved from oracle to postgres, I can’t understand what the mistake is.
here are the tables:

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);

here is the trigger:

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();

and when i insert the line

insert into lechenie(id_lech, id_diag, id_an, id_usl, id_proc, id_pac)
  values(nextVal('idLech'),'1','1','1','2','1');

it gives me this:

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.
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

What's wrong, I'm not catching up, please help.

Answer 1

You in after insert the trigger on a plate with the strange name lechenie execute unconditional insert in the same table. Question for you: why do you expect something other than infinite recursion as a result?