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

Alexander Farber wrote:
> 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(
> [...] AS
> $func$
> [...]
> CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x
> [...]
> $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?

The problem is that "jsonb_array_elements" returns several rows, which does not
make sense in this context. Which of the rows do you want?

If you know that it will always return at most one row, you could use:

... THEN (SELECT jae FROM jsonb_array_elements(tiles) jae LIMIT 1)

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com