Postgres function to update old data and insert new data

Question

I am trying to write a function that when my script pulls information from AD that it inserts new entries that don't exist and then update the ones that do exist. New entries will have an id that comes from a sequence.

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();

I am fairly new to postgresql. I know with oracle you can use reference NEW as new Old as old in the trigger as follows;

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;

How do I get the same functionality I had in oracle in postgres with a function?

Answer 1

Merge

It seems that MERGE does not implemented in Postgres yet.

Way 1 - INSERT ... ON CONFLICT

Read more here with theory & examples.

This way requires constraint (PRIMARY KEY or any UNIQUE constraint) which name you know (not automatically generated). In example constaint called pk_known_languages.

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;

Note: to DELETE some records you need way 2.

Way 2 - big query with CTE

CTE - Common Table Expresson - is regular SQL command (SELECT, INSERT, UPDATE, DELETE and even VALUES) executed before main SQL command to which it is attached. Read more here with theory & examples.

Example of CTE:

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

It is one big query, cte_name's result acts as temp table which is available in main command.

Solution for merge by the same ids:

(This will update records with the same id's and insert records with new or NULL ids.)

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);

Each next CTE can use data returned by all previous CTEs. First CTE, data, returns the data you want to insert/update. Second CTE, upd, does the update of rows with the same id and returns matched ids to ignore them on following insert. Third, regular INSERT command using upd's result to exclude such ids from INSERT.

If all new records always have no id:

(This variation is for speed-up only, you can still use previous query in this case.)

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;

Note:

This query can also be extended to DELETE records which does not present in data (or by any other condition). Just transform INSERT into third CTE and add DELETE statement as main command.