Re: Need help with trigger

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