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

У меня есть пользовательская таблица с идентификаторами и именами пользователей (и другими деталями) и несколько других таблиц, ссылающихся на эту таблицу с различными именами столбцов (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..

Источник

2 ответа

Решение

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

  1. С помощью hstore расширение. 
CREATE TYPE footype AS (a int, b int, c int);

postgres = # выберите строку (10,20,30);
    строка     
------------
 (10,20,30)
(1 ряд)

postgres = # выберите строку (10,20,30)::footype #= 'b=>100';? Колонок?   
-------------
 (10,100,30)
(1 ряд)

hstore Основанная функция может быть очень простой:

создать или заменить функцию update_fields (r anyelement,
                                         вариадические изменения текста [])
возвращает любой элемент как $$
выберите $ 1 # = hstore ($ 2);
$$ language sql;

postgres = # выберите * 
             из полей update_fields (строка (10,20,30)::footype, 
                                "б", "1000", "с", "800");
 а | б | с  
----+------+-----
 10 | 1000 | 800
(1 ряд)
  1. Несколько лет назад я написал расширение pl toolbox. Есть функция record_set_fields: 
pavel = # select * from pst.record_expand (pst.record_set_fields (row (10,20), 'f1', 33));
 имя | значение | типовое   
------+-------+---------
 f1   | 33    | целое число
 f2   | 20    | целое число
(2 ряда)

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

На PostgreSQL 9.4 (возможно, 9.3) вы можете попробовать использовать магию JSON:

postgres = # select json_populate_record (NULL:: footype, jo) 
              из (выберите json_object(array_agg(ключ),
                                       array_agg (ключ регистра, когда 'b' 
                                                          тогда 1000:: текст
                                                          остальное значение 
                                                 конец)) джо
       из json_each_text(row_to_json(row(10,20,30)::footype))) x;
 json_populate_record 
----------------------
 (10,1000,30)
(1 ряд)

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

СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ public.update_field(r anyelement, текст fn, текст val, результат out anyelement) ВОЗВРАЩАЕТ anyelement
 LANGUAGE plpgsql
AS $function$ Declare jo json;
begin
  jo:= (выберите json_object(array_agg(key),
                                       array_agg(case case, когда 'b', затем val
                                               else value end)) из json_each_text(row_to_json(r))); результат:= json_populate_record(r, jo); конец;
$function$

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

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

Источник

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

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

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

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

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

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

SELECT json_populate_record( 
     record
    , ('{"'||'key'||'":"'||'new-value'||'"}')::json
);
Источник
Другие вопросы по тегам