I have a table USER with 3 columns ID, Flag (boolean), and partnerID.
What I want to achieve is that each ID is randomly inserted into the partnerID column. The following rules should be followed:
- If Flag has the value false, NULL should be set here.
- PartnerID should be unique. It should not appear in the column again.
- If ID[5] is already assigned to ID[6], they are a pair. This means that in this case, ID[6] automatically gets ID[5] as partner.
- If there is an odd number of users, the remaining user should get the value NULL.
I really want to do this with plpgsql, so I can run it as a CRON job in Supabase. But is it even possible?
My Idea is to create a new table, do the matching there and return the result to the user table, than delete the temporary table.