如何在SQL函数中使用标志控制查询结果的升序或降序排序？

要实现用order_direction控制排序方向，你需要修改函数里的ORDER BY部分，这里有两种可行方案：

方案1：基于原有类型转换（简单但存在排序逻辑风险） #

这种方式保留你原来把所有排序字段转成text的逻辑，只添加方向控制：

ORDER BY   
  CASE
    WHEN order_by_full_name THEN person."fullName" :: text
    WHEN order_by_email THEN person."primaryEmail" :: text
    WHEN order_by_deal_status THEN deal."dealStatusID" :: text
    WHEN order_by_loan_status THEN loan."loanStatusID" :: text
    WHEN order_by_created_at THEN deal."_createdAt" :: text
    ELSE deal."_lastUpdatedAt" :: text
  END
  -- false对应升序（ASC），true对应降序（DESC）
  CASE WHEN order_direction THEN DESC ELSE ASC END

⚠️ 注意：把数值型（如dealStatusID）、时间型（如_createdAt）转成text后，排序会按字符串规则来——比如数值10会排在2前面，时间也会按字符串比较，这可能不符合你的预期。

方案2：正确处理数据类型的排序（推荐） #

为了保证各类型字段按原生规则排序（数值按大小、时间按先后），我们拆分排序条件，针对每个排序字段分别处理升序和降序：

ORDER BY
  -- 按fullName排序
  CASE WHEN order_by_full_name AND NOT order_direction THEN person."fullName" END ASC,
  CASE WHEN order_by_full_name AND order_direction THEN person."fullName" END DESC,
  -- 按primaryEmail排序
  CASE WHEN order_by_email AND NOT order_direction THEN person."primaryEmail" END ASC,
  CASE WHEN order_by_email AND order_direction THEN person."primaryEmail" END DESC,
  -- 按dealStatusID排序
  CASE WHEN order_by_deal_status AND NOT order_direction THEN deal."dealStatusID" END ASC,
  CASE WHEN order_by_deal_status AND order_direction THEN deal."dealStatusID" END DESC,
  -- 按loanStatusID排序
  CASE WHEN order_by_loan_status AND NOT order_direction THEN loan."loanStatusID" END ASC,
  CASE WHEN order_by_loan_status AND order_direction THEN loan."loanStatusID" END DESC,
  -- 按_createdAt排序
  CASE WHEN order_by_created_at AND NOT order_direction THEN deal."_createdAt" END ASC,
  CASE WHEN order_by_created_at AND order_direction THEN deal."_createdAt" END DESC,
  -- 默认按_lastUpdatedAt排序
  CASE WHEN NOT (order_by_full_name OR order_by_email OR order_by_deal_status OR order_by_loan_status OR order_by_created_at) AND NOT order_direction THEN deal."_lastUpdatedAt" END ASC,
  CASE WHEN NOT (order_by_full_name OR order_by_email OR order_by_deal_status OR order_by_loan_status OR order_by_created_at) AND order_direction THEN deal."_lastUpdatedAt" END DESC

完整修改后的函数（方案2） #

CREATE 
OR REPLACE FUNCTION public.search_deals(
  search text, 
  hasura_session json, 
  order_direction boolean DEFAULT false, 
  order_by_full_name boolean DEFAULT false, 
  order_by_email boolean DEFAULT false, 
  order_by_deal_status boolean DEFAULT false, 
  order_by_loan_status boolean DEFAULT false, 
  order_by_created_at boolean DEFAULT false, 
  limit_param integer DEFAULT 8, 
  offset_param integer DEFAULT 0
) RETURNS SETOF sort_types.__deals LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY 
SELECT 
  deal.id, 
  deal."_createdAt", 
  deal."dealStatusID", 
  dp."partyID", 
  party."partyRoleID", 
  pp."personID", 
  person."fullName", 
  person."primaryEmail", 
  loan.id AS "loanID", 
  loan."loanStatusID" 
FROM 
  deal 
  JOIN "DealParty" dp ON deal.id = dp."dealID" 
  JOIN party ON dp."partyID" = party.id 
  JOIN "PartyPerson" pp ON party.id = pp."partyID" 
  JOIN loan ON deal.id = loan."dealID" 
  JOIN person ON person.id = pp."personID" 
WHERE 
  party."partyRoleID" = 1 
  AND (
    person."primaryEmail" ILIKE '%' || search || '%' 
    OR person."fullName" ILIKE '%' || search || '%'
  ) 
  AND EXISTS (
    SELECT 
      1 
    FROM 
      "DealParty" dp2 
      JOIN party party2 ON dp2."partyID" = party2.id 
      JOIN "PartyPerson" pp2 ON party2.id = pp2."partyID" 
      JOIN person person2 ON pp2."personID" = person2.id 
    WHERE 
      dp2."dealID" = deal.id 
      AND party2."partyRoleID" = 2 
      AND person2."primaryEmail" = hasura_session ->> 'x-hasura-user-id'
  ) 
ORDER BY
  -- 按fullName排序
  CASE WHEN order_by_full_name AND NOT order_direction THEN person."fullName" END ASC,
  CASE WHEN order_by_full_name AND order_direction THEN person."fullName" END DESC,
  -- 按primaryEmail排序
  CASE WHEN order_by_email AND NOT order_direction THEN person."primaryEmail" END ASC,
  CASE WHEN order_by_email AND order_direction THEN person."primaryEmail" END DESC,
  -- 按dealStatusID排序
  CASE WHEN order_by_deal_status AND NOT order_direction THEN deal."dealStatusID" END ASC,
  CASE WHEN order_by_deal_status AND order_direction THEN deal."dealStatusID" END DESC,
  -- 按loanStatusID排序
  CASE WHEN order_by_loan_status AND NOT order_direction THEN loan."loanStatusID" END ASC,
  CASE WHEN order_by_loan_status AND order_direction THEN loan."loanStatusID" END DESC,
  -- 按_createdAt排序
  CASE WHEN order_by_created_at AND NOT order_direction THEN deal."_createdAt" END ASC,
  CASE WHEN order_by_created_at AND order_direction THEN deal."_createdAt" END DESC,
  -- 默认按_lastUpdatedAt排序
  CASE WHEN NOT (order_by_full_name OR order_by_email OR order_by_deal_status OR order_by_loan_status OR order_by_created_at) AND NOT order_direction THEN deal."_lastUpdatedAt" END ASC,
  CASE WHEN NOT (order_by_full_name OR order_by_email OR order_by_deal_status OR order_by_loan_status OR order_by_created_at) AND order_direction THEN deal."_lastUpdatedAt" END DESC
LIMIT 
  limit_param 
OFFSET 
  offset_param;
END;
$function$

补充说明 #

• 当前逻辑是order_direction=false对应升序，true对应降序；要是你需要反过来，把所有NOT order_direction和order_direction的位置互换就行。
• 方案2解决了原函数中类型转换导致的排序逻辑错误，是更可靠的实现方式。

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