PostgreSQL 複合型

## 環境
| DB | バージョン |
| ---- | ---- |
| PostgreSQL | 13.5 |

設定はデフォルト。

## 前文
以下のようなクエリが構文エラーにならず、レコードが取得できることにある日偶然気が付いた。
```SQL
mydb=# SELECT pg_database FROM pg_database;
                                                 pg_database
--------------------------------------------------------------------------------------------------------------
 (13445,postgres,10,6,en_GB.UTF-8,en_GB.UTF-8,f,t,-1,13444,478,1,1663,)
 (16384,mydb,10,6,en_GB.UTF-8,en_GB.UTF-8,f,t,-1,13444,478,1,1663,)
 (1,template1,10,6,en_GB.UTF-8,en_GB.UTF-8,t,t,-1,13444,478,1,1663,"{=c/postgres,postgres=CTc/postgres}")
 (13444,template0,10,6,en_GB.UTF-8,en_GB.UTF-8,t,f,-1,13444,478,1,1663,"{=c/postgres,postgres=CTc/postgres}")
(4 行)
```
`SELECT`句にテーブル名が指定できる？ 何故そんな構文が通る？
通常の全レコード取得は以下。上記の場合は各列の値がカンマ区切りで連結され取得されていることが分かる。
```SQL
mydb=# SELECT * FROM pg_database;
  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
 13445 | postgres  |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | f             | t            |           -1 |         13444 |          478 |          1 |          1663 |
 16384 | mydb      |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | f             | t            |           -1 |         13444 |          478 |          1 |          1663 |
     1 | template1 |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | t             | t            |           -1 |         13444 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13444 | template0 |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | t             | f            |           -1 |         13444 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
(4 行)
```

