postgresql - 如何创建一个通常在条目更新之前的条目后运行的函数？

Question

正如标题所说，我有一个函数，它在每次进入数据库后运行。问题是它不会影响触发器之前输入的所有条目。

如何让它更新以前的？

CREATE OR REPLACE FUNCTION xll_transactions_gl_associated_objects_func(transaction_id bigint)
  RETURNS void AS
$BODY$
DECLARE    
   l_transaction transactions%rowtype;
BEGIN

   SELECT INTO l_transaction * FROM transactions WHERE transactions.id = transaction_id;   

   IF (l_transaction.load_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_business_partner_id = (SELECT business_partner_id FROM loads WHERE  loads.id  = l_transaction.load_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_invoice_no = coalesce(CAST((SELECT invoice_no FROM loads WHERE loads.id  = l_transaction.load_id) AS TEXT), '') WHERE id = transaction_id;
   END IF;

   IF (l_transaction.load_driver_contractor_credit_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_truck_id = (SELECT truck_id FROM load_driver_contractor_credits WHERE  load_driver_contractor_credits.id  = l_transaction.load_driver_contractor_credit_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM load_driver_contractor_credits WHERE  load_driver_contractor_credits.id  = l_transaction.load_driver_contractor_credit_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.load_driver_contractor_deduction_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_truck_id = (SELECT truck_id FROM load_driver_contractor_deductions WHERE  load_driver_contractor_deductions.id  = l_transaction.load_driver_contractor_deduction_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM load_driver_contractor_deductions WHERE  load_driver_contractor_deductions.id  = l_transaction.load_driver_contractor_deduction_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.load_payable_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_truck_id = (SELECT truck_id FROM load_payables WHERE  load_payables.id  = l_transaction.load_payable_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM load_payables WHERE  load_payables.id  = l_transaction.load_payable_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.load_payment_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_business_partner_id = (SELECT business_partner_id FROM loads LEFT JOIN load_payments ON load_payments.load_id = loads.id WHERE  load_payments.id  = l_transaction.load_payment_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_invoice_no = coalesce(CAST((SELECT invoice_no FROM loads LEFT JOIN load_payments ON load_payments.load_id = loads.id WHERE  load_payments.id  = l_transaction.load_payment_id)AS TEXT), '') WHERE id = transaction_id;
      UPDATE transactions SET associated_check_no = (SELECT check_no FROM load_payments  WHERE  load_payments.id  = l_transaction.load_payment_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.payment_id IS NOT NULL) THEN      
      UPDATE transactions SET associated_business_partner_id = (SELECT business_partner_id FROM payments WHERE  payments.id  = l_transaction.payment_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_invoice_no = (SELECT invoice_no FROM payments WHERE payments.id  = l_transaction.payment_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.payment_item_id IS NOT NULL) THEN   
      UPDATE transactions SET associated_business_partner_id = (SELECT business_partner_id FROM payments WHERE  payments.id  = (SELECT bill_id FROM payment_items WHERE payment_items.id = l_transaction.payment_item_id)) WHERE id = transaction_id;
      UPDATE transactions SET associated_invoice_no = (SELECT invoice_no FROM payments WHERE payments.id  = (SELECT bill_id FROM payment_items WHERE payment_items.id = l_transaction.payment_item_id)) WHERE id = transaction_id;
      UPDATE transactions SET associated_check_no = (SELECT check_no FROM payment_details  WHERE  payment_details.id  = (SELECT payment_detail_id FROM payment_items WHERE payment_items.id = l_transaction.payment_item_id)) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.statement_id IS NOT NULL) THEN
      UPDATE transactions SET associated_truck_id = (SELECT truck_id FROM statements WHERE statements.id  = l_transaction.statement_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM statements WHERE statements.id  = l_transaction.statement_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_check_no = (SELECT check_no FROM statements  WHERE  statements.id  = l_transaction.statement_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.statement_fuel_id IS NOT NULL) THEN
      UPDATE transactions SET associated_truck_id = (SELECT truck_id FROM statement_fuel WHERE statement_fuel.id  = l_transaction.statement_fuel_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM statement_fuel WHERE statement_fuel.id  = l_transaction.statement_fuel_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_check_no = (SELECT check_no FROM statements  WHERE  statements.id  = l_transaction.statement_id) WHERE id = transaction_id;
   END IF;

   IF (l_transaction.work_order_id IS NOT NULL) THEN
      UPDATE transactions SET associated_business_partner_id = (SELECT customer_id FROM work_orders WHERE work_orders.id  = l_transaction.work_order_id) WHERE id = transaction_id;
      UPDATE transactions SET associated_invoice_no = CAST((SELECT work_order_no FROM work_orders  WHERE  work_orders.id  = l_transaction.work_order_id) AS TEXT) WHERE id = transaction_id;
      UPDATE transactions SET associated_check_no = (SELECT check_no FROM work_orders  WHERE  work_orders.id  = l_transaction.work_order_id) WHERE id = transaction_id;      
   END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Answer 1

只有深入了解表定义和确切意图，才能优化该功能。主约束和唯一约束、NOT NULL 定义和外键至关重要。

例如，您在同一列 associated_truck_id 上有多个更新。其结果取决于您的约束和外键的详细信息。

由于这是基于触发器的，你可以将函数本身作为触发器函数，从而简化逻辑。

最后，我必须声明，整个工作流程似乎非常可疑。我无法想象一个健全的数据库模型，我会在新行的条目上更新所有现有行。您很可能应该重新考虑您的设计。

无论哪种方式，您都不应该不运行 25 (!) 个单独的更新。这会写入 25 个新的行版本并且非常慢。此外，表会因许多死行而变得臃肿，而 vacuum 有很多额外的工作要做。一切都可以在一次更新中完成。

因此，作为概念证明，以下是将其转换为单一更新的方法。
由于缺乏信息，假设所有引用都是外键列并导致单个匹配。

CREATE OR REPLACE FUNCTION trg_transactions_ins_aft()
  RETURNS trigger AS
$func$
BEGIN

UPDATE transactions t
SET    associated_business_partner_id = COALESCE(p2.business_partner_id, p1.business_partner_id, l2.business_partner_id, l1.business_partner_id, t.associated_business_partner_id)
     , associated_invoice_no          = COALESCE(p2.invoice_no, p1.invoice_no
                                                ,CASE WHEN n.load_payment_id IS NOT NULL THEN COALESCE(l2.invoice_no::text, '') -- simple COALESCE not possible here
                                                      WHEN n.load_id         IS NOT NULL THEN COALESCE(l1.invoice_no::text, '')
                                                      ELSE t.associated_invoice_no
                                                 END)
     , associated_truck_id            = COALESCE(st.truck_id,  lp.ruck_id,   ld.truck_id,  t.associated_truck_id)
     , associated_driver_id           = COALESCE(st.driver_id, lp.driver_id, ld.driver_id, t.associated_driver_id)
     , associated_check_no            = COALESCE(st.check_no,  pd.check_no,  lm.check_no,  t.associated_check_no)
FROM   NEW n
LEFT   JOIN loads                          l1 ON l1.id = n.load_id   -- if NEW.load_id IS NULL, we all NULLs here
LEFT   JOIN load_driver_contractor_credits ld ON ld.id = n.load_driver_contractor_credit_id
LEFT   JOIN load_payables                  lp ON lp.id = n.load_payable_id
LEFT   JOIN load_payments                  lm ON lm.id = n.load_payment_id
LEFT   JOIN loads                          l2 ON l2.id = lm.load_id

LEFT   JOIN payments                       p1 ON p1.id = n.payment_id
LEFT   JOIN payment_items                  pi ON pi.id = n.payment_item_id

LEFT   JOIN payments                       p2 ON p2.id = pi.bill_id
LEFT   JOIN payment_details                pd ON pd.id = pi.payment_detail_id
LEFT   JOIN statements                     st ON st.id = n.statement_id
WHERE  t.id <> n.transaction_id;  -- to affect *all* other rows.

-- I left the rest for you to incorporate as well:

IF NEW.statement_fuel_id IS NOT NULL THEN
 UPDATE transactions SET associated_truck_id  = (SELECT truck_id  FROM statement_fuel WHERE statement_fuel.id  = NEW.statement_fuel_id) WHERE id = transaction_id;
 UPDATE transactions SET associated_driver_id = (SELECT driver_id FROM statement_fuel WHERE statement_fuel.id  = NEW.statement_fuel_id) WHERE id = transaction_id;
 UPDATE transactions SET associated_check_no  = (SELECT check_no  FROM statements     WHERE statements.id      = NEW.statement_id)      WHERE id = transaction_id;
END IF;

IF NEW.work_order_id IS NOT NULL THEN
 UPDATE transactions SET associated_business_partner_id = (SELECT customer_id   FROM work_orders WHERE work_orders.id  = NEW.work_order_id) WHERE id = transaction_id;
 UPDATE transactions SET associated_invoice_no     = CAST((SELECT work_order_no FROM work_orders WHERE work_orders.id  = NEW.work_order_id) AS TEXT) WHERE id = transaction_id;
 UPDATE transactions SET associated_check_no            = (SELECT check_no      FROM work_orders WHERE work_orders.id  = NEW.work_order_id) WHERE id = transaction_id;      
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;


CREATE TRIGGER ins_aft
AFTER INSERT ON transactions
FOR EACH ROW EXECUTE PROCEDURE trg_transactions_ins_aft();