Navicat 17 で PostgreSQL のデータ型を拡張する - パート 2 Jan 3, 2025 by Robert Gravelle

複合型

PostgreSQL で Navicat Premium 17 を使用してカスタムデータ型を作成する方法に関するシリーズの第2弾へようこそ。パート1では、無料の DVD レンタルデータベースにカスタムドメインを作成する方法を学びました。ドメインは、NOT NULL や CHECK などの制約を持つユーザー定義のデータ型です。今日のブログでは、ユーザー定義関数から複雑なデータを返すために複合型を作成します。

PostgreSQL で定義される型

型は CREATE TYPE コマンドを使用して生成されます。これは、ストアドプロシージャや関数で入力パラメータのデータ型および戻り値として使用できる複合型を作成します。

PostgreSQL の CREATE TYPE は、主に4つのバリエーションをサポートしています。

複合型: 2つ以上のデータ型を組み合わせた複合データを定義します。込み入ったデータ構造が表現できる、複雑で複数フィールドを持つデータ型の作成が可能です。
列挙型: あらかじめ定義された名前付き値の固定セットとして定義され、入力をそれらの特定のオプションのみに制限します。
範囲型: 値間の連続した間隔を表し、日付や数値などの連続したデータ範囲に対する高度な操作を可能にします。
基本型: int、varchar、numeric などの既存の基本型に基づいて、ユーザー定義型を作成できます。ユーザー定義型に特定の「基本型」はありませんが、新しい型は基本的に、これらの基になる PostgreSQL 基本型に適用される拡張または制約です。

次のいくつかのセクションでは、型を作成し、関数でそれを使用することにより、複合型をさらに詳しく探求します。

CREATE TYPE 文

すべての型は CREATE TYPE 文を使用して作成されます。たとえば、映画 ID、タイトル、release_year など、映画に関するいくつかの値を返す関数が必要だとします。これは、「film_summary」という名前の型を作成する文です。

CREATE TYPE film_summary AS (
    film_id INT4,
    title VARCHAR(255),
    release_year CHAR(4)
);

Navicat 17 で型を作成する

Navicat Premium 17 と Navicat for PostgreSQL 17 はどちらも、正確な構文をすべて知らなくても型を生成するための GUI ベースのツールを提供しています。メインツールバーの「その他」にそのツールがあります。

次に、「オブジェクト」ツールバーの「新しい型」項目の横にある矢印をクリックします。これにより、型を作成するための 4つの異なるオプションが表示されます。コンテキストメニューから「複合」項目を選択します。

これにより、フィールドの詳細を入力できるグリッドが表示されます。「film_summary」型を構成する 3つのフィールドはすでに存在するため、テーブルデザイナーの「film」テーブルを用いて、そこから型と長さのデータをコピーできます。ここでは、3つのフィールドが赤色で強調表示されています。

グリッドには、最初のフィールド用に空の行がすでにあります。詳細を入力したら、「メンバーを追加」をクリックして新しい行を追加できます。完成したグリッドは次のとおりです。

「保存」ボタンをクリックする前に、「SQL プレビュー」タブをクリックして、Navicat が生成する文を確認できます。

定義をまだ保存していないため、型の名前が「無題」になっていることに注意してください。これは想定どおりです。

ここで名前を割り当てましょう。「保存」ボタンをクリックすると、「名前を付けて保存」ダイアログが表示され、ここで型に「film_summary という名前を付けることができます。

関数で film_summary 型を使用する

それでは、「film_summary」を関数の戻り値の型として使用してみましょう。型の作成と同様に、Navicat の GUI ツールを使用してこれを行います。関数デザイナーにアクセスするには、メインツールバーの「関数」ボタンをクリックし、次に「オブジェクトツールバーの「新しい関数」をクリックします。

エディターは、CREATE FUNCTION の構文のほとんどを自動的に入力します。関数名、入力パラメーター、戻り値の型、関数本体など、いくつかの詳細を指定するだけで済みます。完成した CREATE FUNCTION 文を以下に示します。

CREATE FUNCTION get_film_summary (f_id INT4)
  RETURNS film_summary
AS $BODY$
  SELECT 
    film_id,
    title,
    release_year
  FROM
    film
  WHERE
    film_id = f_id;
$BODY$
  LANGUAGE SQL VOLATILE;