PostgreSQL динамически изменяет поля в NEW-записи в триггерной функции

У меня есть таблица пользователя с идентификаторами и именами пользователей (и другими сведениями) и несколькими другими таблицами, ссылающимися на эту таблицу с различными именами столбцов (CONSTRAINT some_name FOREIGN KEY (columnname) REFERENCES "user" (userid)). Мне нужно добавить имена пользователей в ссылочные таблицы (при подготовке к отбрасыванию всей таблицы пользователя). Это, конечно, легко выполняется с помощью одиночных ALTER TABLE и UPDATE, и хранение этих современных триггеров также (довольно) легко. Но это триггерная функция, которая вызывает у меня некоторое раздражение. Я мог бы использовать отдельные функции для каждой таблицы, но это казалось излишним, поэтому для этой цели я создал одну общую функцию:

CREATE OR REPLACE FUNCTION public.add_username() RETURNS trigger AS
$BODY$
  DECLARE
    sourcefield text;
    targetfield text;
    username text;
    existing text;
  BEGIN
    IF (TG_NARGS != 2) THEN
      RAISE EXCEPTION 'Need source field and target field parameters';
    END IF;
    sourcefield = TG_ARGV[0];
    targetfield = TG_ARGV[1];
    EXECUTE 'SELECT username FROM "user" WHERE userid = ($1).' || sourcefield INTO username USING NEW;
    EXECUTE format('SELECT ($1).%I', targetfield) INTO existing USING NEW;
    IF ((TG_OP = 'INSERT' AND existing IS NULL) OR (TG_OP = 'UPDATE' AND (existing IS NULL OR username != existing))) THEN
      CASE targetfield
        WHEN 'username' THEN
          NEW.username := username;
        WHEN 'modifiername' THEN
          NEW.modifiername := username;
        WHEN 'creatorname' THEN
          NEW.creatorname := username;
        .....
      END CASE;
    END IF;
    RETURN NEW;
  END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

И используя функцию триггера:

CREATE TRIGGER some_trigger_name BEFORE UPDATE OR INSERT ON my_schema.my_table FOR EACH ROW EXECUTE PROCEDURE public.add_username('userid', 'username');

Как это работает, функция триггера получает имя исходного источника (например userid) и имя целевого поля (username) через TG_ARGV. Затем они используются для заполнения (возможно) отсутствующей информации. Все это работает достаточно хорошо, но как я могу избавиться от этого CASE -mess? Есть ли способ динамически изменять значения в записи NEW, когда я не знаю названия поля заранее (точнее, может быть много чего)? Он находится в параметре targetfield, но, очевидно, NEW.targetfield не работает, или что-то вроде NEW[targetfield] (например, Javascript).

Любые идеи о том, как это можно сделать? Помимо использования, например, PL/Python..

Ответы

Ответ 1

Существуют не простые решения на основе plpgsql. Некоторые возможные решения:

  • Использование hstore расширение.
CREATE TYPE footype AS (a int, b int, c int);

postgres=# select row(10,20,30);
    row     
------------
 (10,20,30)
(1 row)

postgres=# select row(10,20,30)::footype #= 'b=>100';
  ?column?   
-------------
 (10,100,30)
(1 row)
Функция

hstore может быть очень простой:

create or replace function update_fields(r anyelement,
                                         variadic changes text[])
returns anyelement as $$
select $1 #= hstore($2);
$$ language sql;

postgres=# select * 
             from update_fields(row(10,20,30)::footype, 
                                'b', '1000', 'c', '800');
 a  |  b   |  c  
----+------+-----
 10 | 1000 | 800
(1 row)
  1. Несколько лет назад я написал расширение pl toolbox. Существует функция record_set_fields:
pavel=# select * from pst.record_expand(pst.record_set_fields(row(10,20),'f1',33));
 name | value |   typ   
------+-------+---------
 f1   | 33    | integer
 f2   | 20    | integer
(2 rows)

Возможно, вы найдете некоторые решения plpgsql, основанные на некоторых трюках с системными таблицами и массивами типа , но я не могу это предложить. Это слишком мало читаемо и для не продвинутого пользователя только черная магия. hstore прост и почти везде, поэтому он должен быть предпочтительным.

В PostgreSQL 9.4 (возможно, 9.3) вы можете попробовать черную магию с помощью JSON-манипуляций:

postgres=# select json_populate_record(NULL::footype, jo) 
              from (select json_object(array_agg(key),
                                       array_agg(case key when 'b' 
                                                          then 1000::text
                                                          else value 
                                                 end)) jo
       from json_each_text(row_to_json(row(10,20,30)::footype))) x;
 json_populate_record 
----------------------
 (10,1000,30)
(1 row)

Итак, я могу написать функцию:

CREATE OR REPLACE FUNCTION public.update_field(r anyelement, 
                                               fn text, val text, 
                                               OUT result anyelement)
 RETURNS anyelement
 LANGUAGE plpgsql
AS $function$
declare jo json;
begin
  jo := (select json_object(array_agg(key), 
                            array_agg(case key when 'b' then val
                                               else value end)) 
            from json_each_text(row_to_json(r)));
  result := json_populate_record(r, jo);
end;
$function$

postgres=# select * from update_field(row(10,20,30)::footype, 'b', '1000');
 a  |  b   | c  
----+------+----
 10 | 1000 | 30
(1 row)

Функция, основанная на JSON, не должна быть ужасной. hstore должен быть быстрее.

Ответ 2

ОБНОВЛЕНИЕ/предостережение: Эрвин указывает, что это в настоящее время недокументировано, а документы указывают, что это невозможно. Используйте решение Pavel или hstore.

Решение на основе json почти так же быстро, как и hstore, упрощается. json_populate_record() изменяет существующие записи для нас, поэтому нам нужно только создать json-объект из ключей, которые мы хотим изменить.

См. мой аналогичный ответ, где вы найдете тесты, которые сравнивают решения.

Самое простое решение требует Postgres 9.4:

SELECT json_populate_record (
      record
     ,json_build_object('key', 'new-value')
);

Но если у вас есть только Postgres 9.3, вы можете использовать casting вместо json_object:

SELECT json_populate_record( 
     record
    , ('{"'||'key'||'":"'||'new-value'||'"}')::json
);