Displaying chat messages of punished users only to themselves

Question

I have prepared a simple test case for my question.

In an online word game for 2 players based on PostgreSQL 14.2, when someone is misbehaving I set their "muted" column to "true".

Then the chat messages coming from the punished users should be hidden to everyone else.

Except for the punished users themselves 😼 - they should see all the chat messages, so that they do not notice that they are muted and do not create new game accounts.

I already do this trick for their avatars 😜

So I have prepared a simple test case, here are my 4 tables:

CREATE TABLE words_users (
    uid SERIAL PRIMARY KEY,
    muted BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE words_social (
    -- social network id
    sid     text     NOT NULL CHECK (sid ~ '\S'),
    -- social network type: 100 = Facebook, 200 = Google, etc.
    social  integer  NOT NULL CHECK (0 < social AND social <= 256),
    given   text     NOT NULL CHECK (given ~ '\S'),
    uid     integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
    PRIMARY KEY(sid, social)
);

CREATE TABLE words_games (
    gid      SERIAL PRIMARY KEY,
    player1  integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL CHECK (player1 <> player2),
    player2  integer REFERENCES words_users(uid) ON DELETE CASCADE
);

CREATE TABLE words_chat (
    cid     BIGSERIAL PRIMARY KEY,
    created timestamptz NOT NULL,
    gid     integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
    uid     integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
    msg     text    NOT NULL
);

Then I fill the tables with the test data:

-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100, 'Nice user', 1), ('def', 200, 'Bad user', 2);

-- put these 2 users into a game number 10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);

-- start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');

And finally here is the SQL function that I am trying to improve:

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid    integer,
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_mine  integer,
                out_msg   text
        ) AS
$func$
        -- TODO display messages by muted users only to themselves
        SELECT
                CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c 
        JOIN    words_games g USING (gid) 
        JOIN    words_social s ON s.uid IN (g.player1, g.player2)
        WHERE   c.gid    = in_gid
        AND     s.social = in_social
        AND     s.sid    = in_sid
        ORDER BY c.CREATED ASC;

$func$ LANGUAGE sql;

SELECT words_get_chat(10, 100, 'abc') AS nice_user;

SELECT words_get_chat(10, 200, 'def') AS muted_user;

Currently the stored function displays all chat messages, but I would like to hide messages coming from muted players for everyone else (shown by red lines in the screenshot below):

Please help me to improve my SQL function and note that I do not want to switch to PL/pgSQL for performance reasons.

Answer 1

Inspired by Daniel's sub query answer and help at the PostgreSQL mailing list, I have developed a CTE and JOIN solution:

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid    integer,
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_mine  integer,
                out_msg   text
        ) AS
$func$
        WITH myself AS (
            SELECT uid 
            FROM words_social
            WHERE social = in_social
            AND sid = in_sid
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c
        JOIN    myself ON TRUE
        JOIN    words_games g USING (gid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     (c.uid = myself.uid 
        -- otherwise only show messages by not muted opponents
                 OR NOT opponent.muted)
        ORDER BY c.created ASC;

$func$ LANGUAGE sql;

Also I received a nice recommendation to separate auth logic from my game's logic:

CREATE OR REPLACE FUNCTION words_get_uid(
                in_social integer,
                in_sid    text
        ) RETURNS integer AS
$func$
        SELECT uid 
        FROM words_social
        WHERE social = in_social
        AND sid = in_sid;
$func$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION words_get_chat(
                in_gid   integer,
                in_uid   integer
        ) RETURNS TABLE (
                out_mine integer,
                out_msg  text
        ) AS
$func$
        SELECT
                CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END,
                c.msg
        FROM    words_chat c
        JOIN    words_games g USING (gid) 
        JOIN    words_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid)
        WHERE   c.gid = in_gid
        -- always show myself my own chat messages
        AND     (c.uid = in_uid 
        -- otherwise only show messages by not muted opponents
                 OR NOT opponent.muted)
        ORDER BY c.created ASC;

$func$ LANGUAGE sql;

SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user;

SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user;