訪問 ORDER BY 中的內部類型欄位時，列不存在

在PostgreSQL 9.4中，我有兩種類型：

CREATE TYPE a_schema.type_child {  
 an_order smallint
}

CREATE TYPE a_schema.type_parent {
 pluto uuid,
 child type_child
}

然後在一個視圖中我有這個腳本：

SELECT 
   something_a,
   something_b,

   ARRAY(SELECT
           ROW (a_uuid,
             a_schema.get_a_type_child(a_type_child_id)
           )::a_schema.type_parent AS tp
         FROM a_schema.a_table
         ORDER BY ((tp).type_child).an_order ASC)
FROM ....

其中儲存過程只是做一個選擇：

CREATE FUNCTION a_schema.get_a_type_child(IN type_id smallint, OUT a_type a_schema.type_child)
 RETURNS a_schema.type_child AS
$BODY$
DECLARE
BEGIN

 SELECT
     ROW (a_order)::a_schema.type_child
 FROM a_schema_another_table
 WHERE at_id = type_id
 LIMIT 1
 INTO a_type;

END;

$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 1;

我得到的錯誤是：

錯誤：列“tp”不存在

與ORDER BY條款有關。

我知道我可以做到

ORDER BY tp

但是我必須為類型定義一個排序，這會引入很多程式碼。

有什麼方法可以訪問內部類型欄位an_order以進行排序？

錯誤說明

您收到錯誤消息：

錯誤：列“tp”不存在

因為您不能在子句的另一個表達式中使用輸出列。ORDER BY您只能作為一個整體引用輸出列名。表達式必須建立在輸入列上。手冊：

每個表達式可以是輸出列（列表項）的名稱或序號SELECT，也可以是由輸入列值形成的任意表達式。

大膽強調我的。

解決方案

您需要將行類型作為查詢的輸入，以將其包含在ORDER BY. 您可以通過LATERAL加入來做到這一點：

SELECT 
   something_a,
   something_b,
   ARRAY (
     SELECT (a_uuid, **my_child**)::a_schema.type_parent AS tp
     FROM   a_schema.a_table
     **LEFT JOIN LATERAL (SELECT get_a_type_child(a_type_child_id) AS my_child) t ON true**
     ORDER  BY **(t.my_child).an_order**
     )
FROM ....

為什麼LEFT JOIN LATERAL () ON true？

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

旁白

該解決方案只是概念驗證。您需要深入了解行和復合類型處理來應對這種設置，但它仍然會很笨拙和緩慢。您的設置比開始時需要的複雜。我將不得不輸入這麼多來解釋一切，這不值得。

我同意@a_horse：正確規範化的模式將為您省去很多麻煩。

該功能可以更簡單，但 PL/pgSQL 本身並沒有錯。SECURITY DEFINER無論如何，函式都免於函式內聯。SQL 與 PL/pgSQL 函式還有其他優缺點：

• PostgreSQL 函式中語言 sql 和語言 plpgsql 的區別

注意：函式。您的術語儲存過程有點偏離，因為 Postgres 沒有真正的儲存過程。只是功能 - 幾乎但不完全相同。

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