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?
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
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)