Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.t1(id int, t text);
CREATE TABLE s2.t1_duplicates (id int, t text);
INSERT INTO s1.t1 VALUES
  (1, 'bad1')
, (2, 'good')
, (3, 'bad2');
3 rows affected

CREATE OR REPLACE PROCEDURE pg_temp.f_archive_dupes(_source_schema text, _source_table text, _unique_keys int[], OUT _row_count int)
  LANGUAGE plpgsql AS
$proc$
   -- target schema and appendix are always the same, source_table is a variable input
DECLARE
   _target_schema CONSTANT text := 's2';  -- hardcoded. ADAPT!
   _target_table  text := _source_table || '_duplicates';
   _sql           text := format(
'WITH del AS (
   DELETE FROM %I.%I
   WHERE  id = ANY($1)
   RETURNING *
   )
INSERT INTO %I.%I TABLE del', _source_schema, _source_table
                            , _target_schema, _target_table);
BEGIN
   RAISE NOTICE '%', _sql;           -- debug
   EXECUTE _sql USING _unique_keys;  -- execute

   GET DIAGNOSTICS _row_count = ROW_COUNT;
END
$proc$;
CALL pg_temp.f_archive_dupes('s1', 't1', '{1, 3}', 0);


_row_count


2


TABLE s1.t1;
TABLE s2.t1_duplicates;


id
t


2
good




id
t


1
bad1

3
bad2