postgresqlpostgres-9.6postgres-10

set returning functions are not allowed in CASE, in Postgres 10.6


sql is not my strong point

upgrading from postgres 9.6 to 10.6 and I get an error from this query:

SELECT id, (CASE WHEN jsonb_typeof(content->'user')='array' THEN jsonb_array_elements(content -> 'user')
         ELSE content::jsonb->'user' END) As att FROM event;

error: set returning functions are not allowed in CASE

result i get back (from v 9.6):

id, att
1, {"name": "Andrew"},
2, {"name": "Stacey"},
3, null

the 'user' element can also be null.

This is what a 'content' column could look like (either one of the two)

{"user": [{"name": "Andrew", "country_code": "GBR"}]]
{"user": null}

any help pleas been stuck on this for a while


Solution

  • I guess you could use

    SELECT id, att
    FROM event,
    jsonb_array_elements(CASE WHEN jsonb_typeof(content->'user')='array'
      THEN content -> 'user'
      ELSE jsonb_build_array(content->'user')
    END) AS user(att);