PREPARATION
受験対策

2016年1月7日（米国時間）にリリースされたPostgreSQLバージョン9.5の機能紹介の後編です。

（3）行単位セキュリティの導入

GRANTによりSELECT、INSERT、UPDATE、DELETEのアクセス権限をテーブルごと、あるいはテーブル内の列ごとに付与することができますが、GRANTではテーブル内の行ごとにアクセス権限を管理することはできません。つまり、テーブル内の特定の行にアクセスできるユーザは、テーブル内のすべての行にアクセスできました。
目的に合わせてビューを使うことで、間接的にテーブル内の一部の行にだけアクセスを許可することも可能でしたが、9.5で導入された行単位のセキュリティポリシーを使うと、特定の条件に合った行だけSELECT/INSERT/UPDATE/DELETEの操作を可能にすることを、テーブル単位でロール毎に設定できます。
行単位セキュリティが有効なテーブルでは、セキュリティポリシーに反する行は、例えばSELECTしても行が返されません（ユーザからは行が存在しないように見えます）し、UPDATEしても更新されません。ポリシーに反する行をINSERTやUPDATEで作成しようとするとエラーになります。
セキュリティポリシーはCREATE POLICYで作成し、対象のテーブル、対象のユーザ（ロール）、対象の操作（SELECT/INSERT/UPDATE/DELETE/ALL）、各操作が可能となる条件式などを指定します。
なお、行単位セキュリティの機能はPostgreSQLの独自拡張です。

（4）UPDATEのSET句でSELECTリストが使用可能に

従来、UPDATE文で複数列を同時に更新するときの構文は
UPDATE table_name
SET column_name1 = expression1, column_name2=expression2...
あるいは
SET (column_name1, column_name2...) = (expression1, expression2...)
のいずれかでしたが、expressionとして複数列を返すSELECT文を記述することができませんでした。
他のテーブルを参照してデータを更新するときはFROM句（PostgreSQLの独自拡張）を使って表結合することで回避していましたが、9.5ではSQL標準に従ったSELECTリストを代入する構文が追加され、
SET (column_name1, column_name2...) = (SELECT col1, col2... FROM ...)
というような記述が可能になりました。
以下に具体的な例を示します。

（青：psqlのプロンプトからの入力、緑：psqlからの出力）

2つのテーブルmasterとsubがあります。

select * from master;

id | a | b

----+----+----

1 | a1 | b1

2 | a2 | b2

3 | a3 | b3

(3 rows)

select * from sub;

id | a | b

----+-------+-------

1 | a1sub | b1sub

2 | a2sub | b2sub

(2 rows)

masterテーブルのa列、b列の内容を、subテーブルのもので置き換えたいとき、PostgreSQL 9.4まででは、FROM句による表結合を使って、

update master m

set (a, b) = (s.a, s.b)

from sub s

where m.id = s.id;

のように書いていましたが、PostgreSQL 9.5では、これに加えて

update master m

set (a, b) = (select a, b from sub s where m.id = s.id);

のように書くこともできます。
ただし、これら2つのUPDATEは結果が必ずしも同じではないことに注意してください。
この例ではsubテーブルにid=3の列が存在しないため、FROMを使う場合は、結合できる2行だけが更新されて

select * from master;

id | a | b

----+-------+-------

3 | a3 | b3

1 | a1sub | b1sub

2 | a2sub | b2sub

(3 rows)

となりますが、SELECTを使う場合は、id=3に対してNULLが返されるため3行とも更新され、

select * from master;

id | a | b

----+-------+-------

1 | a1sub | b1sub

2 | a2sub | b2sub

3 |           |

(3 rows)

という結果になります。
また、逆に以下のようにsubテーブルにid=3の行が複数ある（subテーブルのid列には一意制約がないものとします）場合、

select * from sub;

id | a | b

----+-------+-------

1 | a1sub | b1sub

2 | a2sub | b2sub

3 | a3sub |

3 | | b3sub

(4 rows)

FROMを使うとUPDATE自体はエラーになりませんが、その結果は

select * from master;

id | a | b

----+-------+-------

1 | a1sub | b1sub

2 | a2sub | b2sub

3 | a3sub |

(3 rows)

あるいは

id | a | b

----+-------+------

1 | a1sub | b1sub

2 | a2sub | b2sub

3 | | b3sub

(3 rows)

で、id=3の行の更新結果がどうなるかは予測できません。
SELECTを使う場合は、1行の更新にSELECTが複数行を返すことが認められず、

ERROR: more than one row returned by a subquery used as an expression

というエラーになります。

別の例を採り上げます。
以下のdetailsテーブルをgid列の値が同じ行ごとにグループ化して集計し、summaryテーブルに格納することを考えます。

select * from details ;

did | gid | x

-----+-----+---

1 | 1 | 1

2 | 1 | 2

3 | 1 | 3

4 | 2 | 2

5 | 2 | 4

6 | 2 | 6

7 | 3 | 4

8 | 3 | 5

(8 rows)

select * from summary;

id | sumx | avgx | countx

----+------+------+--------

1 |       |       |

2 |       |       |

3 |       |       |

(3 rows)

FROM句を使って結合する場合、集約関数が使えないという制約があるため、PostgreSQL 9.4まででは、次のようにするしかありませんでした。

update summary set

sumx=(select sum(x) from details where summary.id = details.gid),

avgx=(select avg(x) from details where summary.id = details.gid),

countx=(select count(x) from details where summary.id = details.gid);

UPDATE 3

PostgreSQL 9.5では、次のように記述できます。

update summary

set (sumx, avgx, countx)

=(select sum(x), avg(x), count(x) from details where summary.id = details.gid);

UPDATE 3

いずれも結果は同じで

select * from summary;

id | sumx | avgx | countx

----+------+------+--------

1    |    6 |    2 |    3

2    |  12 |    4 |    3

3    |    9 |  4.5 |    2

(3 rows)

となりました。

解説：松田神一