sqlpostgresqllateral

Upgrading to PostgreSQL 11: set-returning functions are not allowed in CASE

The following query stopped working when upgrading from PostgreSQL 9.6 to 11:

with doc as (select * from documents where name = doc_id)

select jsonb_array_elements_text(permissions)
from users
where users.name = user_name

union

select 
  case 
    when doc.reader = user_name then 'read'
    when doc.owner = user_name then unnest(array['read','write'])
    else unnest(array[]::text[])
    end 
from doc;

The union as usual puts together two lists of values, both lists can have zero, one or more elements.

The first select can return zero, one or more just because that's what's in the users table.

The second select always scans one row from the documents table, but returns zero, one or more rows depending on what the case decides.

PostgreSQL 9.6 was working as expected, PostgreSQL 11 says:

ERROR:  set-returning functions are not allowed in CASE
LINE 56:    else unnest(array[]::text[])
                 ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.

I appreciate the advice, but I can't figure out how to use a LATERAL FROM here.

Solution

  • The hint is a bit misleading here. As it says, adding lateral joins to your set-returning functions might help (in general), but I don't think it makes much sense in your case.

    You can easily work around this by changing the CASE expression to return an array, and then unnesting the result:

    ...
select 
  unnest(
    case 
      when doc.reader = user_name then array['read']
      when doc.owner = user_name then array['read','write']
      else array[]::text[]
    end
  )
from doc;