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