CREATE OR REPLACE FUNCTION create_instrutor () RETURNS TRIGGER AS $$
DECLARE
wage_average DECIMAL;
low_paid_instrutors INTEGER DEFAULT 0;
total_instrutors INTEGER DEFAULT 0;
wage DECIMAL;
percentuality DECIMAL(5, 2);
BEGIN
SELECT AVG(instrutor.wage) INTO wage_average FROM instrutor WHERE id <> NEW.id;
IF NEW.wage > wage_average THEN
INSERT INTO log_instrutors (description) VALUES (NEW.instrutor_name || ' wage is upper the average.');
END IF;
FOR wage IN SELECT instrutor.wage FROM instrutor WHERE id <> NEW.id LOOP
total_instrutors := total_instrutors + 1;
IF NEW.wage > wage THEN
low_paid_instrutors := low_paid_instrutors + 1;
END IF;
END LOOP;
percentuality = low_paid_instrutors::DECIMAL / total_instrutors::DECIMAL * 100;
IF percentuality >= 100.00 THEN
SELECT instrutor.wage INTO NEW.wage FROM instrutor ORDER BY wage DESC LIMIT 1;
END IF;
INSERT INTO log_instrutors (description) VALUES (NEW.instrutor_name || ' receives more them ' || percentuality || '% of the instrutors cast.');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Observação: Para impedir o insert de um instrutor que receba acima da media com percentual maior ou igual a 100% basta trocar a linha :
SELECT instrutor.wage INTO NEW.wage FROM instrutor ORDER BY wage DESC LIMIT 1;
Pela seguinte linha:
RETURN NULL;