Postgresql
將 OLD & NEW 傳遞給函式
我有2個觸發器。
triggerA
並分別triggerB
作用於tableA
和的變化tableB
。在這兩個觸發器中,我想使用與OLD
&NEW
variables相同的函式。我不知道該怎麼做,因為它會拋出錯誤:OLD hasn't been assigned yet. The tuple structure of a not-yet-assigned record is indeterminate.
然後我嘗試將 null 分配給一個變數來解決這個問題,它是:
old_v hasn't been assigned yet.
觸發器 1 和 2(這部分在兩個觸發器中都很常見):
DECLARE added added_scores; old_v record; new_v record; BEGIN CASE TG_OP WHEN 'INSERT' THEN old_v = null; new_v = NEW; WHEN 'UPDATE' THEN old_v = OLD; new_v = NEW; WHEN 'DELETE' THEN old_v = OLD; new_v = null; END CASE; select _i_compute_added_scores(TG_OP, old_v, new_v) into added; -- some computation, return result
功能 :
CREATE OR REPLACE FUNCTION public._i_compute_added_scores( tg_op_p text, old_p anyelement, new_p anyelement) RETURNS added_scores AS $BODY$ DECLARE new_score integer; old_score integer; res added_scores; BEGIN CASE TG_OP_p WHEN 'INSERT' THEN new_score := new_p.score; old_score := 0; WHEN 'DELETE' THEN new_score := 0; old_score := old_p.score; WHEN 'UPDATE' THEN new_score := new_p.score; old_score := old_p.score; END CASE; -- some more computation and return
您需要在將其作為參數發送之前分配 RECORD 類型,在您的情況下,一種非常簡單的方法是簡單地將其分配給 NEW 或 OLD,即使它不是必需的,例如:
CASE TG_OP WHEN 'INSERT' THEN old_v = NEW; new_v = NEW; WHEN 'UPDATE' THEN old_v = OLD; new_v = NEW; WHEN 'DELETE' THEN old_v = OLD; new_v = OLD; END CASE;
請注意,現在使用
TG_OP
和控制必須讀取/使用的變數非常重要。