postgresql - Postgres - 将行移动到不同的表

Question

使用 postgres 11 我想自动将行从一个表移动到另一个表。我已经设置了查询、触发函数和触发器，但是当启用触发器时，我的测试插入失败并显示“0 0”。

• 要从中移动行的源表是“cmdb”
• 要将行移动到的目标表是“cmdb_attic”
• 条件是当列'mgmt_ip' = ''
• 整行应该移动
• 该表仅包含 3 列：'hostname'、'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();

标签： postgresqlpostgresql-triggers

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;

（在线演示）