保证“`RETURNING`”从重新插入，同时限制存储的数据

Question

我有下表：

CREATE TABLE scoped_data(
  owner_id text,
  scope text
  key text,
  data json,
  PRIMARY KEY (owner_id, scope, key)
);

作为每个事务的一部分，我们可能会为多个作用域插入数据。考虑到这个表具有快速增长的潜力，如果它是data或空JSON对象，我不想存储它。

一个人觉得这是一种惯用的方法。以下内容位于PL/pgSQL函数的上下文中：

WITH upserts AS (
  INSERT INTO scoped_data (owner_id, scope, key, data)
    VALUES
      (p_owner_id, 'broad', p_broad_key, p_broad_data),
      (p_owner_id, 'narrow', p_narrow_key, p_narrow_data),
      -- etc.
    ON CONFLICT (owner_id, scope, key)
      DO UPDATE SET data = scoped_data.data || COALESCE(EXCLUDED.data, '{}')
    RETURNING scope, data
)
SELECT json_object_agg(u.scope, u.data)
  FROM upserts u
  INTO v_all_scoped_data;

我将RETURNING包括在内，因为我希望包含在变量中的每个作用域数据的最新版本供以后使用，因此我需要RETURNING返回一些内容，即使逻辑上没有更新任何数据。

例如(全部用于key = 1 and scope = 'narrow')：

1. data = '{}' => v_scoped_data = {}，没有key = 1在scoped_data中的数据。
2. data = '{"some":"data"}' => v_scoped_data = { "narrow": { "some": "data" } }，以scoped_data表示的数据。
3. data = '{}' => v_scoped_data = { "narrow": { "some": "data" }，来自2的数据仍未受影响。
4. data = '{"more":"stuff"}' => v_scoped_data = { "narrow": { "some": "data", "more": "stuff" }.表中存储的更新数据。

我最初添加了一个触发器BEFORE INSERT ON scoped_data，它执行了以下操作：

IF NULLIF(NEW.data, '{}') IS NULL THEN
  RETURN NULL;
END IF;

RETURN NEW;

这样可以很好地防止插入新记录，但问题是该触发器还阻止了对现有行的后续插入，因此没有发生INSERT，因此没有ON CONFLICT，因此RETURNING中没有返回任何内容。

我已经考虑过几种方法，这两种方法都觉得不雅致，或者认为它们应该是不必要的：

• 将CHECK约束添加到scoped_data.data：CHECK(NULLIF(data, '{}') IS NOT NULL)中，允许插入并捕获PL/pgSQL代码中的异常。
• 如果DELETE字段为NULL或空，则AFTER INSERT触发器中的data。

我这样做是正确的吗？当有更好的方法时，我是否试图强迫这种逻辑重新插入？显式的INSERT和UPDATE是否更符合逻辑？

我使用的是Postgres 9.6。

Answer 1

我将使用BEFORE触发器ON INSERT来防止不必要的插入和更新。

若要返回值，即使在未执行操作的情况下，也可以使用返回原始行的UNION ALL查询对查询进行ORDER，以便首先排序任何新行(向这两个查询引入人工列)，并使用LIMIT 1获得正确的结果。