words_get_chat.sql

gistfile1.txt

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$
DECLARE
        _uid integer;
BEGIN
        _uid := (SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid);

        UPDATE words_games SET
                chat2 = 0
        WHERE   gid     = in_gid
        AND     player1 = _uid;

        IF NOT FOUND THEN
                UPDATE words_games SET
                        chat1 = 0
                WHERE   gid     = in_gid
                AND     player2 = _uid;
        END IF;

        IF NOT FOUND THEN
                RAISE EXCEPTION 'Game % not found for user % %', in_gid, in_social, in_sid;
        END IF;

        RETURN QUERY 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;
END
$func$ LANGUAGE plpgsql;