Automatically updating a new information column in PostgreSQL

From: Pepe TD Vo <pepevo(at)yahoo(dot)com>
To: Pgsql-admin <pgsql-admin(at)postgresql(dot)org>, <pgsql-sql(at)postgresql(dot)org>
Subject: Automatically updating a new information column in PostgreSQL
Date: 2019-10-04 13:26:52
Message-ID: 744670167.2325583.1570195612720@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-sql


good morning experts,I have a trigger before insert (even with or update) and seem it doesnt' work.  The function simply sets both columns named mig_filename to "unknown if its null, and mig_insert_dt to current timestample for each row passed to the trigger.
here is my script even I take all the rest out and just simple function with new.mig_insert_dt := localtimestamp;

CREATE OR REPLACE FUNCTION "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"()  RETURNS trigger AS $$
declare

v_ErrorCode int;
v_ErrorMsg varchar(512);
v_Module varchar(32) := 'TR_STG_APPLICATION_CDIM_INS';

begin

----

-- If this is an INSERT operation----

if TG_OP = 'INSERT' then

   ----
   -- This just ensures that the filename is not null
   ----

   if new.mig_filename IS NULL then
      new.mig_filename := 'Unknown';
   end if;

   new.mig_insert_dt = current_timestamp;

end if;

----
-- Exception error handler
----

   exception
      when others then

      v_ErrorCode := SQLSTATE;
      v_ErrorMsg := SQLERRM;

      insert into "ECISDRDM"."ERRORLOG"( "TSTAMP", "OS_USER", "HOST", "MODULE", "ERRORCODE",               "ERRORMSG")
      values (CURRENT_TIMESTAMP, CURRENT_USER, inet_server_addr(), v_Module, v_ErrorCode, v_ErrorMsg);

RETURN NEW;
end;
$$
language 'plpgsql';

CREATE TRIGGER "TR_STG_APPLICATION_CDIM_INS" BEFORE INSERT OR UPDATE ON "ECISDRDM"."STG_APPLICATION_CDIM" FOR EACH ROW EXECUTE PROCEDURE "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"() ;

I even " RAISE EXCEPTION 'UNKNOWN'; " and for the mig_insert_dt, I put either '=' or ':=' Now(), now(), localtimestamp, timestamp, and none of them would fill the time. Both mig.filename and mig_insert_dt are still blank."
if new.mig_filename IS NULL then     RAISE EXCEPTION 'UNKNOWN';    new.mig_filename := 'Unknown';end if;new.mig_insert_dt '= now();   

According to the postgres example 39-3 "shows an example of trigger procedure in PL/pgSQL", I don't see any different with the example and don't know what I have missed here.  Would you please advise what I did wrong here?  
thank you,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Voillequin, Jean-Marc 2019-10-04 13:49:24 RE: Automatically updating a new information column in PostgreSQL
Previous Message Tim Underwood 2019-10-02 13:27:37 Questions regarding pgAgent and ODBC drivers

Browse pgsql-sql by date

  From Date Subject
Next Message Voillequin, Jean-Marc 2019-10-04 13:49:24 RE: Automatically updating a new information column in PostgreSQL
Previous Message Rob Sargent 2019-09-27 16:41:05 Re: Transactions in one table