Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vGGT1-00B4IC-3W for pgsql-general@arkaria.postgresql.org; Tue, 04 Nov 2025 12:48:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vGGT0-00Dgvn-2K for pgsql-general@arkaria.postgresql.org; Tue, 04 Nov 2025 12:48:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vGGSz-00Dgvf-MI for pgsql-general@lists.postgresql.org; Tue, 04 Nov 2025 12:48:52 +0000 Received: from zcsmtaf02-pub.meteo.fr ([137.129.63.6]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vGGSw-005vsi-0h for pgsql-general@lists.postgresql.org; Tue, 04 Nov 2025 12:48:52 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id E617345B4452 for ; Tue, 4 Nov 2025 12:48:48 +0000 (GMT) Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10032) with ESMTP id jmGDGljlHn-u for ; Tue, 4 Nov 2025 12:48:48 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id CCDD445B4456 for ; Tue, 4 Nov 2025 12:48:48 +0000 (GMT) X-Virus-Scanned: amavis at meteo.fr Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10026) with ESMTP id XfiVLSPRc3Ai for ; Tue, 4 Nov 2025 12:48:48 +0000 (GMT) Received: from zcsmsm04.meteo.fr (zcsmsm04.meteo.fr [172.24.3.124]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id B6AA545B4452 for ; Tue, 4 Nov 2025 12:48:48 +0000 (GMT) Date: Tue, 4 Nov 2025 12:48:48 +0000 (GMT) From: PALAYRET Jacques To: pgsql-general@lists.postgresql.org Message-ID: <616435641.255753519.1762260528389.JavaMail.zimbra@meteo.fr> Subject: PostgreSQL trigger how to detect a column value explicitely modified MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_10c7c16d-c859-4f39-8819-ad26a2e3b05b" X-Originating-IP: [172.24.2.157] X-Mailer: Zimbra 9.0.0_GA_4583 (ZimbraWebClient - FF128 (Win)/9.0.0_GA_4583) Thread-Index: tolrOwerUXsQ/EIF9lJCaY9T8G0WzQ== Thread-Topic: PostgreSQL trigger how to detect a column value explicitely modified List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_10c7c16d-c859-4f39-8819-ad26a2e3b05b Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello,=20 In a trigger body, is there a simple way to know if a column value has been= explicitely modified ?=20 Explicitely modified ; in others words, typically indicated in the SET clau= se of the UPDATE.=20 A simple way ; I mean without analysing the SQL statement which can be very= difficult according to the statement.=20 # My trigger function executed by trigger BEFORE UPDATE ON a table tb (with= columns id, c2, c3 ; of integer type) FOR EACH ROW :=20 CREATE OR REPLACE FUNCTION func_tg_upd()=20 RETURNS trigger LANGUAGE plpgsql=20 AS $function$=20 BEGIN=20 if NEW.c2 !=3D 1=20 then=20 NEW.c2 :=3D 0 ;=20 end if;=20 return NEW;=20 END;=20 $function$=20 ;=20 # Actions of the trigger=20 UPDATE tb=20 SET c 2 =3D2=20 WHERE ... -- old c2 value can be 2 or another value=20 ;=20 =3D> c2 will be changed to 0 by the trigger, OK , because I explicitely ind= icated the new value of c2 to a value different from 1.=20 The the old value of c2 could be different or not.=20 Example :=20 TABLE tb ;=20 id | c2 | c3=20 -----+----+----=20 100 | 1 | 2=20 UPDATE tb=20 SET c2=3D2=20 ;=20 TABLE tb ;=20 id | c2 | c3=20 -----+----+----=20 100 | 0 | 2=20 ---------- ---------- ---------- ---------- ----------=20 UPDATE tb=20 SET c 3 =3D3=20 WHERE ... -- and c2=3D2 ( previous value of c2 is 2 )=20 ;=20 =3D> c2 will be changed to 0 by the trigger, but I don't want that action (= NOT OK for me ) because I didn't explicitely indicate the new value 2 to c= 2 .=20 Example :=20 TABLE tb ;=20 id | c2 | c3=20 -----+----+----=20 100 | 2 | 2=20 UPDATE tb=20 SET c3=3D3=20 ;=20 TABLE tb ;=20 id | c2 | c3=20 -----+----+----=20 100 | 0 | 3=20 Regards=20 ----- M=C3=A9t=C3=A9o-France -----=20 PALAYRET Jacques=20 DCSC/GDC=20 jacques.palayret@meteo.fr=20 Fixe : +33 561078319=20 --=_10c7c16d-c859-4f39-8819-ad26a2e3b05b Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello,

In a trigger body, is there a simple way to know if a colum= n value has been explicitely modified ?
Explicitely modified ; in others= words, typically indicated in the SET clause of the UPDATE.
A simple wa= y ; I mean without analysing the SQL statement which can be very difficult = according to the statement.

# My trigger= function executed by trigger BEFORE UPDATE ON a table= tb (with columns id, c2, c3 ; of integer type) FOR EACH ROW :
CREATE OR REPLACE FUNCTION func_tg_upd()
&= nbsp;RETURNS trigger  LANGUAGE plpgsql
AS $function$
BEGIN
  if NEW.c2 !=3D 1
<= span style=3D"background-color: rgb(204, 255, 204);" data-mce-style=3D"back= ground-color: #ccffcc;">  then
    NEW.c2 :=3D 0 ;  end if;
return = NEW;
END;
$function$
;

# Actions of= the trigger
UPDATE tb
SET c= 2=3D2
WHERE ...  &nbs= p;  -- old c2 value can be 2 or another value
;=3D> c2 will be changed to 0<= /span> by the trigger, OK,= because I explicitely indicated the new value of c2 to a value different f= rom 1.
The the old value of c2 could be different or not.
= Example :
TABLE tb ;
 id&nb= sp; | c2 | c3
-----+----+----
 100 |  1 |  2

UPDATE tb
SET c2=3D2
;
 TABLE tb ;
 id  | c2 | c3-----+----+----
 100 |  0 |  2
---------- ------= ---- ---------- <= !--StartFragment-->---------- ------= ----

UPDATE tb
SET c<= span style=3D"background-color: rgb(255, 204, 153);" data-mce-style=3D"back= ground-color: #ffcc99;">3=3D3
WHERE ...  &nbs= p;  -- and c2=3D2 (previous value of = c2 is 2)
;
=3D> c= 2 will be changed to 0 by the trigger, but I don't want tha= t action (NOT OK for me) b= ecause I didn't explicitely indicate the n= ew value 2 to c2.
Example = :
TABLE tb ;
 id  | c2 | c3
-----+----+----
 = ;100 |  2 |  2
UPDATE tb
SET c3=3D3
;
TABLE tb ;
 = id  | c2 | c3
-----+----+----
 100 | = 0 |  3


Regards
----- M=C3=A9t=C3=A9o-France -----
= PALAYRET Jacques
DCSC/GDC
jacques.palayret@meteo.fr
Fixe :&nb= sp;+33 561078319
--=_10c7c16d-c859-4f39-8819-ad26a2e3b05b--