![[PukiWiki]](image/logo.png "[PukiWiki]"){kind=logo}
PostgreSQLを使用している環境で、PL/pgSQLを使った方が良いのにという場面があります。
たとえば、バッチ処理などが該当すると思います。
バッチ処理プログラムをJava, Ruby, Pythonなどで記述することも可能ですが、PL/pgSQLで記述することも可能です。
(プログラム言語で記述した場合は、データベースが変わっても流用しやすいというメリットがあります。)
PL/pgSQLは、データベースを操作するプログラム言語になります。
PL/pgSQLで記述したファンクションは、SELECTやトリガーから呼出し実行することができます。
よく、「スドアド」とか「ストアドプロシージャ」と言われるものになります。
当サイトに記載されている会社名、製品名などは一般に各社または団体の商標または登録商標です。
当サイトの資料により直接的および間接的障害が生じても一切責任を負いませんので、あらかじめご了承ください。
自己責任のもとで本資料をご利用ください。
PL/pgSQLでHello worldを作成してみます。
尚、操作はすべてpsqlコマンドで行います。
pgAdminIIIでも赤枠で囲んだボタンで実行可能です。
または、以下に記すファンクションSQLをpgAdminIIIと関連付けしておけば、pgAdminIIIが自動起動され、赤枠で囲んだボタンをクリックしたときの画面が表示されます。
(WindowsのpgAdminIII v1.16.1 @ Windows で確認)
以下のPL/pgSQLファンクションは、ターミナル上にHello world!と表示し、クエリー結果としてHello World!を表示するサンプルです。
![[smile]](image/face/smile.png)
-- helloworld.sql
CREATE OR REPLACE FUNCTION helloworld()
RETURNS
TEXT
AS $$
DECLARE
msg text := 'Hello world!';
BEGIN
RAISE INFO '%', msg;
RETURN msg;
END
$$ LANGUAGE plpgsql
;
上記の
helloworld.sqlをDBに登録する手順を以下に記します。
尚、使用するのはpsqlコマンドとなります。
pgAdminIII等のGUIツールで動作確認する場合は、読み替えてください。
今回、サンプルソースも小さいのでcut&pasteで張り付けてもよいと思います。
sakura@cygwin ~/PLpgSQL$ psql -U sakura -h localhost sakuradb Password for user sakura: psql (9.6.0, server 9.2.17) Type "help" for help. sakuradb=> -- helloworld.sql sakuradb=> sakuradb=> CREATE OR REPLACE FUNCTION helloworld() sakuradb-> RETURNS sakuradb-> TEXT sakuradb-> AS $$ sakuradb$> DECLARE sakuradb$> msg text := 'Hello world!'; sakuradb$> BEGIN sakuradb$> RAISE INFO '%', msg; sakuradb$> RETURN msg; sakuradb$> END sakuradb$> $$ LANGUAGE plpgsql sakuradb-> ; CREATE FUNCTION sakuradb=>
psqlコマンドを使用してファンクションを登録します。
オプション-fによりファイルを指定してファンクションを登録しています。
sakura@cygwin ~/PLpgSQL$ psql -U sakura -h localhost sakuradb -f helloworld.sql Password for user sakura: CREATE FUNCTION
上記の操作でhelloworldファンクションの登録が完了しました。
早速、このファンクションを実行してみます。
sakura@cygwin ~/PLpgSQL$ psql -U sakura -h localhost sakuradb Password for user sakura: psql (9.6.0, server 9.2.17) Type "help" for help. sakuradb=>
sakuradb=> select helloworld(); INFO: Hello world! helloworld -------------- Hello world! (1 row)ASを使って名称を指定した例です。
sakuradb=> select helloworld() as message; INFO: Hello world! message -------------- Hello world! (1 row)INFO: Hello world! と、クエリー結果として Hello world! が返却されているのが確認できます。
登録されているファンクションの一覧を確認する場合は、以下のSQLを実行します。
以下のSQLでファンクション名のみ表示されます。
SELECT p.proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public' ;
sakuradb=> SELECT p.proname FROM pg_catalog.pg_namespace n sakuradb-> JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid sakuradb-> WHERE n.nspname = 'public' sakuradb-> ; proname ------------ helloworld (1 row)
以下のSQLでファンクション名およびスクリプトが表示されます。
SELECT proname, prosrc FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON pronamespace = n.oid WHERE nspname = 'public' ;
sakuradb=> SELECT proname, prosrc FROM pg_catalog.pg_namespace n
sakuradb-> JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
sakuradb-> WHERE nspname = 'public';
proname | prosrc
------------+-----------------------------------
helloworld | \r +
| DECLARE\r +
| msg text := 'Hello world!';\r+
| BEGIN\r +
| RAISE INFO '%', msg;\r +
| RETURN msg;\r +
| END\r +
|
(1 row)
作成したhelloworldファンクションを削除(DROP)する方法を以下に記します。
ファンクションを削除(DROP)する構文は以下の通りです。
DROP FUNCTION ファンクション名
今回作成した、helloworld()ファンクションを削除(DROP)してみます。
sakuradb=> DROP FUNCTION helloworld(); DROP FUNCTION
削除されたのが確認できます。
sakuradb=> SELECT helloworld();
ERROR: 関数helloworld()は存在しません
LINE 1: SELECT helloworld();
^
HINT: 指定名称、指定引数型に合う関数がありません。明示的な型キャストが必要かもしれません
sakuradb=>
01.png