Re: Need help with trigger

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(dot)klaver(at)aklaver(dot)com