I am writing case statement in a materialized view as below
CASE
WHEN ((length(comp_serv_view.services_rolled_id) > 0) AND (length(comp_serv_view.company_service_id) > 0)) THEN string_to_array(concat(comp_serv_view.services_rolled_id, ',', comp_serv_view.company_service_id),','::text)
WHEN ((length(comp_serv_view.services_rolled_id) > 0) AND ((length(comp_serv_view.company_service_id) = 0) OR (comp_serv_view.company_service_id IS NULL))) THEN string_to_array(comp_serv_view.services_rolled_id,','::text)
WHEN (((length(comp_serv_view.services_rolled_id) = 0) OR (comp_serv_view.services_rolled_id IS NULL)) AND (length(comp_serv_view.company_service_id) > 0)) THEN string_to_array(comp_serv_view.company_service_id,','::text)
ELSE ''::text
END AS top_3_company_service_id,
However i am getting below error
ERROR: CASE types text and text[] cannot be matched
LINE 22: ...gth(comp_serv_view.company_service_id) > 0)) THEN string_to_...
^
SQL state: 42804
Character: 1964
can someone help me to understand what has gone wrong here? I have converted each case to text but getting the error also will be helpful if you can give example of how to use string to array with a concat statement.