RE: Automatically updating a new information column in PostgreSQL - Mailing list pgsql-sql
| From | Voillequin, Jean-Marc |
|---|---|
| Subject | RE: Automatically updating a new information column in PostgreSQL |
| Date | |
| Msg-id | BYAPR20MB2614A0A6FCF3246CF66CF202BE9E0@BYAPR20MB2614.namprd20.prod.outlook.com Whole thread Raw |
| Responses |
Re: Automatically updating a new information column in PostgreSQL
|
| List | pgsql-sql |
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 <pepevo@yahoo.com>
Sent: Friday, October 4, 2019 3:27 PM
To: Pgsql-admin <pgsql-admin@postgresql.org>; pgsql-sql@postgresql.org
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 thenv_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
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