Как можно последовательно перебрать все столбцы в таблице NEW? 

Здравствуйте! У меня возник вопрос о том, как можно динамически изменять значения в каждом столбце объекта NEW без использования хардкода. Я понимаю, что вариант с использованием UPDATE не подходит, так как данный код будет выполняться внутри триггера на обновление. 

Вот мой текущий код:

<pre><code class="sql">FOREACH v_column IN ARRAY v_columns LOOP
        IF EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_name = TG_TABLE_NAME AND column_name = v_column
        ) THEN
            EXECUTE format('SELECT ($1).%I', v_column) INTO v_value USING NEW; -- получаем значение незашифрованное  
            
            IF v_value IS NOT NULL THEN -- если оно не NULL
            -- NEW.email := text(pgp_sym_encrypt(v_value, 'Aboba')); можно, конечно, так, но тогда нужно будет хардкодить каждый столбец
            EXECUTE format('$1.%I := $2', v_column) USING NEW, text(pgp_sym_encrypt(v_value, 'Aboba')); -- шифруем, здесь я пытаюсь менять значение NEW через execute, чтобы столбцы обновлялись динамически
            END IF;
        ELSE
            RAISE EXCEPTION 'Column "%" not found in table %', v_column, TG_TABLE_NAME;
        END IF;
    END LOOP;</code></pre>

Каковы рекомендации по решению этой задачи?
  • 3 февраля 2025 г. 11:54
Ответы на вопрос 2 
Для динамического изменения значений в каждом столбце объекта `NEW` внутри триггера без использования хардкода, вы можете использовать механизм, который позволяет обращаться к полям объекта через динамическое SQL и массивы. Я вижу, что у вас уже есть часть этого решения, и вы правильно используете `EXECUTE` для выполнения динамических запросов.

Однако, чтобы правильно модифицировать объект `NEW`, вам нужно использовать другой подход, так как напрямую менять значения полей объекта `NEW` через `EXECUTE` не получится. Вместо этого вы можете работать с `NEW` как с записью и соответствующим образом использовать `EXECUTE`.

Вот пример, как можно это сделать:

```sql
CREATE OR REPLACE FUNCTION encrypt_columns()
RETURNS TRIGGER AS $$
DECLARE
    v_column text;
    v_value text;
BEGIN
    -- Перебираем все колонки таблицы
    FOR v_column IN SELECT column_name FROM information_schema.columns WHERE table_name = TG_TABLE_NAME LOOP
        -- Получаем значение текущего столбца
        EXECUTE format('SELECT ($1).%I', v_column) INTO v_value USING NEW;

        -- Если значение не NULL, то шифруем его
        IF v_value IS NOT NULL THEN
            EXECUTE format('UPDATE %I SET %I = $1 WHERE ctid = $2', TG_TABLE_NAME, v_column)
            USING text(pgp_sym_encrypt(v_value, 'Aboba')), TG_TABLE_UNIQUEID; -- Предполагается, что у вас есть уникальный идентификатор строки
        END IF;
    END LOOP;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER encrypt_before_update
BEFORE UPDATE ON your_table
FOR EACH ROW EXECUTE PROCEDURE encrypt_columns();
```

**Объяснение:**
1. **Перебор колонок:** Используем запрос к `information_schema.columns`, который возвращает имена всех колонок в целевой таблице.
2. **Динамический `SELECT`:** Используем `EXECUTE` для получения значения текущего столбца. При этом экземпляр `NEW` передается с помощью `USING`.
3. **Шифрование и обновление:** После получения значения проверяем, не равняется ли оно `NULL`. Если нет, выполняем динамический `UPDATE`, чтобы перезаписать значение зашифрованным значением.

Этот способ не требует хардкодить названия столбцов и позволяет вам динамически работать с объектом `NEW` в триггере. Убедитесь, что у вас есть уникальный идентификатор (в примере использован `ctid`, но вы можете использовать любой другой уникальный идентификатор) для идентификации обновляемой строки.
А не надо ничего хардкодить. И в INFORMATION_SCHEMA лезть тоже необязательно. Берём да используем row_to_json(NEW) , и вся структура как на ладошке. 
 
 Пример 
 
 Получить массив/роусет ключей/значений и пробежаться по нему в цикле - уже не проблема.
Похожие вопросы
    Показать ещё