Обсуждение: Is there any value in using trigger conditions rather than function conditions
Is there any value in using trigger conditions rather than function conditions
От
Bill MacArthur
Дата:
Hello, We have elements in our setup that have been around for quite awhile. We are currently running 9.2 and I was wondering ifthere would be any significant performance increase by moving the conditions out of the function into the trigger... likewould avoiding the function load save anything.... or would the function be loaded regardless? The function is used bymany tables to avoid having to explicitly define the timestamp in every query. We have a similar trig/func setup for recordingthe DB operator at the moment. An example trigger: CREATE TRIGGER update_members_stamp BEFORE UPDATE ON members FOR EACH ROW EXECUTE PROCEDURE update_stamp(); The shared function: CREATE OR REPLACE FUNCTION update_stamp() RETURNS trigger AS $BODY$ BEGIN -- if the stamp is being manually manipulated, then we will leave it alone IF NEW.stamp ISNULL OR NEW.stamp = OLD.stamp THEN NEW.stamp := NOW(); END IF; RETURN NEW; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 1; If we did something like this would there be any real benefit on a moderately busy table? (ideally I would like to embedthe action in the trigger but that does not seem possible) CREATE TRIGGER update_members_stamp BEFORE UPDATE ON members FOR EACH ROW WHEN NEW.stamp ISNULL OR NEW.stamp = OLD.stamp EXECUTE PROCEDURE update_stamp_x(); CREATE OR REPLACE FUNCTION update_stamp_x() RETURNS trigger AS $BODY$ BEGIN NEW.stamp := NOW(); END IF; RETURN NEW; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 1;