PostgreSQL error: set-returning functions are not allowed in CASE

Question

I tried to run this query in PostgreSQL 10:

select e.errordescription,
       CASE 
        WHEN e.reworkempid is not null THEN get_empname(e.reworkempid) 
        else null 
      end  
from error_log_gs  e 
where e.qcworkpackageid=3012175 and e.logno=1 

Got the error:

set-returning functions are not allowed in CASE

Toponym

Case expression, not statement. – Sankey

Error seems pretty clear to me, what is your question? – Squaw

Actually the question is kind of self-evident, much more so than the cryptic error message. It really isn't clear why CASE returns a set here when in other use-cases it seems like it is a row-by-row operation (eg. every single CASE tutorial you come across in a search). Fortunately @linoff understood that. – Mythical

Answer 1

Use a lateral join instead:

select e.errordescription, ge.name
from error_log_gs e left join lateral
     get_empname(e.reworkempid) ge(name)
     on e.reworkempid is not null
where e.qcworkpackageid = 3012175 and e.logno = 1 ;

Habitue

Answer 2

The exact error I was facing was, 0A000: set-returning functions are not allowed in CASE.

While investigating, found that the syntax works in Postgres version 9.6 but not in 11.

To overcome the problem, I had a work around of adding one more CTE (Common Table Expression) as below which solved my problem.

cte as  
( 
select string_to_array("StartDate", ',') as "S1_StartDate", 
       string_to_array("EndDate", ',') as "S1_EndDate", 
       case  when "FlatDisc" is  null then '{0}' when "FlatDisc" ='' then '{0}' else string_to_array("FlatDisc", ',') end as "S1_FlatDisc" 
       from TABLE_XYZ 
) 
,cte2 as 
( 
select unnest("S1_StartDate") as "S_StartDate", 
       unnest("S1_EndDate") as "S_EndDate", 
       unnest("S1_FlatDisc") as "S_FlatDisc" 
       from cte 
)
select * from cte2

Skutchan