Оператор выбора PL/pgSQL внутри триггера возвращает null, используя где NEW

я создаю триггер, который срабатывает при INSERT в таблице, и я хочу регистрировать структуру вставленных таблиц, поэтому я написал эту функцию

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column text;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := NEW."COLUMN_NAME";
    target_table_name := NEW."TABLE_NAME";
  END IF;
    SELECT column_name into old_column
                   FROM information_schema."columns"
                   WHERE table_schema = 'items' 
                   and table_name = LOWER(NEW."TABLE_NAME")
                  and column_name = LOWER(NEW."COLUMN_NAME");
if (coalesce(old_column,'')='' or old_column='' or old_column = added_column) THEN
  -- If a new column has been added
  IF (Lower(added_column) != 'sync') then
    
    -- Add the new column to the target table
        EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)';
  END IF;
end if;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

выполняется этим TRIGGER :

CREATE TRIGGER update_table_log_received_trigger
AFTER INSERT
ON items."TABLE_LOG_RECEIVED"
FOR EACH ROW
EXECUTE FUNCTION update_table_log_received();

возвращаемое исключение следующее:

! ОШИБКА: столбец «x» отношения «y» уже существует Где: инструкция SQL « ALTER TABLE items. ДОБАВИТЬ СТОЛБЦ x VARCHAR(50) »

моя проблема сейчас в том, что он не должен проходить проверки If (я вставил код после многих изменений, у меня есть два условия if, которые делают одно и то же просто потому, что), я отлаживал и регистрировал операторы, чтобы отметить, что запрос выбора внутри моей функции, по-видимому, возвращает null. я также пытался использовать «ИСПОЛЬЗОВАНИЕ НОВОГО», но я не эксперт, поэтому я не мог заставить его работать

это проблема с объявленной переменной, которая не заполняется из «НОВОЙ» записи, или я неправильно выполняю оператор выбора?

Обновлено: tl; dr для моей проблемы, я хотел бы обновить таблицу в базе данных2 всякий раз, когда та же таблица (с той же структурой) изменяется из базы данных1, будь то добавленный столбец или измененный столбец, на данный момент я застрял на первая проблема добавить столбец.

Я записываю структуры своих таблиц в виде строк в новую таблицу и синхронизирую ее с Database2, чтобы затем триггер изменил ту же измененную таблицу из Database1, надеюсь, теперь это имеет больше смысла.

Database1 log_table, которая регистрирует все структуры моих таблиц: База данных2 log_table_received, которая является копией log_table, которая выполняется триггер всякий раз, когда вставляются новые значения;

1) plpgsql триггерные функции имеют TG_TABLE_NAME для целевой таблицы. 2) Вы хотите использовать quote_ident() отсюда Строковые функции для работы с именами таблиц и столбцов.

— 08.02.2023 17:36

Целевая таблица — это не таблица, на которой основан триггер, это данные из ROW, но спасибо за то, что я этого не знал, я использовал quote_ident(), конечно, это полезно, но это еще не решило мою проблему, я добавил больше информация к моему посту, может быть, теперь стало понятнее @AdrianKlaver

— 09.02.2023 11:25

sql postgresql triggers plpgsql

08.02.2023 10:17

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Попробуйте этот синтаксис:

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column text;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := new."column_name";
    target_table_name := new."table_name";
  END IF;

  if not exists(select 1 from information_schema."columns" where table_name = target_table_name and column_name = added_column) 
  then 
      EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN ' || LOWER(added_column) || ' VARCHAR(50)';
  end if; 
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Я попробовал на своей БД, это работает. Некоторые детали вы можете изменить самостоятельно.

Я пробовал этот синтаксис, и я попробовал его снова сейчас, как ваш пример и так далее select 1 from information_schema."columns" where table_name = quote_ident(target_table_name) and column_name = quote_ident(added_column), но проблема все та же, я добавил больше информации в свой пост, может быть, теперь это понятнее, и вы могли бы мне помочь

— 09.02.2023 11:27

08.02.2023 19:13

Ответ принят как подходящий

Зафиксированный; Вопрос должен был быть:

Как выбрать таблицы и столбцы таблицы внутри функции в postgresql.

Использованная литература: Как добавить столбец, если его нет в PostgreSQL?

Как проверить, существует ли таблица в заданной схеме

Как получить список имен столбцов и типов данных таблицы в PostgreSQL?

По сути, доступ к information_schema может получить только владелец, то есть пользователь, или (i) увидеть результат, когда я его запрашиваю, но он возвращает FALSE при выполнении внутри скрипта, подробнее здесь: https://stackoverflow.com/a/24089729/15170264

Полный триггер после исправления с помощью CTE для запроса pg_catalog также добавил ADD COLUMN IF NOT EXISTS в мой запрос на выполнение на всякий случай.

CREATE OR REPLACE FUNCTION update_table_log_received()
RETURNS TRIGGER AS $$
DECLARE
  added_column TEXT;
  target_table_name TEXT;
  old_column varchar;
    old_table varchar;
BEGIN
  -- Check if a new column has been added
  IF (TG_OP = 'INSERT') THEN
    added_column := NEW."COLUMN_NAME";
    target_table_name := NEW."TABLE_NAME";
  END IF;
 /*
  * --------------- --CTE to find Columns of table "Target_table_name" from pg_catalog
  */

    WITH cte_tables AS (
   SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'items'
    AND pg_class.relname = 'trace'
ORDER BY
    attnum ASC
)
select column_name into old_column from cte_tables where 
column_name=LOWER(added_column);

 
  if (old_column is null )  then 
    -- Add the new column to the target table
        old_column := added_column;
        EXECUTE 'ALTER TABLE items.' || LOWER(target_table_name)|| ' ADD COLUMN IF NOT EXISTS ' || LOWER(added_column) || ' VARCHAR(50)';
    else
        old_column := added_column || 'already exists ! ';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


create TRIGGER update_table_log_received_trigger
AFTER INSERT
ON items."TABLE_LOG_RECEIVED"
FOR EACH ROW
EXECUTE FUNCTION update_table_log_received();

Переменная old_column хранит сообщение условия else, но я не возвращаю его, если бы это была простая функция.

16.02.2023 13:35