Re: Need help with trigger - Mailing list pgsql-general

On 23-01-2021 18:31, Adrian Klaver wrote:
> 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?

Data is read from another table in the same database but is not in the
same format.

>
> 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;
>


Yep, I think about this update and was my first approach but need to be
done only if end_date is different.
If end_date is the same this mean contract still exists so only update
lastseen column, if the lastseen is not updated other process will read
not seen contracts and will generate report and send them to delete
services. If lastseen is changed and sendto is zero again, mean end_date
is changed and need to be send new end_date again to other systems.



>
>>      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
>>
>>