Created
February 15, 2020 02:45
-
-
Save mandado/2d074b1d975a026111b224e96e62bb11 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP FUNCTION IF EXISTS avoid_quota_beyond_limit() CASCADE; | |
CREATE OR REPLACE FUNCTION avoid_quota_beyond_limit() RETURNS TRIGGER AS $$ | |
DECLARE | |
var_group_id uuid; | |
var_product_id uuid; | |
var_quota_by_group int; | |
var_total_quotas int ; | |
var_current_quotas int; | |
BEGIN | |
select product_id into var_product_id from groups where id = NEW.group_id; | |
select coalesce(sum(quotas), 0) into var_total_quotas from user_groups where group_id = NEW.group_id; | |
select quotas_by_group into var_quota_by_group from products where id = var_product_id; | |
RAISE NOTICE 'GROUP TO INSERT ID: %', NEW.group_id; | |
RAISE NOTICE 'TOTAL_QUOTAS: %', var_total_quotas; | |
RAISE NOTICE 'QUOTAS TO INSERT: %', NEW.quotas; | |
var_current_quotas := var_total_quotas + NEW.quotas; | |
IF var_current_quotas > var_quota_by_group THEN | |
RAISE EXCEPTION 'CURRENT QUOTA VALUE IS EXCEEDING THE CURRENT QUOTA (%)', ('MAX_QUOTAS (' || var_current_quotas || ') - TO_INSERT (' || NEW.quotas || ')'); | |
END IF; | |
return NEW; | |
END; | |
$$ LANGUAGE plpgsql; | |
CREATE TRIGGER avoid_quota_beyond_limit | |
BEFORE INSERT ON user_groups | |
FOR EACH ROW EXECUTE PROCEDURE avoid_quota_beyond_limit(); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment