伙计们!

我在 MySQL 数据库中有一个触发器:

CREATE DEFINER="root"@"127.0.0.1" TRIGGER `tai_actions_for_active_count` AFTER INSERT ON `actions` FOR EACH ROW BEGIN
  DECLARE l_isnn TINYTEXT;
  IF NEW.action_type IN ('CREATION', 'VERIFICATION', 'CLOSE') THEN

    SET l_isnn = IF(NEW.isnn is NULL, '*', NEW.isnn);
    IF NOT NEW.action_type = 'CLOSE' THEN
      INSERT INTO subscriptions.`statistics_active_count`(`service_id`, `operator_id`, `isnn`, `active_count`)
        VALUES (NEW.service_id, NEW.operator_id, l_isnn, 1)
        ON DUPLICATE KEY UPDATE active_count = active_count + 1;
    ELSE
      SET @tai_actions_for_active_count = -1;
      UPDATE subscriptions.`statistics_active_count` SET active_count = @tai_actions_for_active_count := active_count - 1
        WHERE `service_id` = NEW.service_id AND `operator_id` = NEW.operator_id AND `isnn` = l_isnn;
      IF @tai_actions_for_active_count = 0 THEN DELETE FROM subscriptions.`statistics_active_count` WHERE `active_count` = 0; END IF;
    END IF;
  END IF;
END

所以我需要重写它以使其在 Postgres 数据库中工作。因为有 ON DUPLICATE KEY UPDATE 我正在使用带有 UPSERT (ON CONFLICT (KEY) DO UPDATE) 的 Postgres 9.5 版。

所以我对 SQL 语言知之甚少，你能告诉我我做错了什么吗？有 Postgres PL 代码:

  DECLARE
    l_isnn TEXT;
    tai_actions_for_active_count INTEGER;
  BEGIN
    IF NEW.action_type IN ('CREATION', 'VERIFICATION', 'CLOSE') THEN
        IF NEW.isnn is NULL THEN
            l_isnn := '*';
        ELSE
            l_isnn := NEW.isnn;
        END IF;
        IF NOT NEW.action_type = 'CLOSE' THEN
            INSERT INTO "subscriptions.statistics_active_count"(service_id, operator_id, isnn, active_count)
                VALUES (NEW.service_id, NEW.operator_id, l_isnn, 1)
                ON CONFLICT(active_count) DO UPDATE SET active_count = active_count + 1;
        ELSE
            tai_actions_for_active_count := -1;
            UPDATE "subscriptions.statistics_active_count" SET active_count = active_count - 1
        -- (tai_actions_for_active_count := active_count - 1)
                WHERE service_id = NEW.service_id AND operator_id = NEW.operator_id AND isnn = l_isnn;
            UPDATE "subscriptions.statistics_active_count" SET tai_actions_for_active_count = active_count
                WHERE service_id = NEW.service_id AND operator_id = NEW.operator_id AND isnn = l_isnn;
            IF tai_actions_for_active_count = 0 THEN DELETE FROM "subscriptions.statistics_active_count" WHERE active_count = 0; END IF;
        END IF;
    END IF;
    RETURN NULL;
  END;

因为我想测试这个触发器，所以我收到一个错误 -- relation "subscriptions.statistics_active_count"does not exist

你能帮我处理那个代码吗？

最佳答案

我终于找到了解决方案。我猜:)

BEGIN
   IF NEW.action_type IN ('CREATION', 'VERIFICATION', 'CLOSE') THEN
      IF NEW.isnn IS NULL THEN
         l_isnn := '*';
      ELSE
         l_isnn := NEW.isnn;
      END IF;
      IF NOT NEW.action_type = 'CLOSE' THEN
         BEGIN
            INSERT INTO subscriptions.statistics_active_count (service_id, operator_id, isnn, active_count)
            VALUES (NEW.service_id, NEW.operator_id, l_isnn, 1);
         EXCEPTION WHEN unique_violation THEN
            UPDATE subscriptions.statistics_active_count  SET active_count = active_count + 1
            WHERE service_id = NEW.service_id and operator_id=NEW.operator_id;
         END;
      ELSE
         tai_actions_for_active_count := -1;
         WITH upd AS
         (UPDATE subscriptions.statistics_active_count
         SET active_count = active_count - 1
         WHERE service_id = NEW.service_id AND operator_id = NEW.operator_id AND isnn = l_isnn;
         RETURNING active_count)
         SELECT *
         FROM upd INTO tai_actions_for_active_count;
         IF tai_actions_for_active_count = 0 THEN
            DELETE FROM public.statistics_active_count
            WHERE active_count = 0;
         END IF;
      END IF;
   END IF;
END;

关于mysql - 将 MySQL 触发器重写为 Postgres，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31920117/