Good morning,
for a word game in PostgreSQL 10.3 I have a table with jsonb column "tiles".
The column holds either a JSON array of objects (word tiles played) or a string (of swapped letters).
I am trying to fetch a history/protocol of a game with:
CREATE OR REPLACE FUNCTION words_get_moves(
in_gid integer
) RETURNS TABLE (
out_action text,
out_letters text,
out_words text
) AS
$func$
WITH cte1 AS (
SELECT
mid,
action,
STRING_AGG(x->>'letter', '') AS tiles
FROM (
SELECT
mid,
action,
CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x
--JSONB_ARRAY_ELEMENTS(tiles) AS x
FROM words_moves
WHERE gid = in_gid
--AND JSONB_TYPEOF(tiles) = 'array'
) AS p
GROUP BY mid, action),
cte2 AS (
SELECT
mid,
STRING_AGG(y, ', ') AS words
FROM (
SELECT
mid,
FORMAT('%s (%s)', word, score) AS y
FROM words_scores
WHERE gid = in_gid
) AS q
GROUP BY mid)
SELECT
action,
tiles,
words
FROM cte1
LEFT JOIN cte2 using (mid)
ORDER BY mid ASC;
$func$ LANGUAGE sql;
However calling this stored function gives the error:
ERROR: 0A000: set-returning functions are not allowed in CASE
LINE 18: ... CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRA...
^
HINT: You might be able to move the set-returning function into a LATERAL FROM item.
I have read that PostgreSQL 10 handles SRF more strictly, but what does it want me to do here, to add 1 more table to the LEFT JOIN?
Thank you
Alex
