Re: Multiple records returned by a JOIN - Mailing list pgsql-general

From Alexander Farber
Subject Re: Multiple records returned by a JOIN
Date
Msg-id CAADeyWj-m5OYMXwWs3Z5AJg2qBzrLA=OfeS8rAAnptNPjGGaHw@mail.gmail.com
Whole thread Raw
In response to Re: Multiple records returned by a JOIN  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Last night I have inexplicably missed 2 conditions /facepalm

Now my JOIN works ok, without multiple records -

CREATE OR REPLACE FUNCTION words_stat_games(
                in_social integer,
                in_sid    text
        ) RETURNS TABLE (
                out_gid    integer,
                out_reason text,
                out_state1 text,
                out_score1 integer,
                out_score2 integer
        ) AS
$func$
        SELECT
                g.gid,
                g.reason,
                CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
                CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
                CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
        FROM    words_games g
        JOIN    words_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
        WHERE   g.finished IS NOT NULL
        AND     s.social = in_social                          -- MISSED CONDITION
        AND     s.sid = in_sid                                   -- MISSED CONDITION
        ORDER BY g.finished DESC
        LIMIT   10;

$func$ LANGUAGE sql STABLE;

pgsql-general by date:

Previous
From: Raghavendra Rao J S V
Date:
Subject: Re: Planning to change autovacuum_vacuum_scale_factor value to zero.Please suggest me if any negative impact.
Next
From: Laurenz Albe
Date:
Subject: Re: Planning to change autovacuum_vacuum_scale_factor value tozero. Please suggest me if any negative impact.