PostgreSQL函数中如何通过参数动态控制ORDER BY排序逻辑？

我有如下可正常运行的PostgreSQL SQL函数：

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
 ORDER BY p.is_mattress DESC, p.sale, p.regular
  
$function$;

我希望根据传入的_sort_by参数值来控制排序逻辑，具体规则如下：

• 当_sort_by = 'DEFAULT'时，排序规则为 p.is_mattress DESC, p.sale, p.regular
• 当_sort_by = 'PRICE_LOW_TO_HIGH'时，排序规则为 COALESCE(p.sale, p.regular) ASC
• 当_sort_by = 'PRICE_HIGH_TO_LOW'时，排序规则为 COALESCE(p.sale, p.regular) DESC

我尝试用CASE语句实现该逻辑，但触发了语法错误，错误信息如下：

ERROR: syntax error at or near "DESC"
LINE 68: WHEN 'DEFAULT' THEN p.is_mattress DESC, p.sale, p.re...

尝试的错误代码：

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
 ORDER BY 
    CASE _sort_by
        WHEN 'DEFAULT' THEN p.is_mattress DESC, p.sale, p.regular
        WHEN 'PRICE_LOW_TO_HIGH' THEN COALESCE(p.sale, p.regular) ASC
        WHEN 'PRICE_HIGH_TO_LOW' THEN COALESCE(p.sale, p.regular) DESC
    END
$function$;

经过调试，我找到了可行的解决方案，代码如下：

CREATE OR REPLACE FUNCTION public.get_products(_sort_by TEXT DEFAULT 'DEFAULT'::TEXT)
 RETURNS SETOF products
 LANGUAGE sql
 STABLE
AS $function$
SELECT
  p.*
from
  products p
  ORDER BY 
   CASE WHEN _sort_by = 'PRICE_LOW_TO_HIGH' THEN COALESCE(p.sale, p.regular) END ASC,
   CASE WHEN _sort_by = 'PRICE_HIGH_TO_LOW' THEN COALESCE(p.sale, p.regular) END DESC,
   CASE WHEN _sort_by = 'BRAND_A_TO_Z' THEN b.name END ASC,
   CASE WHEN _sort_by = 'BRAND_Z_TO_A' THEN b.name END DESC,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.is_mattress END DESC,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.sale END,
   CASE WHEN _sort_by = 'DEFAULT' THEN p.regular END
$function$;

内容的提问来源于stack exchange，提问作者Daniel