0

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.

D.Doe
  • 89
  • 2
  • 10

1 Answers1

3

I think it’s better to use trigger AFTER INSERT OR UPDATE that will select a row with ID equal to partnerID of the row being inserted/updated and update it by setting PartnerID of a found row to ID of a row being inserted/updated. Thus we’ll support a one-to-one correspondence. Then, having this trigger you can just update partnerID of existing rows (or insert new rows with partnerID) randomly or as you wish. Trigger will do all what is needed for one-to-one correspondence. Really I don’t see the need to do cron in this case. Everything could be implemented just in SQL. Of course both columns can be also declared with UNIQUE constraint.

https://www.postgresql.org/docs/current/sql-createtrigger.html

There is also a little bit different example to implement pair matching with mapping table instead of extra column

postgres - How to efficiently find if a matching pair exists for the given tables

So in short, yes, this can be implemented in Supabase quite easily.

Pavel Borisov
  • 31
  • 2