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