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 по дате отправления:

Предыдущее
От: "sivapostgres@yahoo.com"
Дата:
Сообщение: Re: Copy & Re-copy of DB
Следующее
От: Ron
Дата:
Сообщение: Re: Copy & Re-copy of DB