Need help with trigger

From: Condor <condor(at)stz-bg(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Need help with trigger
Date: 2021-01-23 12:57:24
Message-ID: 6feca05ea1521b0a4da87bfcfe78c39f@stz-bg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2021-01-23 15:17:14 Re: Copy & Re-copy of DB
Previous Message sivapostgres@yahoo.com 2021-01-23 12:52:43 Re: Copy & Re-copy of DB