S-JIS[2020-11-19]

PostgreSQL type

PostgreSQLのcreate typeのメモ。

概要

PostgreSQLでは、create type文によって、新しいデータ型を定義することが出来る。

複合型（Composite Type）

テーブル定義のように、複数のデータ型を組み合わせた新しいデータ型。
構造体のようなもの。
標準SQLにもある。

列挙型（Enumerated Type）

列挙子を定義する。

範囲型（Range Type）

値の範囲を定義できる。

基本型（Base Type）

新しい基本型（スカラ型）を定義できる。

複合型

複合型の作成方法は、create tableとよく似ている。
ただ、asを付けるのをよく忘れる＾＾；

create type 型名 as (
  カラム名1 データ型1,
  カラム名2 データ型2,
  …
  カラム名n データ型n
);

-- 例
create type hoge_type as (
  h_id   int,
  h_name text
);

定義した複合型は、psqlから\dによって確認することが出来る。

hishidamadb=> \d hoge_type
                 複合型"public.hoge_type"
   列   |   型    | 照合順序 | Null 値を許容 | デフォルト 
--------+---------+----------+---------------+------------
 h_id   | integer |          |               | 
 h_name | text    |          |               | 

複合型を再定義したい場合は、drop type文によって一旦削除してから再度create typeを実行する。
（create or replace typeという構文は無い）

ただし、その複合型を使っているプロシージャー（procedure）や関数（function）があると、その複合型は削除できない。
一旦プロシージャーや関数をdropすれば、複合型を削除できるようになる。（当然、後でプロシージャーや関数を再作成する必要がある）

drop typeにcascadeを付けると、その複合型を使っているプロシージャーや関数を自動的に削除してくれる。

drop type hoge_type cascade;

複合型をPL/pgSQLで使う際の注意点

テーブルの複合型

create tableによってテーブルを作成すると、テーブル名と同名の複合型が暗黙に定義されるのだそうだ。

したがって、PL/pgSQLで変数を定義する際のデータ型に、テーブル名のみ（実際は複合型の名前）を指定できる。

declare
  rec1 hoge%rowtype;	--行型「テーブル名%rowtype」
  rec2 hoge;        	--複合型

特に、行型の配列は何故か定義できないので、複合型の配列にする。

declare
  recs1 hoge%rowtype[];	--NG
  recs2 hoge[];        	--OK

複合型の中の複合型へのSELECT結果の代入

複合型の中で別の複合型を指定することが出来る。

create type foo_type as (
  foo_id   int,
  hoge_rec hoge
);

このとき、fooのフィールドhoge_recに直接レコードを代入しようとすると、エラーになる。

declare
  foo foo_type;
begin
  select * into foo.hoge_rec from hoge where h_id = 1;
end

↓実行するとエラー

ERROR:  malformed record literal: "1"
DETAIL:  Missing left parenthesis.

「left parenthesis」は左括弧「(」のことで、たぶんレコード型の値（例えば「(1, 'name')」）を想定して、丸括弧が無いと言っているのだと思う。
"1"は、取得したhogeテーブルの先頭カラムh_idから来ていると思う。
たぶん、"1"をfoo.hore_recの型（レコード型）に変換しようとしてエラーになったのだろう。

一旦ローカル変数で受ければ問題ない。

declare
  foo  foo_type;
  hrec hoge;
begin
  select * into hrec from hoge where h_id = 1;
  foo.hoge_rec := hrec;
end

あるいは、以下のように書くことも出来る。

declare
  foo foo_type;
begin
  select row(hoge.*) into foo.hoge_rec from hoge where h_id = 1;
end

declare
  foo foo_type;
begin
  foo.hoge_rec := row(hoge.*) from hoge where h_id = 1;
end

（この場合、単なる「row(*)」だとエラーになるので注意）

複合型の中の複合型のカラムの参照

複合型の中でさらに複合型を指定している場合、そのカラムを参照するには以下のように書きたくなるが。

  if foo.hoge_rec.h_id = 1 then
    raise info 'foo=%', foo;
  end if;

↓実行するとエラー

DEBUG:  relation "foo.hoge_rec" does not exist
ERROR:  missing FROM-clause entry for table "hoge_rec"
行 1: SELECT foo.hoge_rec.h_id = 1
             ^

どうも、foo.hoge_rec.h_idが「スキーマ名.テーブル名.カラム名」と解釈されて、hoge_recテーブルが無いというエラーになっているようだ。

以下のように丸括弧で囲んでやると通る。

  if (foo.hoge_rec).h_id = 1 then
    raise info 'foo=%', foo;
  end if;