From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | brilliantov(at)byterg(dot)ru, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: After insert trigger not work |
Date: | 2014-11-13 14:57:02 |
Message-ID: | 5464C6BE.5040209@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/13/2014 04:27 AM, Brilliantov Kirill Vladimirovich wrote:
> 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;
In above and below you are selecting two column values into one integer
variable, you may not be getting what you think you are:
test=> DO $$
DECLARE
var_1 integer DEFAULT 0;
BEGIN
SELECT 10, 1 INTO var_1;
RAISE NOTICE '%', var_1;
END;
$$ LANGUAGE plpgsql
;
NOTICE: 10
DO
Or maybe you are, it is not clear what the variables are supposed to
hold. From the name I would say the device id, from how they are used
below I would say the free ram values.
> 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?
You might to put some RAISE NOTICEs in your function to track what is
going on:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-errors-and-messages.html
> Thank you and excuse me for my bad english.
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-11-13 15:26:57 | Re: sepgsql where are the security labels |
Previous Message | Russell Keane | 2014-11-13 14:40:31 | Data corruption |