Я пытаюсь написать функцию, которая, когда мой скрипт извлекает информацию из AD, вставляет новые записи, которые не существуют, а затем обновляет те, которые существуют. Новые записи будут иметь идентификатор, полученный из последовательности.

 CREATE OR REPLACE FUNCTION "SCHEMA".fn_name()
    returns trigger AS 
$$
    begin
        if new.dn_id is null then
            NEW.dn_id = nextval("SCHEMA".seq_name); 
        else if (TG_OP = 'UPDATE') then 
            insert into "SCHEMA"."TABLE_NAME"(dn, dn_name, search, user_cat) values (new.dn, new.dn_name, 
              new.search, new.user_cat) where old.dn_name = new.dn_name;
    RETURN NEW;
    end;
$$

LANGUAGE 'plpgsql';

create trigger trg_name
before insert or update
on "SCHEMA"."TABLE"
for each row
execute procedure "SCHEMA".fn_name();
  

Я довольно новичок в postgresql. Я знаю, что с oracle вы можете использовать ссылку NEW как new Old как old в триггере следующим образом;

 create or replace TRIGGER trg_name
    BEFORE INSERT
    ON table
    REFERENCING NEW AS new OLD AS old
    FOR EACH ROW
BEGIN
    SELECT seq_name.NEXTVAL INTO :new.dn_id FROM DUAL;
END trg_name;
  

Как мне получить ту же функциональность, что и в oracle, в postgres с помощью функции?

Слияние

Похоже, это MERGE еще не реализовано в Postgres.

Способ 1 — INSERT ... ON CONFLICT

Подробнее читайте здесь с теорией и примерами.

Этот способ требует ограничения ( PRIMARY KEY или любого UNIQUE ограничения), имя которого вы знаете (не генерируется автоматически). В примере вызывается pk_known_languages constaint .

 INSERT INTO public.known_languages(id, "comment", translations)
 VALUES ('en', 'English', 'Translations here'),
         ('ru', 'Русский', 'Тут переводы')
ON CONFLICT ON CONSTRAINT pk_known_languages
  DO UPDATE
  SET "comment" = EXCLUDED."comment",
     translations = EXCLUDED.translations;
  

Примечание: для DELETE некоторых записей вам нужен способ 2.

Способ 2 — большой запрос с CTE

CTE — Common Table Expresson — это обычная команда SQL ( SELECT, INSERT, UPDATE, DELETE и даже VALUES ), выполняемая перед основной командой SQL, к которой она присоединена. Подробнее читайте здесь с теорией и примерами.

Пример CTE :

     WITH cte_name AS (
        SELECT *
        FROM public.my_table_1
    )
    INSERT INTO public.my_table_2
    SELECT *
    FROM cte_name;
  

Это один большой запрос, cte_name результат которого действует как временная таблица, доступная в главной команде.

Решение для слияния тем же id s:

(Это обновит записи с теми же id ‘s и вставит записи с новыми или NULL id s.)

 WITH data(id, "comment", translations) AS (
  VALUES ('en', 'English', 'Translations here'),
         ('ru', 'Русский', 'Тут переводы')
),
 upd AS (
   UPDATE public.known_languages
     SET
       "comment" = data."comment",
       translations = data.translations
     FROM data
     WHERE known_languages.id = data.id
     RETURNING data.id   -- return all mathced ids
 )
 INSERT INTO public.known_languages
   SELECT *
   FROM data
   WHERE data.id NOT IN (SELECT id
                         FROM upd);
  

Каждый следующий CTE может использовать данные, возвращенные всеми предыдущими CTE .
Во-первых CTE , data , возвращает данные, которые вы хотите вставить / обновить.
Во-вторых CTE , upd , обновляет строки одинаковыми id и возвращает совпадающие id s, чтобы игнорировать их при следующей вставке.
В-третьих, обычная команда INSERT, использующая upd результат ‘s для исключения таких id данных INSERT .

Если все новые записи всегда не имеют идентификатора:

(Этот вариант предназначен только для ускорения, в этом случае вы все равно можете использовать предыдущий запрос.)

 WITH data(id, "comment", translations) AS (
  VALUES (NULL, 'English', 'Translations here'),
         ('ru', 'Русский', 'Тут переводы')
),
 upd AS (
   UPDATE public.known_languages
     SET
       "comment" = data."comment",
       translations = data.translations
     FROM data
     WHERE known_languages.id = data.id
       AND data.id IS NOT NULL
 )
 INSERT INTO public.known_languages
   SELECT *
   FROM data
   WHERE data.id IS NULL;
  

Примечание:

Этот запрос также может быть расширен до DELETE записей, которые не представлены в data (или по любому другому условию). Просто преобразуйте INSERT в third CTE и добавьте DELETE оператор в качестве основной команды.