Re: How to return a jsonb list of lists (with integers)

[Pavel Stehule]

Hi

st 17. 2. 2021 v 11:40 odesílatel Alexander Farber <
alexander.far...@gmail.com> napsal:

> I have tried the following, but IF FOUND is always false for some reason:
>
>                 _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)
>                 );
>

Don't do this. When you want to use a query, then use SELECT INTO.

Regards

Pavel



>                 IF FOUND THEN
>                         out_data := JSONB_INSERT(out_data, '{versus}',
> _versus);
>                 END IF;
>
>