Postgres Pro
Downloads
Home   >   mailing lists

Thread: Automatically updating a new information column in PostgreSQL

Automatically updating a new information column in PostgreSQL

From
Pepe TD Vo
Date:
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

Re: Automatically updating a new information column in PostgreSQL

From
Tom Lane
Date:
Pepe TD Vo <pepevo@yahoo.com> writes:
> good morning experts,I have a trigger before insert (even with or update) and seem it doesnt' work.  The function
simplysets both columns named mig_filename to "unknown if its null, and mig_insert_dt to current timestample for each
rowpassed to the trigger. 
> here is my script even I take all the rest out and just simple function with new.mig_insert_dt := localtimestamp;

It looks like the RETURN NEW is inside the exception handler recovery
block, which is not where you want it.  When I run this example I get

ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"()

So there is something seriously wrong with however you are testing
this, if you failed to notice that.

            regards, tom lane

Re: Automatically updating a new information column in PostgreSQL

From
Tom Lane
Date:
Pepe TD Vo <pepevo@yahoo.com> writes:
> good morning experts,I have a trigger before insert (even with or update) and seem it doesnt' work.  The function
simplysets both columns named mig_filename to "unknown if its null, and mig_insert_dt to current timestample for each
rowpassed to the trigger. 
> here is my script even I take all the rest out and just simple function with new.mig_insert_dt := localtimestamp;

It looks like the RETURN NEW is inside the exception handler recovery
block, which is not where you want it.  When I run this example I get

ERROR:  control reached end of trigger procedure without RETURN
CONTEXT:  PL/pgSQL function "ECISDRDM"."TRIGGER_FCT_TR_STG_APPLICATION_CDIM_INS"()

So there is something seriously wrong with however you are testing
this, if you failed to notice that.

            regards, tom lane