just simplified, but it works fine for me.
create table example(id int primary key, value text);
create or replace function trg_fn() returns trigger language plpgsql as $$
begin
RAISE NOTICE 'trigger_func(%) called: action = "" when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RAISE NOTICE 'id=%, value=%', NEW.id, NEW.value;
update example set value=replace(value,'_',' ') where left(value,3) = 'US_';
return new;
end; $$;
create trigger after_insert_trigger after insert ON example for each row execute function trg_fn();
insert into example select x, case when x % 2 = 0 then 'US_' || x::text else x::text end from generate_series(1, 100) x;
NOTICE: trigger_func(<NULL>) called: action = "" when = AFTER, level = ROW
NOTICE: id=99, value=99
NOTICE: Returned 0 rows
NOTICE: trigger_func(<NULL>) called: action = "" when = AFTER, level = ROW
NOTICE: id=100, value=US_100
NOTICE: Returned 0 rows
INSERT 0 100
-- do not see any values with US_, although i inserted 50 of them.
postgres=# select count(*) from example where value like 'US\_%';
count
-------
0
(1 row)
-- do see 50 "US<space>" values as expected.
postgres=# select count(*) from example where value like 'US %';
count
-------
50
(1 row)
Can you verify accountnumber field does not have any spaces etc at the beginning.
unless there is some conflicting stuff modifying rows, i think this should be ok.
you can
lock TABLE example IN exclusive mode; -- DO NOT DO IT IF IT IMPACTS ANYTHING IN PRODUCTION
On Thu, 6 May 2021 at 13:15, Atul Kumar <akumar14871@xxxxxxxxx> wrote:
Hi,
I have simple table having structure like given below:
\d bp_ach_trans
Table "bonzipay.bp_ach_trans"
Column | Type |
Modifiers
--------------------+------------------------+-------------------------------------------------------------------
bptransid | integer | not null default
nextval('bp_ach_trans_bptransid_seq1'::regclass)
filename | character varying(50) |
payment_status | character varying(30) |
settledate | character varying(15) |
payment_pastransid | bigint |
tname | character varying(250) |
code | character varying(5) |
error_txt | character varying(200) |
routingnumber | character varying(15) |
tracenumber | character varying(10) |
accountnumber | character varying(15) |
bankaccountnumber | character varying(17) |
type | character varying(1) |
amount | numeric |
site | character varying(30) |
accountype | character varying(2) |
tranid | character varying(15) |
Triggers:
ins_ussf_rec AFTER INSERT ON bp_ach_trans FOR EACH ROW EXECUTE
PROCEDURE ussf_accountnumber_update()
the function definition is like below:
CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ BEGIN update bonzipay.bp_ach_trans set
accountnumber=replace(accountnumber,'_',' ') where
left(accountnumber,3) = 'US_'; RETURN NEW; END; $function$
my query is:
when I am inserting around 1000 records in the table having
accountnumber not having value 'US_', I am getting only 300 records
insertion. remaining around 700 values are not getting inserted.
why this strange behavior is happening, as I am not inserting any
record having value 'US_' even after that all records are not
inserting.
Any suggestions are welcome.
Regards,
Atul
Thanks,
Vijay
Mumbai, India
