Need help with trigger
От | Condor |
---|---|
Тема | Need help with trigger |
Дата | |
Msg-id | 6feca05ea1521b0a4da87bfcfe78c39f@stz-bg.com обсуждение исходный текст |
Ответы |
Re: Need help with trigger
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Need help with trigger (Adrian Klaver <adrian.klaver@aklaver.com>) |
Список | pgsql-general |
Hello ppl, I need help with trigger how to replace insert command with update. External program read data from one table and in every 30 min I need to refresh data in arhive table. What I want is: if data already inserted and end_date is not changed, only to update lastseen column. If data not exists to insert data and if data exists and end_date is changed to update end_date, lastseen and sendto columns. Well, update probably will never happened, this functionality is left for frontend but its can be cut off. Here is my table DROP TABLE IF EXISTS arhive_table; CREATE TABLE arhive_table ( uts integer default date_part('epoch', CURRENT_TIMESTAMP)::integer, contract text, service integer, end_date date, lastseen timestamp WITHOUT time zone default CURRENT_TIMESTAMP, sendto integer default 0, error text ); CREATE UNIQUE INDEX arhive_table_uniq ON arhive_table(contract, service); Date is: INSERT INTO arhive_table (contract, service, end_date) VALUES ('CNT1', 1, '2021-01-31'), ('CNT1', 2, '2021-01-31'); after 30 min data can be: INSERT INTO arhive_table (contract, service, end_date) VALUES ('CNT1', 1, '2021-02-28'), ('CNT1', 2, '2021-01-31'); and my trigger is: CREATE OR REPLACE FUNCTION public.log_last_chaged() RETURNS trigger LANGUAGE plpgsql AS $function$ enddate DATE; BEGIN IF TG_OP = 'INSERT' THEN SELECT INTO enddate end_date FROM arhive_table WHERE contract = NEW.contract AND service = NEW.service; IF enddate IS NULL THEN -- line below probably will do normal INSERT RETURN NEW; ELSIF enddate IS DISTINCT FROM NEW.end_date THEN NEW.sendto := 0; NEW.uts := date_part('epoch', CURRENT_TIMESTAMP)::integer; -- But here need to do UPDATE not INSERT END IF; ELSIF TG_OP = 'UPDATE' THEN IF OLD.end_date IS DISTINCT FROM NEW.end_date THEN NEW.sendto := 0; NEW.uts := date_part('epoch', CURRENT_TIMESTAMP)::integer; END IF; END IF; -- lastseen must always be updated with CURRENT_TIMESTAMP if contract is seen NEW.lastseen := CURRENT_TIMESTAMP; RETURN NEW; END $function$; DROP TRIGGER IF EXISTS last_changes ON arhive_table; CREATE TRIGGER last_changes BEFORE INSERT OR UPDATE OF end_date ON arhive_table FOR EACH ROW WHEN (pg_trigger_depth() < 1) EXECUTE FUNCTION log_last_chaged(); Regards, HS
В списке pgsql-general по дате отправления: