You might be able to move the set-returning function into a LATERAL FROM item.

[Alexander Farber]

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