Thank you Thomas -
On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer <spam_eater@xxxxxxx> wrote:
Alexander Farber schrieb am 21.10.2019 um 15:39:
> I am trying to construct a query, which would draw a game board when given a move id (aka mid):
>
> SELECT
> hand,
> JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col,
> JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row,
> JSONB_ARRAY_ELEMENTS(tiles)->'letter' AS letter,
> JSONB_ARRAY_ELEMENTS(tiles)->'value' AS value
> FROM words_moves
> WHERE action = "" AND
> gid = (SELECT gid FROM words_moves WHERE mid = 391416)
> AND played <= (SELECT played FROM words_moves WHERE WHERE mid = 391416)
> ORDER BY played DESC
>
> The above query works for me and fetches all moves performed in a game id (aka gid) up to the move id 391416.
>
> 1. Is it okay to call JSONB_ARRAY_ELEMENTS four times in the query, will PostgreSQL optimize that to a single call?
Typically set returning functions should be used in the FROM clause, not the SELECT list:
SELECT
hand,
t.tile -> 'col' AS col,
t.tile -> 'row' AS row,
t.tile -> 'letter' AS letter,
t.tile -> 'value' AS value
FROM words_moves
cross join jsonb_array_elements(tiles) as t(tile)
WHERE action = ""
AND gid = (SELECT gid FROM words_moves WHERE mid = 391416)
AND played <= (SELECT played FROM words_moves WHERE WHERE mid = 391416)
ORDER BY played DESC
I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that I also need the board id aka bid from another table, words_games), but hit the next problem:
CREATE OR REPLACE FUNCTION words_get_move(
in_mid integer
) RETURNS TABLE (
out_bid integer,
out_mid bigint,
out_hand text,
out_col integer,
out_row integer,
out_letter text,
out_value integer
) AS
$func$
SELECT
g.bid,
m.mid,
m.hand,
(t->'col')::int AS col,
(t->'row')::int AS row,
(t->'letter')::text AS letter,
(t->'value')::int AS value
FROM words_moves m
CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile)
LEFT JOIN words_games g USING(gid)
WHERE m.action = "" AND
m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid)
AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid)
ORDER BY m.played DESC;
$func$ LANGUAGE sql;
in_mid integer
) RETURNS TABLE (
out_bid integer,
out_mid bigint,
out_hand text,
out_col integer,
out_row integer,
out_letter text,
out_value integer
) AS
$func$
SELECT
g.bid,
m.mid,
m.hand,
(t->'col')::int AS col,
(t->'row')::int AS row,
(t->'letter')::text AS letter,
(t->'value')::int AS value
FROM words_moves m
CROSS JOIN JSONB_ARRAY_ELEMENTS(m.tiles) AS t(tile)
LEFT JOIN words_games g USING(gid)
WHERE m.action = "" AND
m.gid = (SELECT gid FROM words_moves WHERE mid = in_mid)
AND m.played <= (SELECT played FROM words_moves WHERE mid = in_mid)
ORDER BY m.played DESC;
$func$ LANGUAGE sql;
words_ru=> \i src/slova/dict/words_get_move.sql
psql:src/slova/dict/words_get_move.sql:28: ERROR: cannot cast type jsonb to integer
LINE 17: (t->'col')::int AS col,
^
psql:src/slova/dict/words_get_move.sql:28: ERROR: cannot cast type jsonb to integer
LINE 17: (t->'col')::int AS col,
^
How to cast the col to integer here?
Thanks
Alex
