根据UUID数组插入行

Question

寻找一种基于UUID数组的记录列表的方法。这是我的示例代码：

CREATE OR REPLACE FUNCTION "AddGroupUsers" (
  "@OrganizationID" UUID,
  "@GroupID"        UUID,
  "@UserIDs"        UUID[]
)
RETURNS viud AS
$func$
BEGIN
  FOR index IN "@UserIDs" LOOP
    INSERT INTO
      "UserGroups" (
        "UserID",
        "GroupID",
        "OrganizationID"
      )
    VALUES (
      "@UserID"[index],
      "@GroupID",
      "@OrganizationID"
    );
  END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

显然不起作用，大声笑。

我希望能够致电：

SELECT "AddGroupUsers"(
  'cb6e96db-73db-4b07-811f-c54b61d09fa4',
  '451a9ab7-02f6-4f63-bb87-80ad531ab490'
  array(
    '451a9ab7-02f6-4f63-bb87-80ad531ab490',
    '451a9ab7-02f6-4f63-bb87-80ad531ab491',
    '451a9ab7-02f6-4f63-bb87-80ad531ab492',
    '451a9ab7-02f6-4f63-bb87-80ad531ab493',
    '451a9ab7-02f6-4f63-bb87-80ad531ab494'
  )::uuid[]
);

作为旁注，我有一个唯一的密钥约束，可确保每个存在的用户ID和GroupID的记录。如果第二个数组值打破该规则，整个查询将失败，我如何忽略它以确保其余值插入？

Answer 1

使用unnest和Plain SQL而不是PLPGSQL。在此表中：

create table user_groups (
    org_id uuid, grp_id uuid, use_id uuid,
    unique (grp_id, use_id)
);

此功能将插入不存在：

create or replace function AddGroupUsers(
    _org_id uuid, _grp_id uuid, _use_id uuid[]
) returns setof user_groups as $$
    insert into user_groups (org_id, grp_id, use_id) 
    select s.org_id, grp_id, use_id
    from
        (
            select 
                _org_id as org_id, 
                _grp_id as grp_id, 
                unnest(_use_id) as use_id
        ) s
        left join
        user_groups ug using (grp_id, use_id)
    where ug.grp_id is null
    returning *
    ;
$$ language sql;

用法：

select *
from AddGroupUsers( 
    'cb6e96db-73db-4b07-811f-c54b61d09fa4'::uuid,
    '451a9ab7-02f6-4f63-bb87-80ad531ab490'::uuid,
    array[
        '451a9ab7-02f6-4f63-bb87-80ad531ab490',
        '451a9ab7-02f6-4f63-bb87-80ad531ab491',
        '451a9ab7-02f6-4f63-bb87-80ad531ab492',
        '451a9ab7-02f6-4f63-bb87-80ad531ab493',
        '451a9ab7-02f6-4f63-bb87-80ad531ab494'
    ]::uuid[]
);
                org_id                |                grp_id                |                use_id                
--------------------------------------+--------------------------------------+--------------------------------------
 cb6e96db-73db-4b07-811f-c54b61d09fa4 | 451a9ab7-02f6-4f63-bb87-80ad531ab490 | 451a9ab7-02f6-4f63-bb87-80ad531ab490
 cb6e96db-73db-4b07-811f-c54b61d09fa4 | 451a9ab7-02f6-4f63-bb87-80ad531ab490 | 451a9ab7-02f6-4f63-bb87-80ad531ab491
 cb6e96db-73db-4b07-811f-c54b61d09fa4 | 451a9ab7-02f6-4f63-bb87-80ad531ab490 | 451a9ab7-02f6-4f63-bb87-80ad531ab492
 cb6e96db-73db-4b07-811f-c54b61d09fa4 | 451a9ab7-02f6-4f63-bb87-80ad531ab490 | 451a9ab7-02f6-4f63-bb87-80ad531ab493
 cb6e96db-73db-4b07-811f-c54b61d09fa4 | 451a9ab7-02f6-4f63-bb87-80ad531ab490 | 451a9ab7-02f6-4f63-bb87-80ad531ab494

Answer 2

基于此答案和官方文档，您可以declare存储每个用户ID的变量，例如：

CREATE OR REPLACE FUNCTION AddGroupUsers (
  "@OrganizationID" UUID,
  "@GroupID"        UUID,
  "@UserIDs"        UUID[]
)
RETURNS void AS
$func$
DECLARE uID UUID;
BEGIN
  FOREACH uID IN ARRAY "@UserIDs" LOOP
    INSERT INTO
      UserGroups (
        UserID,
        GroupID,
        OrganizationID
      )
    VALUES (
      uID,
      "@GroupID",
      "@OrganizationID"
    );
  END LOOP;
END;
$func$ LANGUAGE PLPGSQL;

并实际称其为：

SELECT AddGroupUsers(
  'cb6e96db-73db-4b07-811f-c54b61d09fa4'::uuid,
  '451a9ab7-02f6-4f63-bb87-80ad531ab490'::uuid,
  array[
    '451a9ab7-02f6-4f63-bb87-80ad531ab490',
    '451a9ab7-02f6-4f63-bb87-80ad531ab491',
    '451a9ab7-02f6-4f63-bb87-80ad531ab492',
    '451a9ab7-02f6-4f63-bb87-80ad531ab493',
    '451a9ab7-02f6-4f63-bb87-80ad531ab494'
  ]::uuid[]
);

(注意方括号而不是括号(