> I am trying to create the following strored function based on your suggestion (and I have forgotten to mention, that
Ialso 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 = 'play' 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,
> ^
>
Use ->> to return the value as text (not as JSONB) and you need to use the column alias, not the table alias:
(t.tile ->> 'col')::int