Postgres - move rows to different table

Question

Using postgres 11 I would like to automatically move rows from one table to another. I have setup a query, trigger function, and trigger but my test inserts fail with '0 0' when the trigger is enabled.

• source table to move rows from is 'cmdb'
• destination table to move rows to is 'cmdb_attic'
• condition is when column 'mgmt_ip' = ''
• entire row should move
• the table only contains 3 columns: 'hostname', 'mgmt_ip', 'os_type'

The trigger function code I have is:

BEGIN
    WITH moved_rows AS (
        DELETE FROM cmdb
        WHERE mgmt_ip=''
        RETURNING *
    )
    INSERT INTO cmdb_attic
    SELECT * FROM moved_rows;
    RETURN NULL;
END;

I defined a trigger under the table 'cmdb' that fires before on events insert.

When I do a test insert against table 'cmdb' I receive no error message, and nothing is inserted - into either table.

SOLUTION

I deleted my trigger function and trigger from pgAdmin and ran the code Bergi provided below into pgsql and it works.

CREATE FUNCTION redirect_to_attic() RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.mgmt_ip = '' THEN
        INSERT INTO cmdb_attic VALUES (NEW.*);
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER redirect
  BEFORE INSERT
  ON cmdb 
  FOR EACH ROW 
  EXECUTE PROCEDURE redirect_to_attic();

EDIT 1 - trigger details from pgsql

inv_net=# select * from pg_trigger;
 tgrelid |    tgname     | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
---------+---------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+------------
   24623 | move_to_attic |  24618 |      7 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
(1 row)

EDIT 2 - test insert and select

With the trigger enabled, below is what I get. If I disable the trigger, my insert works and I can find that row in 'cmdb'.

inv_net=# INSERT INTO cmdb(hostname, mgmt_ip, os_type) VALUES ('testdevice', '', 'ios');
INSERT 0 0

inv_net=# select * from cmdb where hostname='testdevice';
 hostname | mgmt_ip | os_type
----------+---------+---------
(0 rows)

inv_net=# select * from cmdb_attic where hostname='testdevice';
 hostname | mgmt_ip | os_type
----------+---------+---------
(0 rows)

EDIT 3 - Steps Used to Create and Apply Trigger Function and Trigger in pgAdmin4

settings/tabs not listed were not adjusted

1. Tables > Trigger Functions > Create > Trigger Function
2. Type name 'move_to_attic'
3. Code tab: Insert code (from original post)
4. No other options/settings adjusted
5. Tables > cmdb > Triggers > Create > Triggers
6. Type name 'move_to_attic'
7. Definition tab: Trigger Enabled (yes), Row trigger (yes), Trigger Function public.move_to_attic
8. Events tab: Fires BEFORE, Events INSERT
9. Code tab: my code from the Trigger Function is there already
10. SQL tab: just says "-- No updates."

EDIT 4 - Output on SQL Tabs for Trigger and Trigger Function

trigger function (using Bergi's answer)

-- FUNCTION: public.move_to_attic()

-- DROP FUNCTION public.move_to_attic();

CREATE FUNCTION public.move_to_attic()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$BEGIN
    IF NEW.mgmt_ip='' THEN
        INSERT INTO cmdb_attic SELECT NEW;
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;$BODY$;

ALTER FUNCTION public.move_to_attic()
    OWNER TO svc_netops_postgre;

trigger (applied to cmdb)

-- Trigger: move_to_attic

-- DROP TRIGGER move_to_attic ON public.cmdb;

CREATE TRIGGER move_to_attic
    AFTER INSERT
    ON public.cmdb
    FOR EACH ROW
    EXECUTE PROCEDURE public.move_to_attic();

Answer 1

basically I want to redirect an insert from cmdb to cmdb_attic where that condition is met

The trigger function for that should look like this:

BEGIN
    IF NEW.mgmt_ip = '' THEN
        INSERT INTO cmdb_attic VALUES (NEW.*);
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;

(online demo)