Dec 6, 2024 by Robert Gravelle

PostgreSQL の際立った機能の一つは、ユーザー定義の関数とデータ型を幅広くサポートしていることです。これにより、開発者はカスタムの変換、演算子、および集約関数を作成できます。集約関数は、SUM、AVG、COUNT などの標準の SQL 集約関数を越えて、データに対して複雑な計算や変換を実行するための強力な方法を提供します。Navicat for PostgreSQL と Navicat Premium はどちらも、特殊なグラフィカル・ユーザー・インターフェース（GUI）により、データベースとシームレスに統合するカスタム関数と集約関数を簡単に作成できます。いくつかの詳細を指定するだけで、Navicat が pgSQL ステートメントを作成してくれます。今日のブログでは、DVD Rental databaseで動作する集約関数を作成し、カテゴリ別に映画のタイトルを連結します。

集約関数について

集約関数は、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() 集約関数の作成

これで、2つの関数を 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";

集約関数の名前が「Untitled」であることに注意してください。「保存 ボタンを押すと、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;

まとめ

今日のブログでは、Navicat Premium でカスタム PostgreSQL 集約関数を作成し、DVD レンタルデータベースで動作するようにしました。この関数は、カテゴリ別に映画のタイトルを連結します。

Navicat Premium 17 を試してみたいですか？ Windows、macOS、および Linux オペレーティングシステムに対応し、 すべての機能を14日間お試しいただける無料トライアル版をダウンロードできます。