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@aklaver.com