SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
create table "source" (id int, value int); insert into "source" values(1, 1), (2, 1), (1, 1), (1, 1), (2, 1), (1, 1); create table target (time timestamp primary key, count1 int, count2 int); insert into target values ('2022-04-17 17:00:00'::timestamp, 4, NULL); -- Есть триггерная функция, но не могу понять, почему при вставке id 2 в исходную таблицу, count2 в target не обновляется? CREATE FUNCTION trigger_function() RETURNS TRIGGER LANGUAGE PLPGSQL AS $trigger_function$ BEGIN IF new.id = 1 THEN INSERT INTO target (time, count1) VALUES ( date_trunc('hour', NOW()::timestamp) , NEW.value) ON CONFLICT (time) DO UPDATE SET count1 = target.count1 + EXCLUDED.count1; END IF; IF new.id = 2 THEN INSERT INTO target (time, count2) VALUES ( date_trunc('hour', NOW()::timestamp),NEW.value) ON CONFLICT (time) DO UPDATE SET count2 = target.count2 + EXCLUDED.count2; END IF; RETURN NEW; END; $trigger_function$; CREATE TRIGGER insert_into_source AFTER INSERT ON "source" FOR EACH ROW EXECUTE PROCEDURE trigger_function(); insert into "source" values (1, 3), (2, 5); select * from target;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear