Hello, I have developed a complete SQL fiddle for my question:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dcf063ba1615b392cc3cfa347a32c97b The context is that I run an online game for two players using a PostgreSQL 14.2 backend. I would like to make my game more friendly by hiding chat messages of misbehaving users. However, to prevent the punished users from noticing it and registering new game accounts, I would like to still show them all messages :-> So here are the 4 tables used in my reduced test case: CREATE TABLE words_users ( uid SERIAL PRIMARY KEY, muted BOOLEAN NOT NULL DEFAULT false ); CREATE TABLE words_social ( sid text NOT NULL CHECK (sid ~ '\S'), 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 put 2 users into the game #10 and they start chatting: -- 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 #10 INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2); -- both users in the game #10 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??'); Here is my custom stored function (in SQL, I would prefer not to switch to PL/pgSQL): 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$ 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_users u ON (u.uid IN (g.player1, g.player2) -- The condition below is broken if both users are not muted AND (u.muted OR (c.uid = u.uid AND NOT u.muted))) JOIN words_social s ON (s.uid = u.uid) WHERE c.gid = in_gid AND s.social = in_social AND s.sid = in_sid ORDER BY c.CREATED ASC; $func$ LANGUAGE sql; For a chat of a bad and a nice user it seemingly works: SELECT words_get_chat(10, 100, 'abc') AS nice_user; SELECT words_get_chat(10, 200, 'def') AS muted_user; But if you change both users to be not muted - it will break and they only will see their own messages. I have tinkered a lot with my db fiddle... but still cannot figure it out Thank you! Alex