はじめに
text型の可変個数配列の列に対するパターンマッチでちょっと躓いたのでメモ。
ANY/ALL演算子
配列内の任意の要素が1つ以上が真であれば配列全体への評価を真とするといった比較演算のために、PostgreSQLではANYという特殊な配列用演算子を持っている。
配列要素に対するANY演算子の例。
postgres=# SELECT 100 = ANY ('{50, 100, 120}');
?column?
----------
t
(1 row)
postgres=# SELECT 70 = ANY ('{50, 100, 120}');
?column?
----------
f
(1 row)
この例は配列の要素に対して"="演算子で評価し、一つでも真になる配列要素があれば真とみなすという例である。
ちょっと書き方は変わっている、こうすることで配列個数に関わりなく1つの比較演算で評価を行える。
注意しなければいけないのは、通常の比較演算子の直後にANYと書くことと、比較対象の配列を小括弧で括る必要があるということ。小括弧で括らないと構文エラーとなる。
postgres=# SELECT 100 = ANY '{50, 100, 120}';
ERROR: syntax error at or near "'{50, 100, 120}'"
LINE 1: SELECT 100 = ANY '{50, 100, 120}';
ANYとは逆にALL演算子は、配列内の全ての要素への比較演算結果が真のときに、真と評価するというものになる。
postgres=# SELECT 150 > ALL ('{50, 100, 120}');
?column?
----------
t
(1 row)
postgres=# SELECT 120 > ALL ('{50, 100, 120}');
?column?
----------
f
(1 row)
配列に対するパターンマッチはできない問題
なので、"="比較演算子と同じようにパターンマッチ比較演算子(LIKE, ~)を書けばいけるかな？と思ったのだが、同じようにパターンマッチ演算子を使うと意図した結果が得られない。
postgres=# SELECT '%ba%' LIKE ANY ('{foo,bar,baz}');
?column?
----------
f
(1 row)
最初は「？」と思ったが、よくよく考えてみればパターンマッチ演算子は "=" 検索のような交換則が成り立つわけではないので、当たり前である。
postgres=# SELECT 'bar' LIKE '%ba%';
?column?
----------
t
(1 row)
postgres=# SELECT '%ba%' LIKE 'bar';
?column?
----------
f
(1 row)
つまり、SELECT '%ba%' LIKE ANY ('{foo,bar,baz}') というクエリは、'%ba%'という文字列に対して、'foo', 'bar', 'baz'という3つのパターンマッチをしようとしているわけで、そりゃ真になるはずもない。
かといって、左辺と右辺を入れ替えたほうな記法をANYつきで実行するわけにはいかない。
postgres=# SELECT ('{foo,bar,baz}') LIKE ANY '%ba';
ERROR: syntax error at or near "'%ba'"
LINE 1: SELECT ('{foo,bar,baz}') LIKE ANY '%ba';
これは構文エラーになる。(´・ω・`)
対策：unnest関数とサブクエリの組み合わせ
こういう場合にはスマートな方法ではないが、unnest関数とサブクエリの組み合わせで対応する必要がある。
例えばこんなTEXT配列を持つテーブルがある。
test=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
data | text[] |
test=# TABLE test;
id | data
----+---------------
1 | {abc,def,ghi}
2 | {foo,bar,baz}
3 | {xxx,yyy,zzz}
(3 rows)
これに対してdataの配列内に1つでもマッチするidとdataを表示したい、というクエリを書く場合、以下の2つを組み合わせることになる。
- unnest関数で配列を行に展開するサブクエリを実行
- サブクエリ実行結果に対して、パターンマッチ
例えばこんな感じになる(LIKE検索の例)。
test=# SELECT DISTINCT id, data FROM
(SELECT id, data, unnest(data) as arr FROM test) as t
WHERE arr LIKE 'ba%';
id | data
----+---------------
2 | {foo,bar,baz}
(1 row)
test=# SELECT DISTINCT id, data FROM
(SELECT id, data, unnest(data) as arr FROM test) as t
WHERE arr LIKE '%a%';
id | data
----+---------------
1 | {abc,def,ghi}
2 | {foo,bar,baz}
(2 rows)
応用例：aclitem配列の検索
今回は、aclitem型配列に対するパターンマッチを例にする。
aclitem型とは
PostgreSQLではアクセスコントロールに関する情報をaclitem型と呼ばれる型の配列で管理している。
たぶん、aclitem型の実体はtext型のような気がする。
この型を持つカタログは以下のとおり(PostgreSQL 9.6の場合)。
- pg_attribute
- pg_class
- pg_database
- pg_default_acl
- pg_foreign_data_wrapper
- pg_foreign_server
- pg_init_privs
- pg_language
- pg_largeobject_metadata
- pg_namespace
- pg_pltemplate
- pg_proc
- pg_tablespace
- pg_type
結構ありますねー。
で、この中はちょっと特殊な記法で書かれていています。
pg_database の場合はこんな感じ。
postgres=# SELECT datname, datacl FROM pg_database WHERE datname LIKE 'db_%' ORDER BY datname;
datname | datacl
---------+-------------------------------------------------------------------------------------
db_a | {=Tc/postgres,postgres=CTc/postgres,foo=CTc/postgres,bar=c/postgres,baz=T/postgres}
db_b | {=Tc/postgres,postgres=CTc/postgres,foo=Cc/postgres}
db_c | {=Tc/postgres,postgres=CTc/postgres,baz=c/postgres}
(3 rows)
- "="の左辺はロール名。
- "/"の前のCTcの文字はそれぞれ以下の意味。
- 大文字のCはデータベース内での新規スキーマの作成を許可
- Tは一時テーブルを作成することを許可
- 小文字のcは指定されたデータベースへの接続を許可
- "/"の後はオーナーを示す。
この書式はPostgreSQL文書を見てもきちんと書いてない気がするのよなー。
pg_databaseのaclitem型配列への検索
例えばfooロールが接続権限(c)を持つデータベース名を求めたい場合は、こんな感じで記述することになる。
postgres=# SELECT DISTINCT datname FROM
(SELECT datname, unnest(datacl::text[]) as arr FROM pg_database) as t
WHERE arr ~ '^foo=.c/.';
datname
---------
db_b
(1 row)
- dataclはaclitem配列なので、これをtext配列にキャストしてunnest関数にかける。
- 行に展開された配列要素に対して "~" 演算子で正規表現マッチをかける。
unnsest関数の詳細についてはPostgreSQL文書の配列関数と演算子を、"~"演算子と正規表現の詳細についてはPostgreSQL文書のパターンマッチにて熟知すべし。