I have a table as such:
CREATE TABLE users (id, ...)
CREATE TABLE groups (id, ...)
CREATE TABLE group_roles (group_id REFERENCES pages.id, role, ...)
CREATE TABLE user_memberships (
user_id REFERENCES users.id,
group_role_id REFERENCES group_roles.id,
)
I have a bunch of (user_id, group_id) tuples that I need to pass into Postgres, through an ORM (Sequelize), and then I need postgres to delete the user_memberships rows that correspond to the (user_id, group_id) pairs I passed in. I'd also like all of this to happen in one query.
I'd like to write the following code:
DELETE FROM user_memberships
WHERE (
(
user_id = :user_id_n
AND group_role_id IN (
SELECT id FROM group_roles WHERE group_id = :group_id_n
)
)
)
where :user_id_n and :group_id_n would be the nth elements of an array of (user_id, group_id) tuples. But this is not valid pgpsql. What would valid pgpsql look like?
Your table definitions are a bit off but there is nothing about the delete on its own that won't work:
CREATE TABLE users (id int primary key);
CREATE TABLE "groups" (id int primary key);
CREATE TABLE pages (id int primary key);
CREATE TABLE group_roles (
id int PRIMARY KEY,
group_id int REFERENCES "groups"(id));
CREATE TABLE user_memberships (
user_id int REFERENCES users(id),
group_role_id int REFERENCES group_roles(id)
);
insert into users values (1),(2);
insert into "groups" values (1),(2);
insert into group_roles values (1,1),(2,2);
insert into user_memberships values (1,1),(2,2);
That DELETE is perfectly valid, pretty much exactly how you typed it: demo at db<>fiddle
DELETE FROM user_memberships
WHERE user_id = 1
AND group_role_id IN
( SELECT id
FROM group_roles
WHERE group_id = 1 );
It can work fine with the whole array in it, too:
DELETE FROM user_memberships
USING generate_series(1,array_length(ARRAY[[1,1],[2,2]],1))as a(n)
WHERE user_id = (ARRAY[[1,1],[2,2]])[n][1]
AND group_role_id IN
( SELECT id
FROM group_roles
WHERE group_id = (ARRAY[[1,1],[2,2]])[n][2] )
RETURNING *;