making sure only an existing user in the table can add a record in PostgreSQL

Question

I have something like fb groups, where people can be moderators. If a user wants to add a moderator to a certain group, I'd like to check that first of all he himself is a moderator of this group.

currently the table looks like this :

CREATE TABLE fb_groups_moderators (
    fb_group_id int,
    user_id  bigint,
    
    PRIMARY KEY (fb_group_id, user_id),
    FOREIGN KEY (fb_group_id) references fb_groups (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) references users (id) ON DELETE CASCADE
);

So I can't just do :

INSERT INTO fb_group_moderators (fb_group_id, user_id) VALUES (11, 123);

I need check that the user who called this query, is in fact also a moderator of fb_group_id = 11, only then can he add user 123 to be a moderator to this group.

Answer 1

You need to pass the id of the user that is asking for the insert as a parameter to the query. Then, you can use the INSERT ... SELECT syntax.

Consider:

INSERT INTO fb_group_moderators (fb_group_id, user_id) 
SELECT v.fb_group_id, v.user_id
FROM (VALUES (11, 123, ?)) v(fb_group_id, user_id, adding_user_id)
INNER JOIN fb_group_moderators m
    ON  m.user_id = v.adding_user_id
    AND m.fb_group_id = v.fb_group_id

The question mark represents the query parameter (the user that is requesting the insert), which is then aliased adding_user_id in the query.