`PostgreSQL`には他の DB には見られないような多様な型を持っている。[配列型](https://www.postgresql.jp/document/13/html/arrays.html "8.15. 配列")なんてものもあるから、列の値を配列で束ねて返しているのかなと漠然と考えていた。
しかしよくよく考えてみると配列型であれば、`{` `}` で囲む書式なので違う。これって何だろう（何の型だろう）と疑問に思い調べてみた。

## 結論
![7153db65127ceba1b5e5fd9012a73e7d.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/7bd4a6d0-9b7e-f5d2-bee6-d5fe30751fe0.jpeg)
あ…ありのまま今起こった事を話すぜ!
`pg_database`という名前の型（複合型）。厳密には行コンストラクタ。
な…何を言っているのかわからねーと思うが おれも何をされたのかわからなかった…

以下調査内容の詳細。

## 序文
まず[公式のSELECT句のドキュメント](https://www.postgresql.jp/document/13/html/sql-select.html "SELECT")を確認。しかし、`SELECT`句にテーブル名を指定できるとか、指定した場合の動作の説明は見当たらなかった。いきなり頓挫。そこで実際に動かして確認してみた。

## 初歩的な確認
以下は構文エラーとなる。
```SQL
mydb=# SELECT pg_database;
ERROR:  column "pg_database" does not exist
行 1: SELECT pg_database;
             ^
mydb=# SELECT pg_database FROM pg_attribute;
ERROR:  column "pg_database" does not exist
行 1: SELECT pg_database FROM pg_attribute;
```
`FROM`句が省略されている場合や、指定されていても`SELECT`句の指定と異なる場合は不可らしい。
まだ暗中模索。

## 実行プラン
冒頭のクエリの実行プランを確認。
```SQL
mydb=# EXPLAIN ANALYZE VERBOSE
mydb-# SELECT pg_database FROM pg_database;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on pg_catalog.pg_database  (cost=0.00..1.02 rows=2 width=282) (actual time=0.046..0.062 rows=4 loops=1)
   Output: pg_database.*
 Planning Time: 0.178 ms
 Execution Time: 0.134 ms
(4 行)

mydb=# EXPLAIN ANALYZE VERBOSE
mydb-# SELECT * FROM pg_database;
                                                                                QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pg_catalog.pg_database  (cost=0.00..1.02 rows=2 width=258) (actual time=0.017..0.021 rows=4 loops=1)
   Output: oid, datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl
 Planning Time: 0.134 ms
 Execution Time: 0.049 ms
(4 行)
```
`Output:`行に注目。`SELECT *`の場合は全ての列が展開されていることが分かる。これは想定した結果。`SELECT *`は内部的にそうした動きとなりコストが発生するので、`SELECT`句には明示的に列名を指定しましょう、なんて最初に習うよね。

一方、`SELECT pg_database`の場合、実行プラン上では`pg_database.*`となっている。
しかし、そもそも`SELECT *`は`SELECT pg_database.*`と書いても同じなのでその実行プランを確認。
```SQL
mydb=# EXPLAIN ANALYZE VERBOSE
mydb-# SELECT pg_database.* FROM pg_database;
                                                                                QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pg_catalog.pg_database  (cost=0.00..1.02 rows=2 width=258) (actual time=0.033..0.039 rows=4 loops=1)
   Output: oid, datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl
 Planning Time: 0.202 ms
 Execution Time: 0.089 ms
(4 行)
```
やはり`SELECT *`の実行プランと同じ。つまりクエリ上の`pg_database.*`と、実行プラン上の`pg_database.*`とは意味が異なるらしい。（後述）
まだまだ暗中模索。

## `pg_typeof`関数で確認
```SQL
mydb=# SELECT pg_typeof(pg_database) FROM pg_database;
  pg_typeof
-------------
 pg_database
 pg_database
 pg_database
 pg_database
(4 行)
```
`pg_database`という名前の型ということ？ そうなると`pg_typeof`関数の引数も型を与えていることになる。しかし通常引数に指定するものは値であり、型を与えたらエラーになる筈だが。
```SQL
mydb=# SELECT pg_typeof(1);
 pg_typeof
-----------
 integer
(1 行)

mydb=# SELECT pg_typeof(INT);
ERROR:  column "int" does not exist
行 1: SELECT pg_typeof(INT);
```
この件は後で再検証再考察。
糸口が少し見えてきた。

## 実テーブルに格納し確認
原始的な方法だが有効。以下で`table_table`テーブルを作成し格納。
```SQL
mydb=# CREATE TABLE table_table AS
mydb-# SELECT pg_database FROM pg_database
mydb-# ;
SELECT 4
```
テーブル定義確認。
```SQL
mydb=# \d table_table
                   テーブル"public.table_table"
     列      |   タイプ    | 照合順序 | Null 値を許容 | デフォルト
-------------+-------------+----------+---------------+------------
 pg_database | pg_database |          |               |
```
やはり`pg_database`という名前の型らしい。
`table_table`の内容を確認。
```SQL
mydb=# SELECT * FROM table_table;
                                                 pg_database
--------------------------------------------------------------------------------------------------------------
 (13445,postgres,10,6,en_GB.UTF-8,en_GB.UTF-8,f,t,-1,13444,478,1,1663,)
 (16384,mydb,10,6,en_GB.UTF-8,en_GB.UTF-8,f,t,-1,13444,478,1,1663,)
 (1,template1,10,6,en_GB.UTF-8,en_GB.UTF-8,t,t,-1,13444,478,1,1663,"{=c/postgres,postgres=CTc/postgres}")
 (13444,template0,10,6,en_GB.UTF-8,en_GB.UTF-8,t,f,-1,13444,478,1,1663,"{=c/postgres,postgres=CTc/postgres}")
(4 行)
```
そりゃ冒頭`SELECT pg_database FROM pg_database;`と同じ内容だよね。
各要素の項目名は分からないが、`CREATE TABLE`の`FROM`句で指定したシステムテーブル`pg_database`と同じ構成と予想される。
そちらの定義は以下。
```SQL
mydb=# \d pg_database
                 テーブル"pg_catalog.pg_database"
      列       |  タイプ   | 照合順序 | Null 値を許容 | デフォルト
---------------+-----------+----------+---------------+------------
 oid           | oid       |          | not null      |
 datname       | name      |          | not null      |
 datdba        | oid       |          | not null      |
 encoding      | integer   |          | not null      |
 datcollate    | name      |          | not null      |
 datctype      | name      |          | not null      |
 datistemplate | boolean   |          | not null      |
 datallowconn  | boolean   |          | not null      |
 datconnlimit  | integer   |          | not null      |
 datlastsysoid | oid       |          | not null      |
 datfrozenxid  | xid       |          | not null      |
 datminmxid    | xid       |          | not null      |
 dattablespace | oid       |          | not null      |
 datacl        | aclitem[] |          |               |
インデックス:
    "pg_database_datname_index" UNIQUE, btree (datname)、テーブル空間"pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid)、テーブル空間"pg_global"
テーブル空間: "pg_global"
```

改めて`PostgreSQL`の[データ型の一覧](https://www.postgresql.jp/document/13/html/datatype.html "第8章 データ型")を確認。配列型以外に複合型なるものが見つかる。

[8.16. 複合型](https://www.postgresql.jp/document/13/html/rowtypes.html "8.16. 複合型")
> 複合型は、行もしくはレコードの構造を表現します。

今回調べているものの構造はまさにこれ。ビンゴっぽい。それを前提に`table_table`テーブルから個別の要素を取得してみる。
```SQL
-- 構文エラーとなる取得クエリ
mydb=# SELECT pg_database.datname FROM table_table;
ERROR:  missing FROM-clause entry for table "pg_database"
行 1: SELECT pg_database.datname FROM table_table;
             ^

-- 正しい取得クエリ
mydb=# SELECT (pg_database).datname FROM table_table;
  datname
-----------
 postgres
 mydb
 template1
 template0
(4 行)

mydb=# SELECT (pg_database).* FROM table_table;
  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
 13445 | postgres  |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | f             | t            |           -1 |         13444 |          478 |          1 |          1663 |
 16384 | mydb      |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | f             | t            |           -1 |         13444 |          478 |          1 |          1663 |
     1 | template1 |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | t             | t            |           -1 |         13444 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13444 | template0 |     10 |        6 | en_GB.UTF-8 | en_GB.UTF-8 | t             | f            |           -1 |         13444 |          478 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
(4 行)
```
`pg_database.datname`のような指定は構文エラーになるので`(pg_database).datname` `(pg_database).*`のように括弧で囲む必要があるらしい。これも複合型の構文仕様に一致する。

[8.16.3. 複合型へのアクセス](https://www.postgresql.jp/document/13/html/rowtypes.html#ROWTYPES-ACCESSING "8.16.3. 複合型へのアクセス")
> 複合型の列のフィールドにアクセスするには、テーブル名からフィールドを選択する場合とほぼ同様に、ドットとフィールド名を記述します。 実際、テーブル名からの選択とかなり似ていますので、パーサを混乱させないように括弧を使用しなければならないことがしばしばあります。 例えば、on_handというテーブルの例からサブフィールドを選択しようとした場合、以下のように書くかもしれません。
> ```SQL
> SELECT item.name FROM on_hand WHERE item.price > 9.99;
> ```
> これは、SQLの構文規則に従ってitemがon_handの列名ではなくテーブル名として解釈されるため、動作しません。 以下のように記述しなければなりません。
> ```SQL
> SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
> ```
更に複合型の説明には以下がある。

> テーブルを生成する時には、テーブルの行型を表現するために、テーブル名と同じ名前の複合型も自動的に生成されます。 例えば、以下のように
> ```SQL
> CREATE TABLE inventory_item (
>     name            text,
>     supplier_id     integer REFERENCES suppliers,
>     price           numeric CHECK (price > 0)
> );
> ```
> テーブルを作成すると、上述のものと同じinventory_itemという複合型が副次的に作成され、同様に使用することができるようになります。 

そうなの？ 改めて`CREATE TABLE`のドキュメント確認。

[CREATE TABLE](https://www.postgresql.jp/document/13/html/sql-createtable.html "CREATE TABLE")
> さらに、CREATE TABLEは、作成するテーブルの1行に対応する複合型のデータ型を作成します。

知らなかった……＿|￣|○。これって常識？
つまり、冒頭`SELECT pg_database FROM pg_database;`クエリの`SELECT`句での指定はテーブル名ではなく複合型の名前と解釈すべき。そりゃ、`SELECT`句にテーブル名なんて指定できるわきゃねぇよなぁ。
かなり状況が見えてきた。

## 行コンストラクタとキャスト
複合型の説明に「行コンストラクタ」というワードが出てくるので確認。

[4.2.13. 行コンストラクタ](https://www.postgresql.jp/document/13/html/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS "4.2.13. 行コンストラクタ")
> デフォルトでは、ROW式により作成される値は匿名レコード型になります。

例に出てくるクエリを実際に試してみる。
```SQL
mydb=# SELECT ROW(1,2.5,'this is a test');
           row
--------------------------
 (1,2.5,"this is a test")
(1 行)

mydb=# SELECT pg_typeof(ROW(1,2.5,'this is a test'));
 pg_typeof
-----------
 record
(1 行)
```
ふむふむ。行コンストラクタなんて知らなかったよ。そして`pg_database`は型（複合型）なのでキャストに使用することができる。
```SQL
mydb=# SELECT ROW(13445::oid,'postgres'::name, 10::oid, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)::pg_database;
              row
--------------------------------
 (13445,postgres,10,,,,,,,,,,,)
(1 行)
```
こんなことができるんだねぇ。
これは良い補強材料。

## `pg_typeof`関数再び
前述`pg_typeof`関数での確認クエリだが、`FROM`句を省略するとエラーとなる。
```SQL
mydb=# SELECT pg_typeof(pg_database);
ERROR:  column "pg_database" does not exist
行 1: SELECT pg_typeof(pg_database);
```
そしてこれは`SELECT pg_typeof(INT);`のエラーと同じ。つまり上記クエリの`pg_database`は型であり、やはり`pg_typeof`関数の引数に型の指定は不可で正しい。
そして`SELECT pg_typeof(pg_database) FROM pg_database;`でエラーにならないのは、この場合の`pg_typeof`関数の引数に与えているものは`FROM`句が返す行コンストラクタ、と考えられる。

以上を総括すると、以下のクエリの`SELECT`句に含まれる`pg_database`はそれぞれ別物。紛らわしい。
```SQL
SELECT pg_database FROM pg_database;    -- pg_databaseテーブルの行コンストラクタ
SELECT pg_database.* FROM pg_database;  -- pg_databaseテーブルの列名
SELECT pg_typeof(pg_database);          -- 複合型名（エラーになる）
```
それで冒頭の結論となった。

因みに`pg_database.*`は複合型の列名を参照しているのかも、とも思ったが、この件も公式で触れられている。

[8.16.5. 問い合わせでの複合型の使用](https://www.postgresql.jp/document/13/html/rowtypes.html#ROWTYPES-USAGE "8.16. 複合型")
> 通常のtable_name.column_nameという列名修飾の構文は、フィールド選択をテーブルの現在行の複合型の値に対して適用していると考えることもできます。 （効率の問題から、実際にはそのような実装にはなっていません。）

但し、実行プラン上の`pg_database.*`は複合型の列名と思われ。

## 補足
マイナーなテーマなのか、探し方が悪いのか、ネット上から公式以外で有力な情報は殆ど見つからなかった。
かろうじて見つかったのが以下。これが解法のヒント（というか結論そのもの）になった。
[Postgres composite types for tables](https://www.geekytidbits.com/postgres-composite-type/ "Postgres composite types for tables")
更にこのサイトでは`NULL`に関する解説もあり興味深い。

![7153db65127ceba1b5e5fd9012a73e7d.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254888/7bd4a6d0-9b7e-f5d2-bee6-d5fe30751fe0.jpeg)
もっと恐ろしいものの片鱗を味わったぜ…
## 関連投稿
[PostgreSQL 文字列末尾スペース、文字列型の上限、暗黙型変換等の備忘録 - Qiita](https://qiita.com/god19/items/1e21ef8bd86c65693aba "PostgreSQL 文字列末尾スペース、文字列型の上限、暗黙型変換等の備忘録 - Qiita")