Hello, thank you for the helpful replies.
The function works well:
I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists.
And the custom stored function below works mostly well, except for a special case -
CREATE OR REPLACE FUNCTION words_stat_charts(
in_uid integer,
in_opponent integer, -- optional parameter, can be NULL
OUT out_data jsonb
) RETURNS jsonb AS
$func$
BEGIN
out_data := JSONB_BUILD_OBJECT();
-- add a JSON list with 7 integers
out_data := JSONB_INSERT(out_data, '{length}', JSONB_BUILD_ARRAY(
SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer
))
-- add a JSON list with 3 integers
FROM words_scores WHERE uid = in_uid;
out_data := JSONB_INSERT(out_data, '{results}', JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);
-- add a JSON list with 3 integers, but only if in_opponent param is supplied
IF in_opponent > 0 AND in_opponent <> in_uid THEN
out_data := JSONB_INSERT(out_data, '{versus}', JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
END IF;
END
$func$ LANGUAGE plpgsql;
The function works well:
# select * from words_stat_charts(5, 6);
out_data
---------------------------------------------------------------------------------------------------
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [6, 3, 0], "results": [298, 151, 0]}
(1 row)
Except when 2 players never played with each other - then I get [ null, null, null ]:
# select * from words_stat_charts(5, 1);
out_data
------------------------------------------------------------------------------------------------------------
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [null, null, null], "results": [298, 151, 0]}
(1 row)
Is there maybe a nice trick to completely omit "versus" from the returned JSONB map of lists when its [ null, null, null ]?
Thank you
Alex
