Thank you for the insightful comments.
Actually in my specific case I have managed to get rid of the (almost) same
condition in the outer query:
CREATE OR REPLACE FUNCTION words_stat_scores_2(
in_social integer,
in_sid text
) RETURNS TABLE (
out_day text,
out_diff numeric,
out_score numeric
) AS
$func$
WITH filtered_moves AS (
SELECT
m.uid,
s.uid AS web_script_viewer,
DATE_TRUNC('day', m.played) AS day,
m.mid,
EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
(PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid)
JOIN words_social s ON (s.uid IN (g.player1, g.player2))
WHERE s.social = in_social
AND s.sid = in_sid
AND m.played > CURRENT_TIMESTAMP - interval '2 month'
)
SELECT
TO_CHAR(f.day, 'DD.MM.YYYY'),
ROUND(AVG(f.diff)),
ROUND(AVG(m.score), 1)
FROM words_moves m
JOIN filtered_moves f using(mid)
WHERE f.uid = f.web_script_viewer -- INSTEAD OF DOING JOIN
ON words_social AGAIN
AND m.action = 'play'
GROUP BY f.day
ORDER BY f.day;
$func$ LANGUAGE sql STABLE;
The "big picture" of my database is that every player data can be referred
by the numeric "uid" (user id).
But when a user comes though a web script, then he must first authenticate
through words_social table, I can trust him just giving me some "uid".
(I suppose many databases have similar "authentication" table, storing
usernames/passwords)
And thus my question is about how to only authenticate once - and then
carry this result through several CTEs.
Regards
Alex
