Thank you for replying, David!
The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries.
Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats by other users, by just replacing the numeric "uid"...
So I try your suggestion with:
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 u1 ON (u1.uid = g.player1)
JOIN words_users u2 ON (u2.uid = g.player2)
JOIN words_social s ON (s.uid IN (u1.uid, u2.uid))
WHERE c.gid = in_gid
AND s.social = in_social
AND s.sid = in_sid
ORDER BY c.CREATED ASC;
$func$ LANGUAGE sql;
...but how to bring the u1.muted or u2.muted there?
Best regards
Alex
