How to DELETE/INSERT rows in the same table using a UPDATE Trigger?

Question

I want to create a trigger function, which copies certain columns of an recent updated row and deletes the old data. After that I want to insert the copied columns in exact the same table in the same row (overwrite). I need the data to be INSERTED because this function will be embedded in an existing program, with predefined Triggers.

That's what I have so far:

CREATE OR REPLACE FUNCTION update_table()
RETURNS TRIGGER AS
$func$
BEGIN

WITH tmp AS (DELETE FROM table 
             WHERE table.id = NEW.id 
             RETURNING id, geom ) 

INSERT INTO table (id, geom) SELECT * FROM tmp;

                  
END;
$func$ language plpgsql;

 
CREATE TRIGGER T_update
AFTER UPDATE OF geom ON table
EXECUTE PROCEDURE update_table();

But I get the Error message:

ERROR: cannot perform DELETE RETURNING on relation "table"
HINT: You need an unconditional ON DELETE DO INSTEAD rule with a RETURNING clause.

Why I should use a rule here?

I'm using PostgreSQL 9.6

UPDATE:

A little bit of clarification. When I have two columns in my table (id, geom), after I updated geom I want to make a copy of this (new)row and insert it into the same table, while overwriting the updated row. (I'm not interested in any value before the update) I know that this is odd but I need this row to be inserted again because the program i embed this function in, listens to a INSERT statement and cannot be changed by me.

Answer 1

First thanks to @JimJones for the answer. I´d like to post his answer modified for this purpose. This code "overwrites" the updated row by inserting a copy of itself and then deleting the old duplicate. That way I can Trigger on INSERT.

CREATE TABLE t (Unique_id SERIAL,id int, geom geometry(point,4326));

CREATE OR REPLACE FUNCTION update_table() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO t (id, geom) VALUES (NEW.id,NEW.geom);
  RETURN NEW;
END; 
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_update 
BEFORE UPDATE OF geom ON t FOR EACH ROW EXECUTE PROCEDURE update_table();


CREATE OR REPLACE FUNCTION delete_table() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM t a
    USING t b
    WHERE a.Unique_id < b.Unique_id
    AND a.geom = b.geom;
RETURN NEW;
END; 
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_delete 
AFTER UPDATE OF geom ON t FOR EACH ROW EXECUTE PROCEDURE delete_table();


INSERT INTO t VALUES (1,1,'SRID=4326;POINT(1 1)');


UPDATE t SET geom = 'SRID=4326;POINT(2 2)' WHERE id = 1;