From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Same condition in the CTE and in the subsequent JOIN using it |
Date: | 2018-04-26 11:00:14 |
Message-ID: | CAADeyWhZmaY9PrSgw1q0V2aRR-AqKecyya5vjzGsnuFn6DXJzQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2018-04-26 11:01:22 | Re: Same condition in the CTE and in the subsequent JOIN using it |
Previous Message | Thomas Poty | 2018-04-26 08:30:23 | Re: Issue on public schéma with Pg_restore |