postgresql - 如何在不使用函数的情况下使用 string_agg 更新值？

Question

我用一个函数解决了这个问题，我喜欢我的解决方案，但我想知道是否有一种方法可以不使用函数来解决这个问题。事情是这样的:

有四个表与此相关:

1. entities:系统的实体(租户)
2. members:一个实体的成员
3. member_sets:成员集合
4. members_and_sets:连接成员和集合的表(多对多)

member_sets 表有一个名为 bits 的列，它是集合的二进制表示，例如，如果一个实体有 5 个成员，一个特定的集合有第三个成员，bits列的值为00100，实体有三种特殊的集合:universe、empty和unit，它们的二进制表示为: 11111、00000 和 10000，假设单位集有第一个成员。

面临的挑战是让这个集合的二进制表示保持最新；每当将一个成员添加到实体时，必须更新所有二进制表示。使用触发器和函数很容易做到这一点，我的解决方案是这样的:

CREATE FUNCTION setbits(INTEGER) RETURNS VARBIT AS
$$SELECT STRING_AGG(belongs, '')::VARBIT AS setbits FROM (
  SELECT LEAST(COALESCE(members_and_sets.set_id, 0), 1)::text AS belongs
  FROM  members LEFT JOIN members_and_sets
  ON members.id=members_and_sets.member_id
  AND members_and_sets.set_id=$1
  GROUP BY members.id,members_and_sets.set_id
  ORDER BY members.id)
AS bitsring;$$
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;

-- calling this function in a trigger after inserting a new member:

UPDATE member_sets ms
SET bits=setbits(ms.id)
WHERE ms.entity_id=NEW.entity_id;

现在我的问题是:我可以在不使用函数的情况下做到这一点吗？我尝试使用 CTE，但显然我是菜鸟，无法做到这一点；我无法将 set_id 传递给必须的内部查询，因此我的解决方案是将查询包装在一个函数中，并将 set_id 作为参数传递给该函数。同样，这个解决方案非常有效，我只是想知道如果没有函数调用我是否无法做到这一点。

Answer 1

由于您的函数体只是一个SELECT，您应该能够用子查询替换函数调用:

UPDATE member_sets ms
SET bits= (
  SELECT STRING_AGG(belongs, '')::VARBIT AS setbits FROM (
    SELECT LEAST(COALESCE(members_and_sets.set_id, 0), 1)::text AS belongs
    FROM  members LEFT JOIN members_and_sets
    ON members.id=members_and_sets.member_id
    AND members_and_sets.set_id=ms.id
    GROUP BY members.id,members_and_sets.set_id
    ORDER BY members.id)
  AS bitsring
)
WHERE ms.entity_id=NEW.entity_id;