วิธีการตั้งค่าคอลัมน์ประเภทคอมโพสิตโดยใช้ไดนามิก sql ในขั้นตอนทริกเกอร์

ฉันมีฟังก์ชันทริกเกอร์ที่ถูกเรียกโดยหลายตารางเมื่อมีการอัปเดต COLUMN A เพื่อให้สามารถอัปเดต COLUMN B ตามค่าจากฟังก์ชันอื่นได้ (อธิบายยากกว่าที่เป็นจริง) ฟังก์ชันทริกเกอร์ใช้ col_a และ col_b เนื่องจากมีความแตกต่างกันในตารางที่ต่างกัน

IF needs_updated THEN
    sql = format('($1).%2$s = dbo.foo(($1).%1$s); ', col_a, col_b);

    EXECUTE sql USING NEW;
END IF;

เมื่อฉันพยายามเรียกใช้รูปแบบข้างต้น รูปแบบจะสร้าง sql นี้:

($1).NameText = dbo.foo(($1).Name);

เมื่อฉันรัน SQL ด้วยการใช้ ฉันคาดหวังว่าสิ่งนี้จะเกิดขึ้น (ซึ่งใช้งานได้เมื่อดำเนินการโดยตรงโดยไม่มี sql แบบไดนามิก):

NEW.NameText = dbo.foo(NEW.Name);

ฉันจะได้รับ:

[42601] ข้อผิดพลาด: ข้อผิดพลาดทางไวยากรณ์ที่หรือใกล้ "$1"

ฉันจะอัปเดตคอลัมน์ในเรคคอร์ด/ประเภทคอมโพสิตใหม่แบบไดนามิกได้อย่างไร


postgresql triggers database-trigger
person Airn5475    schedule 25.02.2016    source แหล่งที่มา

คำตอบ (2)


arrow_upward
0
arrow_downward

สิ่งนี้จะไม่ทำงานเนื่องจาก NEW.NameText = dbo.foo(NEW.Name); ไม่ใช่แบบสอบถาม sql ที่ถูกต้อง และฉันไม่สามารถนึกถึงวิธีที่คุณสามารถอัปเดตแอตทริบิวต์ตัวแปรของ NEW แบบไดนามิกได้ คำแนะนำของฉันคือกำหนดพฤติกรรมสำหรับแต่ละตารางของคุณอย่างชัดเจน:

IF TG_TABLE_SCHEMA = 'my_schema' THEN
    IF TG_TABLE_NAME = 'my_table_1' THEN
        NEW.a1 = foo(NEW.b1);
    ELSE IF TG_TABLE_NAME = 'my_table_2' THEN
        NEW.a2 = foo(NEW.b2);
    ... etc ...
    END IF;
END IF;
person Ildar Musin    schedule 25.02.2016
comment
อิลดาร์ขอบคุณสำหรับการตอบกลับ น่าเสียดายที่มีตรรกะเบื้องหลัง needs_updated ที่จะทำซ้ำสำหรับแต่ละตาราง (และคอลัมน์ที่ระบุ) และ ณ จุดนั้น ฉันอาจสร้างฟังก์ชันทริกเกอร์แยกกันสำหรับแต่ละตารางด้วย ฉันหวังว่าจะลดตรรกะที่ซ้ำซากลง - person Airn5475; 25.02.2016

arrow_upward
0
arrow_downward

อันดับแรก: นี่เป็นความเจ็บปวดครั้งใหญ่ใน plpgsql ดังนั้นคำแนะนำที่ดีที่สุดของฉันคือทำสิ่งนี้ใน PL อื่น ๆ เช่น plpythonu หรือ plperl การทำเช่นนี้ในสิ่งใดสิ่งหนึ่งจะเป็นเรื่องเล็กน้อย แม้ว่าคุณจะไม่ต้องการกระตุ้นทั้งหมดใน PL อื่น แต่คุณยังคงสามารถทำสิ่งต่อไปนี้ได้:

v_new RECORD;
BEGIN
v_new := plperl_function(NEW, column_a...)

กุญแจสำคัญในการทำเช่นนี้ใน plpgsql คือการสร้าง CTE ที่มีสิ่งที่คุณต้องการ:

c_new_old CONSTANT text := format(
  'WITH
    NEW AS (SELECT (r).* FROM (SELECT ($1)::%1$s r) s)
    , OLD AS (SELECT (r).* FROM (SELECT ($2)::%1$s r) s
    '
  , TG_RELID::regclass
);

คุณจะต้องกำหนด v_new ที่เป็นบันทึกธรรมดาด้วย จากนั้นคุณสามารถทำสิ่งเช่น:

-- Replace 2nd field in NEW with a new value
sql := c_new_old || $$SELECT row(NEW.a, $3, NEW.c) FROM NEW$$
EXECUTE sql INTO v_new USING NEW, OLD, new_value;
person Jim Nasby    schedule 29.02.2016