在 Navicat 17 中建立自訂 PostgreSQL 彙總 2024 年 12 月 6 日，由 Robert Gravelle 撰寫

PostgreSQL 的一個突出特點是它廣泛支援使用者定義函式和資料類型。這使開發人員能夠建立自訂轉換、運算子和彙總函式。彙總函式提供了一種對資料執行複雜計算和轉換的強大方法，超越了 SUM、AVG 和 COUNT 等標準的 SQL 彙總函式。Navicat for PostgreSQL 和 Navicat Premium 的專門的圖形使用者介面（GUI）都能輕鬆編寫與資料庫無縫整合的自訂函式和彙總。我們只需提供一些細節，Navicat 就能產生 pgSQL 陳述式！在今天的文章中，我們將建立一個彙總函式，用於按類別串連在 DVD 租賃資料庫中的電影標題。.

關於彙總

彙總是 SQL 的一項基本功能，可讓你對一組資料列執行計算或轉換，並傳回單一結果。最常見的彙總函式是 SUM、AVG、COUNT、MIN 和 MAX，它們分別允許你透過計算總計、平均值、計數、最小值和最大值來快速匯總資料。

然而，SQL 提供的內建彙總函式並不總是能滿足應用程式的特定需求。這就是能建立自訂彙總能力的有用之處。自訂彙總可讓你定義自己的邏輯來匯總和轉換資料，超越了一般的 SQL 彙總。這個過程通常涉及定義一個狀態轉換函式（用於更新累加器以處理每一個資料列），以及一個可選的最終函式（被呼叫以產生最終的彙總結果）。

產生轉換函式和最終函式

我們的轉換函式 array_append_state() 會被呼叫以更新每一個資料列的彙總狀態。

若要存取 Navicat 的函式編輯器，請按一下主按鈕列中的「函式」按鈕，然後按一下物件工具列中的「新增函式」：

Navicat 將從主函式定義開始引導我們。在那裡，我們可提供函式名稱、輸入參數和主體：

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)
  RETURNS text[] AS $BODY$
BEGIN
  RETURN array_append(current_state, new_value);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

完成後，我們可以按一下「儲存」來建立函式。

現在我們將返回到「物件」索引標籤並按一下「新增函式」來建立最終函式。

array_to_comma_string() 函式將接受一個電影標題的陣列並在每個元素之間插入逗號：

CREATE FUNCTION "public"."array_to_comma_string" (state text[])
  RETURNS text AS $BODY$
BEGIN
  RETURN array_append(state, ', ');
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

建立 comma_concat() 彙總函式

現在我們可以將兩個函式插入 Navicat 的彙總編輯器。我們可以按一下主按鈕列中的「其他」按鈕，然後從選單中選取「彙總」來存取編輯器：

在表單中，我們將「輸入類型」設定為「text」，然後在「狀態類型」輸入「text[]」並提供「狀態」和「最終函式」。同時，請確保「初始條件」是空陣列（「{}」）：

我們可以透過按一下「預覽」索引標籤來查看產生的 SQL：

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
  SFUNC = "public"."array_append_state",
  STYPE = "pg_catalog"."text[]",
  FINALFUNC = "public"."array_to_comma_string",
  INITCOND = "{}",
  PARALLEL = UNSAFE
);

ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

請注意，彙總的名稱是「未命名」。當我們按一下「儲存」按鈕，Navicat 會提示我們輸入名稱，並使用我們提供的名稱執行該命令。

使用自訂彙總

現在我們可以像呼叫其他函式一樣呼叫我們的彙總函式。以下是一個按類別取得電影清單的查詢：

SELECT 
    c.name AS category,
    comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;