sql - Validate subquery count is not greater than x -

sql - Validate subquery count is not greater than x -

i trying create function , trigger verify count of wid , rdate columns within responsibility table less or equal 10. need raise exception when greater 10.

my subquery count not working. when count() greater 10, no exception thrown. doing wrong?

create function check_10() returns trigger $$ begin if (select count(case when wid = new.wid , rdate = new.rdate 1 else 0 end) total resposibility) > 10 raise exception 'maximum of 10 cage responsibilites each worker per date'; end if; homecoming new; end; $$ language plpgsql; /* i've added update create sure when "updating" row run trigger check verify */ create trigger insert_resp before insert or update on resposibility each row execute procedure check_10();

as long raising exception (and don't grab it), roll whole transaction anyway. return statement doesn't matter in case. remove it.

it alternative utilize return null instead of exception if want silently skip operation on current row , otherwise proceed normally.

the obvious error in code > 10 instead of >= 10 as pointed out @a_horse. , typo in resposibility. rest matter of efficiency.

also, assignments comparatively expensive in plpgsql, there no need here. simplify:

create or replace function check_10() returns trigger $func$ begin if (select count(*) >= 10 responsibility wid = new.wid , rdate = new.rdate) raise exception 'worker % has max. of 10 responsibilites date: %' , new.wid, new.rdate; -- no need *any* homecoming statement here! end if; homecoming new; end $func$ language plpgsql;

be sure have index on wid , rdate or, ideally, multicolumn index on (wid, rdate).

sql postgresql triggers plpgsql