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-20 17:25:37Z

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 ;-)
I'm going to give this a shot with deleting users, will respond if I have trouble :-p seems pretty straightforward though.

Community · Accepted Answer · 2017-05-23 12:17:49Z

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)

Collectives™ on Stack Overflow

Insert rows based on array of UUIDs

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related