子查詢必須在 postgresql 中只返回一列

我想在 PostgreSQL 中的儲存過程呼叫之後獲得 XML 響應。在下面的查詢中，我試圖獲取輸出，但它拋出了一個錯誤：子查詢必須只返回一列。有人可以幫我弄這個嗎？

SELECT (
       SELECT xmlforest('2021_PPP_Parasiticide_Program' AS Name, CASE 
                   WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                       THEN 'True'
                   ELSE 'False'
                   END AS requirementsMe, coalesce("temptable"."YTD_2021_Qualifying_Carton_Purchased", 0) AS qualifyingPurchaseAmount, 'Dollar' AS qualifyingPurchaseAmountType, CASE 
                   WHEN "temptable"."C_2021_PPP_PARA" IS NOT NULL
                       THEN 'Active'
                   ELSE 'In-Active'
                   END AS componentStatus)
           ,(
               SELECT (
                       SELECT xmlforest('q1_parasiticide_carton_rebate' AS rewardName, CASE 
                                   WHEN q1_parasiticide_carton_rebate IS NULL
                                       THEN CAST(0 AS VARCHAR)
                                   ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                   END AS rewardAmount)
                       FROM temptable
                       )
                   ,(
                       SELECT xmlforest('q2_parasiticide_carton_rebate' AS rewardName, CASE 
                                   WHEN q2_parasiticide_carton_rebate IS NULL
                                       THEN CAST(0 AS VARCHAR)
                                   ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
                                   END AS rewardAmount)
                       FROM temptable
                       WHERE temptable.rus_id = temptable.rus_id
                       )
               FROM temptable
               )
       FROM temptable
       );

我想要以下格式的回复 -

<CustomerProgramStatus_response>
  <programComponents>
     <name>2020_PPP_Parasiticide_Program</name>
     <requirementsMet>FALSE</requirementsMet>
     <qualifyingPurchaseAmount>0</qualifyingPurchaseAmount>
     <qualifyingPurchaseAmountType>Dollar</qualifyingPurchaseAmountType>
     <componentStatus>In-Active</componentStatus>
     <componentRewards>
        <rewardName>Q1_Parasiticide_Carton_Rebate</rewardName>
        <rewardAmount>0</rewardAmount>
     </componentRewards>
     <componentRewards>
        <rewardName>Q2_Parasiticide_Carton_Rebate</rewardName>
        <rewardAmount>0</rewardAmount>
     </componentRewards>
  </programComponents>
</CustomerProgramStatus_response>

如果 temptable 有一條記錄，那麼您可能根本不需要子查詢。它有更多的一個，那麼你需要聚合。可能最好的辦法不是提供更多資訊，而是研究一些範例，例如：https ://www.w3resource.com/PostgreSQL/postgresql-xml-functions.php並從內部逐步向外建構您的 xml，例如

1:

SELECT xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
   CASE WHEN q1_parasiticide_carton_rebate IS NULL
   THEN CAST(0 AS VARCHAR)
   ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
   END AS rewardAmount)
FROM temptable;

2:

SELECT xmlforest(xmlforest('q1_parasiticide_carton_rebate' AS "rewardName",
   CASE WHEN q1_parasiticide_carton_rebate IS NULL
   THEN CAST(0 AS VARCHAR)
   ELSE cast(coalesce(q1_parasiticide_carton_rebate, 0) AS VARCHAR(10))
   END AS rewardAmount) as "componentRewards",
xmlforest('q2_parasiticide_carton_rebate' AS rewardName,
   CASE WHEN q2_parasiticide_carton_rebate IS NULL
   THEN CAST(0 AS VARCHAR)
   ELSE cast(coalesce(q2_parasiticide_carton_rebate, 0) AS VARCHAR(10))
   END AS rewardAmount) as "componentRewards")
FROM temptable;

引用自：https://dba.stackexchange.com/questions/290222