Displaying chat messages of punished users only to themselves-CodePudding

I have prepared

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

CodePudding user response：

I understand correctly, you can try to use EXISTS subquery to judgment mute user in words_get_chat function by your logic.

if nice user we need to judgment which message they want to see.
if bad user we can show all of message.

The function might look like as below.

CREATE OR REPLACE FUNCTION words_get_chat(
        in_gid    integer,
        in_social integer,
        in_sid    text
) RETURNS TABLE (
        out_msg   text
) AS
$func$
-- TODO display messages by muted users only to themselves
SELECT
        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
AND     EXISTS (
       SELECT 1
       FROM words_users wu
       WHERE wu.uid = s.uid AND 
       ((muted = true) OR (muted = false AND c.uid = s.uid))
)
ORDER BY c.CREATED ASC;

sqlfiddle

CodePudding user response：

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
            LIMIT 1
        )
        SELECT
                CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END,
                c.msg
        FROM    myself
        JOIN    words_chat c 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;