Postgres -将行移动到不同的表

Question

使用postgres 11，我希望自动将行从一个表移动到另一个表。我已经设置了一个查询、触发器函数和触发器，但是当启用触发器时，我的测试插入会以' 0‘失败。

• 从is 'cmdb‘中移出行的源表
• 将行移动到“cmdb_attic”的目标表
• 条件是当列'mgmt_ip‘= '’时
• 整行应该移动
• 该表仅包含3列：“主机名”、“mgmt_ip”、“os_type”

我拥有的触发器函数代码是：

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

我在表'cmdb‘下定义了一个触发器，该触发器在事件插入之前触发。

当我对表'cmdb‘进行测试时，我不会收到任何错误消息，也不会将任何内容插入到任何一个表中。

溶液

我从pgAdmin中删除了触发器函数和触发器，并将下面提供的代码Bergi运行到pgsql中，它可以工作。

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

编辑1 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)

编辑2 -测试插入并选择

在启用触发器的情况下，下面是我得到的。如果我禁用触发器，我的插入工作，我可以在'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)

编辑3 -用于在pgAdmin4中创建和应用触发器函数和触发器的步骤

未列出的设置/选项卡未被调整

1. 表>触发器函数>创建>触发器函数
2. 键入名称'move_to_attic‘
3. 代码选项卡:插入代码(从原始文章)
4. 没有调整其他选项/设置
5. 表> cmdb >触发器>创建>触发器
6. 键入名称'move_to_attic‘
7. 定义选项卡:触发器已启用(是)、行触发器(是)、触发器函数public.move_to_attic
8. 事件选项卡:激发之前，事件插入
9. 代码选项卡:触发器函数中的代码已经在那里了。
10. SQL选项卡:只说"--没有更新。“

编辑4 -在SQL上输出触发器和触发器函数

触发器函数(使用Bergi的答案)

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

触发器(应用于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

基本上，我想将插入从cmdb重定向到符合条件的cmdb_attic。

它的触发器函数应该如下所示：

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

(在线演示)