【備忘録】PostgreSQLでストアドプロシージャ（関数）を書くときの注意

実行するときは
SELECT test_function();
みたいにして使う。

パラメータ名にもやっぱり大文字使えない。

文字列を引数にする際、ダブルクォートで囲むとエラーが出る。シングルクォートで囲めば大丈夫。
（例：’引数’）

一度関数を作ってしまったら、引数や戻り値の型を変更することはできない。
（参考：https://www.postgresql.jp/document/9.4/html/sql-createfunction.html）

戻り値

戻り値として行を返したいなら、戻り値の型としてrecordを指定する。
既存のテーブルの行を返したいなら、戻り値の型としてそのテーブル名を指定できる。

戻り値の出力で列名が出ない！

行を返す関数foo()を定義したとして、

SELECT foo();

を実行すると、

(1, 2, 3)

というような出力のみで、列名が出ません。
これでほんとに悩んじゃったんですが、

SELECT * FROM foo();

とすれば、列名もしっかり出力されます。
「* FROM」を入れるか入れないかで大違い！
公式サイトをよく読んだらちゃんと「* FROM」が書いてあった！　がくり。

複数行を返した場合

戻り値の型の設定で

SETOF test_table

というように、「SETOF」を付ける。

RETURNではなく、下記のようにRETURN QUERYを用いると良い。（PL/pgSQL）

RETURN QUERY SELECT * FROM test_table;

pgAdminⅢで新しい関数を作るとき

「厳密」はSTRICT指定のこと。
（STRICTを指定すると、引数に1つでもNULLがあれば常にNULLを返す）

「セキュリティを定義」はSECURITY DEFINER指定のこと。
（デフォルトはSECURITY INVOKER。関数を呼び出したユーザの権限で関数が実行される。
SECURITY DEFINERを指定すると、関数を作成したユーザの権限で関数が実行される。）
（参考：https://www.postgresql.jp/document/9.4/html/sql-createfunction.html）

セキュリティ

search_pathを設定する

SECURITY DEFINER指定している場合は、必ずsearch_pathでpg_tempを最後にして、悪意のあるユーザーが一時テーブルスキーマであるpg_temp_nnnを悪用することを防ぐ。
pgAdminⅢでは「変数」タブで「変数名」をsearch_pathにして、「変数値」を「public, pg_temp」とする。
（参考：https://www.postgresql.jp/document/9.4/html/runtime-config-client.html#GUC-SEARCH-PATH）

Publicの実行権限を削除する

pgAdminⅢで関数を作成すると、デフォルトでPublicに実行権限が付与されている。
必ず削除して、しかるべきユーザーにのみ実行権限を付与すること。
（参考：https://www.postgresql.jp/document/9.4/html/sql-createfunction.html）

MENU