Insert rows based on array of UUIDs

Question

Looking for a way to insert a list of records based on an array of UUIDs. Here's my example code:

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;

Obviously doesn't work, lol.

I want to be able to call:

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[]
);

As a side note I have a unique key constraint that ensures only one record for a UserID and GroupID every exist. If the second array value breaks that rule will the whole query fail and how can I ignore it to ensure the rest of the values get inserted?

Clodoaldo Neto · Accepted Answer · 2017-04-20T17:25:37.347

Use unnest and plain sql in instead of plpgsql. With this table:

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

This function will insert non existent:

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;

Usage:

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

Great thanks! I picked your answer because you accounted for the error checking in the solution ;-) — ddibiase, Apr 20 '17 at 17:59
I'm going to give this a shot with deleting users, will respond if I have trouble :-p seems pretty straightforward though. — ddibiase, Apr 20 '17 at 18:09

score 1 · Answer 2 · edited May 23 '17 at 12:17

Based on this answer and the official documentation, you could declare a variable to store each user ID, like this:

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;

And to actually call it:

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[]
);

(Note the square brackets instead of parenthesis)

Insert rows based on array of UUIDs

2 Answers2