Обсуждение: Need help with trigger

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

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.

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

On 23-01-2021 18:17, David G. Johnston wrote:
> On Saturday, January 23, 2021, Condor <condor@stz-bg.com> 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 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.
> 
> Seems like an insert on conflict would be simpler.  Otherwise, what is
> your question or concern?
> 
> David J.

This was my first approach, but as the sample data I provide, end_date 
will always be updated not only if it is changed and my goal is to have 
no meaningless records. My question is there a way to update the trigger 
instead of inserting.

HS

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

On 1/23/21 10:20 AM, Condor wrote:
> On 23-01-2021 18:31, Adrian Klaver wrote:
>> On 1/23/21 4:57 AM, Condor wrote:
>>>
>>>

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

So add an:

ELSIF enddate = FROM NEW.end_date THEN

and do UPDATE of lastseen column.

Honestly I think this better handled by the external program that doing 
the transformation.

> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 23-01-2021 20:49, Adrian Klaver wrote:
> On 1/23/21 10:20 AM, Condor wrote:
>> On 23-01-2021 18:31, Adrian Klaver wrote:
>>> On 1/23/21 4:57 AM, Condor wrote:
>>>> 
>>>> 
> 
>>> 
>>> 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.
> 
> So add an:
> 
> ELSIF enddate = FROM NEW.end_date THEN
> 
> and do UPDATE of lastseen column.
> 
> Honestly I think this better handled by the external program that
> doing the transformation.
> 
>> 
>> 
>> 


Sorry,

I'm sorry, I don't understand something. You mean to do pure INSERT ON 
CONFLICT DO or to modify the trigger ?

On 1/23/21 12:14 PM, Condor wrote:
> On 23-01-2021 20:49, Adrian Klaver wrote:
>> On 1/23/21 10:20 AM, Condor wrote:

> 
> Sorry,
> 
> I'm sorry, I don't understand something. You mean to do pure INSERT ON 
> CONFLICT DO or to modify the trigger ?

No I meant that in the external program you use to fetch the data from 
the other table and reorganize the fields.  Do your test there and do 
either the INSERT or UPDATE.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 23-01-2021 23:29, Melvin Davidson wrote:
> Maybe this example will help.
> From https://www.postgresql.org/docs/current/sql-insert.html
>
> INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil
> Distribution')
>     ON CONFLICT (did) DO UPDATE
>     SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
>     WHERE d.zipcode <> '21201';
>
> On Sat, Jan 23, 2021 at 3:47 PM Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>
>> On 1/23/21 12:14 PM, Condor wrote:
>>> On 23-01-2021 20:49, Adrian Klaver wrote:
>>>> On 1/23/21 10:20 AM, Condor wrote:
>>
>>>
>>> Sorry,
>>>
>>> I'm sorry, I don't understand something. You mean to do pure
>> INSERT ON
>>> CONFLICT DO or to modify the trigger ?
>>
>> No I meant that in the external program you use to fetch the data
>> from
>> the other table and reorganize the fields.  Do your test there and
>> do
>> either the INSERT or UPDATE.
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
> --
>
> Melvin Davidson
> Maj. Database & Exploration Specialist
> Universe Exploration Command – UXC
> Employment by invitation only!


Thanks for the ideas,

after sleeping with the thought, on the morning I decided to remove the
trigger and do it with a simple function.

CREATE OR REPLACE FUNCTION public.log_last_chaged(contractid TEXT,
service INTEGER, endd DATE)
  RETURNS INTEGER
  LANGUAGE plpgsql
AS $function$

DECLARE
enddate DATE;

BEGIN
   SELECT INTO enddate end_date FROM arhive_table WHERE contract =
contractid AND servid = service AND command = 1;
   IF enddate IS NULL THEN
     INSERT INTO arhive_table (contract, serviceid, end_date) VALUES
(contractid, service, endd);
   ELSIF enddate IS DISTINCT FROM endd THEN
     UPDATE arhive_table SET sendit = 0, end_date = endd, lastseen =
CURRENT_TIMESTAMP WHERE contract = contractid AND serviceid = service
AND command = 1;
   ELSE
     UPDATE arhive_table SET lastseen = CURRENT_TIMESTAMP WHERE contract
= contractid AND serviceid = service AND command = 1;
   END IF;
   RETURN 1;
END;
$function$;

Thanks again for ideas.
HS