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
