Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


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


create table post_tag( post_id integer 
                     , tag_id  integer
                     ) ; 
create or replace function max_post_check() 
  returns trigger
 language plpgsql 
as $$
declare
    
    max_posts_allowed integer = 5;
    over_allowed_id   integer;
    number_of_posts   integer;
begin 
     select post_id, count(*) 
       into over_allowed_id 
          , number_of_posts
       from post_tag          
      group by post_id
     having count(*) > max_posts_allowed
     limit 1;
 
     if number_of_posts is not null then 
         raise exception 'Post_id %, with % references exceeds application limit of %',over_allowed_id,number_of_posts,max_posts_allowed;
     end if;  

     return null;  
end;
$$;

create trigger max_post_check_ais
    after insert or update on post_tag
    for each statement
        execute function max_post_check();
-- insert more tha max allowed references                     
insert into post_tag( post_id, tag_id)
    select 57, (random()*100)::integer 
      from generate_series(1,15) gs(n);
ERROR:  Post_id 57, with 15 references exceeds application limit of 5
CONTEXT:  PL/pgSQL function max_post_check() line 17 at RAISE


select * 
  from post_tag; 
-- insert the allowed number
insert into post_tag( post_id, tag_id)
    select 57, (random()*100)::integer 
      from generate_series(1,5) gs(n);
5 rows affected

select * 
  from post_tag;


post_id
tag_id


57
13

57
51

57
6

57
3

57
36


-- insert to more the allowed references
insert into post_tag( post_id, tag_id)
    values (57, 0); 
ERROR:  Post_id 57, with 6 references exceeds application limit of 5
CONTEXT:  PL/pgSQL function max_post_check() line 17 at RAISE


select * 
  from post_tag;


post_id
tag_id


57
13

57
51

57
6

57
3

57
36


-- insert another post_id
 insert into post_tag( post_id, tag_id)
    values (37, 0);
1 rows affected

select * 
  from post_tag;


post_id
tag_id


57
13

57
51

57
6

57
3

57
36

37
0


-- update to create more than allowed references
update post_tag 
  set post_id = 57 
where post_id = 37; 
ERROR:  Post_id 57, with 6 references exceeds application limit of 5
CONTEXT:  PL/pgSQL function max_post_check() line 17 at RAISE


select * 
  from post_tag;


post_id
tag_id


57
13

57
51

57
6

57
3

57
36

37
0