Search Postgresql Archives

Re: Need help with trigger

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 1/23/21 4:57 AM, Condor wrote:


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 is the table being read from and is it in the same database?

See more comments inline below.

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.



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

So do an UPDATE and the RETURN NULL to cancel the INSERT. Untested example:

UPDATE arhive_table SET sendto = 0, uts = date_part('epoch', CURRENT_TIMESTAMP)::integer WHERE contract = NEW.contract AND service = NEW.service;

RETURN NULL;


     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




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux