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.
Optimize for performance second. I would move the test regarding muted to a where clause
I'm not understanding how a given user can see anything but their own messages where you have the condition s.social = in_social.
Assuming the base query is capable of returning all related chat messages for both users (I'd probably place that portion into a CTE) the rows you want to filter out are those whose c.uid is not my own, but only if their muted property is true. It makes it easier to understand if you join words_users twice, defining one as "them" and one as "me". Then you can say something like: WHERE (c.uid = me.uid) OR NOT(them.muted)
Me: u.uid in (player...) and (s.uid = u.uid)
Them: u.uid in (player...) and (s.uid <> u.uid)
Hopefully you get the idea, your "social" dynamic makes this more challenging. If you can just pass "my uid" into the function then figuring out which uid is "me" and which is "not me" becomes quite a bit easier.
David J.