agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Pepe TD Vo <pepevo@yahoo.com>
To: Pgsql-admin <pgsql-admin@postgresql.org>
To: pgsql-sql@postgresql.org
Subject: Automatically updating a new information column in PostgreSQL
Date: Fri, 4 Oct 2019 13:26:52 +0000 (UTC)
Message-ID: <744670167.2325583.1570195612720@mail.yahoo.com> (raw)
References: <744670167.2325583.1570195612720.ref@mail.yahoo.com>
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
view thread (4+ messages) latest in thread
Message-ID: <744670167.2325583.1570195612720@mail.yahoo.com>
Permalink: ../744670167.2325583.1570195612720@mail.yahoo.com/
Also on: postgresql.org/message-id/744670167.2325583.1570195612720@mail.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pepevo@yahoo.com, pgsql-admin@postgresql.org
Subject: Re: Automatically updating a new information column in PostgreSQL
In-Reply-To: <744670167.2325583.1570195612720@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox