RE: Automatically updating a new information column in PostgreSQL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

RE: Automatically updating a new information column in PostgreSQL

Voillequin, Jean-Marc

Hello,

 

The function has a “return new;” in the exception part, not in the main part.

A return new is probably missing after the end if;

 

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;

/* ===================================== HERE ================================*/

return new;

----
-- 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';

 

From: Pepe TD Vo <[hidden email]>
Sent: Friday, October 4, 2019 3:27 PM
To: Pgsql-admin <[hidden email]>; [hidden email]
Subject: Automatically updating a new information column in PostgreSQL

 

 

CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.

 

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

-----------------------------------------
Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its customers, employees and business and where allowed to do so by applicable law. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.

This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
-----------------------------------------
Reply | Threaded
Open this post in threaded view
|

Re: Automatically updating a new information column in PostgreSQL

Tom Lane-2
Pepe TD Vo <[hidden email]> writes:
> 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;

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


bvo
Reply | Threaded
Open this post in threaded view
|

Re: Automatically updating a new information column in PostgreSQL

bvo
In reply to this post by Voillequin, Jean-Marc
1st I had both "return new" in the exception and main part. When I run insert and it is still not working and when I remove it from main part, the result is the same.  

an expert from reddit test the script out and he informed me that the script worked fine.  I have test in the linux box and it worked fine too but do not understand why it's not working in AWS unclassified environment.

I found out that in AWS is case sensitive and recreated table with lower case and voila, all worked. 

thank you so much for looking into script and corrected my work.

have a good weekend.

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


On Friday, October 4, 2019, 09:49:33 AM EDT, Voillequin, Jean-Marc <[hidden email]> wrote:


Hello,

 

The function has a “return new;” in the exception part, not in the main part.

A return new is probably missing after the end if;

 

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;

/* ===================================== HERE ================================*/

return new;

----
-- 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';

 

From: Pepe TD Vo <[hidden email]>
Sent: Friday, October 4, 2019 3:27 PM
To: Pgsql-admin <[hidden email]>; [hidden email]
Subject: Automatically updating a new information column in PostgreSQL

 

 

CAUTION: This email originated from outside of Moody's. Do not click links or open attachments unless you recognize the sender and know the content is safe.

 

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

-----------------------------------------
Moody's monitors email communications through its networks for regulatory compliance purposes and to protect its customers, employees and business and where allowed to do so by applicable law. The information contained in this e-mail message, and any attachment thereto, is confidential and may not be disclosed without our express permission. If you are not the intended recipient or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that you have received this message in error and that any review, dissemination, distribution or copying of this message, or any attachment thereto, in whole or in part, is strictly prohibited. If you have received this message in error, please immediately notify us by telephone, fax or e-mail and delete the message and all of its attachments. Every effort is made to keep our network free from viruses. You should, however, review this e-mail message, as well as any attachment thereto, for viruses. We take no responsibility and have no liability for any computer virus which may be transferred via this e-mail message.

This email was sent to you by Moody’s Investors Service EMEA Limited
Registered office address:
One Canada Square
Canary Wharf
London, E14 5FA
Registered in England and Wales No: 8922701
-----------------------------------------