Good evening,
When I try simply wrapping the jsonb_agg() around the 3 columns in the first query I get the syntax error:
Thank you for any hints
Alex
In 13.2 I have 3 SQL queries, which work well and return integer values.
The values I feed to Google Charts (and currently I switch to Chart.js).
The values I feed to Google Charts (and currently I switch to Chart.js).
Currently I use the queries by calling 3 different custom stored functions by my Java servlet.
I would like to convert the functions to 1 function, in SQL or if not possible, then PL/pgSQL.
The new function should return a JSONB list containing 3 other lists, i.e. something like:
[ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ]
[ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ]
I think I should use the aggregate function jsonb_agg().
But I can't figure out how to apply it to the 3 queries below, could you please help me?
CREATE OR REPLACE FUNCTION words_stat_charts(
in_uid integer,
in_opponent integer
) RETURNS jsonb AS
$func$
-- how to return [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] ?
SELECT
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);
SELECT
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)
);
SELECT
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
FROM words_scores WHERE uid = in_uid;
$func$ LANGUAGE sql STABLE;
When I try simply wrapping the jsonb_agg() around the 3 columns in the first query I get the syntax error:
SELECT
JSONB_AGG(
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);
ERROR: function jsonb_agg(integer, integer, integer) does not exist
LINE 8: JSONB_AGG(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Alex
