Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table table_a ( id          integer       generated always as identity
                                                 primary key 
                     , track_code   text         not null 
                     , create_dtts  timestamptz  not null default current_timestamp
                     , a_val        integer      not null
                     ); 
                 
create table table_b ( id          integer       generated always as identity
                                                 primary key 
                     , track_code   text         not null 
                     , create_dtts  timestamptz  not null default clock_timestamp()
                     , items        integer      not null  
                     ) ; 

CREATE TABLE



CREATE TABLE


create or replace function group_a_ais()
  returns trigger
 language 'plpgsql'
as $$
begin
    insert into  table_b(track_code, items)
          select track_code, count(*)
            from rows_inserted_to_a
           group by track_code ;
    return null;
end;
$$;

create trigger table_a_ais
    after insert on table_a
    referencing new table as rows_inserted_to_a
    for each statement  
        execute function group_a_ais();
 

CREATE FUNCTION



CREATE TRIGGER


select * 
  from table_a;

select * 
  from table_b 
 order by track_code; 


id
track_code
create_dtts
a_val



SELECT 0




id
track_code
create_dtts
items



SELECT 0


insert into table_a (track_code, a_val) 
     select 'TC-' || ((100*random())::integer+1)%4
          , (20*random())::integer+1
       from generate_series(1,15);  
       

INSERT 0 15


select * 
  from table_a;

select * 
  from table_b 
 order by track_code;


id
track_code
create_dtts
a_val


1
TC-3
2024-10-01 13:32:13.865436+01
12

2
TC-1
2024-10-01 13:32:13.865436+01
10

3
TC-3
2024-10-01 13:32:13.865436+01
16

4
TC-2
2024-10-01 13:32:13.865436+01
1

5
TC-1
2024-10-01 13:32:13.865436+01
12

6
TC-3
2024-10-01 13:32:13.865436+01
12

7
TC-2
2024-10-01 13:32:13.865436+01
11

8
TC-0
2024-10-01 13:32:13.865436+01
17

9
TC-1
2024-10-01 13:32:13.865436+01
13

10
TC-2
2024-10-01 13:32:13.865436+01
7

11
TC-0
2024-10-01 13:32:13.865436+01
18

12
TC-1
2024-10-01 13:32:13.865436+01
3

13
TC-0
2024-10-01 13:32:13.865436+01
3

14
TC-3
2024-10-01 13:32:13.865436+01
2

15
TC-3
2024-10-01 13:32:13.865436+01
11



SELECT 15




id
track_code
create_dtts
items


2
TC-0
2024-10-01 13:32:13.870302+01
3

1
TC-1
2024-10-01 13:32:13.870091+01
4

4
TC-2
2024-10-01 13:32:13.870317+01
3

3
TC-3
2024-10-01 13:32:13.870312+01
5



SELECT 4

id	track_code	create_dtts	a_val
1	TC-3	2024-10-01 13:32:13.865436+01	12
2	TC-1	2024-10-01 13:32:13.865436+01	10
3	TC-3	2024-10-01 13:32:13.865436+01	16
4	TC-2	2024-10-01 13:32:13.865436+01	1
5	TC-1	2024-10-01 13:32:13.865436+01	12
6	TC-3	2024-10-01 13:32:13.865436+01	12
7	TC-2	2024-10-01 13:32:13.865436+01	11
8	TC-0	2024-10-01 13:32:13.865436+01	17
9	TC-1	2024-10-01 13:32:13.865436+01	13
10	TC-2	2024-10-01 13:32:13.865436+01	7
11	TC-0	2024-10-01 13:32:13.865436+01	18
12	TC-1	2024-10-01 13:32:13.865436+01	3
13	TC-0	2024-10-01 13:32:13.865436+01	3
14	TC-3	2024-10-01 13:32:13.865436+01	2
15	TC-3	2024-10-01 13:32:13.865436+01	11

id	track_code	create_dtts	items
2	TC-0	2024-10-01 13:32:13.870302+01	3
1	TC-1	2024-10-01 13:32:13.870091+01	4
4	TC-2	2024-10-01 13:32:13.870317+01	3
3	TC-3	2024-10-01 13:32:13.870312+01	5