From: | Brilliantov Kirill Vladimirovich <brilliantov(at)byterg(dot)ru> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | After insert trigger not work |
Date: | 2014-11-13 12:27:13 |
Message-ID: | 5464A3A1.9080609@byterg.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello!
I use postgre-9.3.5 on windows7 x64.
Trigger should update data in table:
CREATE TABLE trassa.ram_free_stat
(
id serial NOT NULL,
device integer NOT NULL,
min_value integer NOT NULL,
avg_value integer NOT NULL DEFAULT 0,
max_value integer NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT ram_free_stat_pk PRIMARY KEY (id),
CONSTRAINT ram_free_stat_device_fk FOREIGN KEY (device)
REFERENCES trassa.devices (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ram_free_stat_max_fk FOREIGN KEY (max_value)
REFERENCES trassa.ram (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT ram_free_stat_min_fk FOREIGN KEY (min_value)
REFERENCES trassa.ram (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
My trigger:
CREATE OR REPLACE FUNCTION trassa.update_ram_free_stat()
RETURNS trigger AS
$BODY$
DECLARE
device_id INTEGER DEFAULT 0;
min_id INTEGER DEFAULT 0;
avg_val INTEGER DEFAULT 0;
max_id INTEGER DEFAULT 0;
BEGIN
SELECT id INTO device_id FROM trassa.ram_free_stat
WHERE device = NEW.device;
SELECT free_ram, id INTO min_id FROM trassa.ram
WHERE device = NEW.device
ORDER BY free_ram LIMIT 1;
SELECT free_ram, id INTO max_id FROM trassa.ram
WHERE device = NEW.device
ORDER BY free_ram DESC LIMIT 1;
SELECT CEIL(AVG(free_ram)) INTO avg_val
FROM trassa.ram WHERE device = NEW.device;
IF device_id > 0 THEN
UPDATE trassa.ram_free_stat
SET min_value = min_id,
avg_value = avg_val,
max_value = max_id
WHERE id = device_id;
ELSE
INSERT INTO trassa.ram_free_stat
(device, min_value,
avg_value, max_value)
VALUES(NEW.device, min_id,
avg_val, max_id);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
I add this trigger on another table:
CREATE TRIGGER update_ram_free_stat_trigger
AFTER INSERT
ON trassa.ram
FOR EACH ROW
EXECUTE PROCEDURE trassa.update_ram_free_stat();
All executed without any error, but data in trassa.ram_free_stat not
updated.
Can you help me solve this problem?
Thank you and excuse me for my bad english.
--
Best regards,
Brilliantov Kirill Vladimirovich
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2014-11-13 12:27:49 | Re: Two instances of Postgres with single data directory |
Previous Message | dineshkaarthick | 2014-11-13 12:17:33 | Two instances of Postgres with single data directory |