Как установить столбец составного типа, используя динамический 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 с USING, я ожидаю, что произойдет что-то подобное (что работает при выполнении прямо без динамического sql):

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

Вместо этого я получаю:

[42601] ОШИБКА: синтаксическая ошибка на уровне или около "$1"

Как я могу динамически обновлять столбец в записи/составном типе NEW?


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. Так что моя лучшая рекомендация — сделать это в каком-нибудь другом ЯП, таком как 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