PL/pgSQLとは？の変更点

追加された行はこの色です。
削除された行はこの色です。
PL/pgSQLとは？へ行く。
----
&smile;''[[PL/pgSQL入門ページに戻る>PL/pgSQL入門]]''
----
* PL/pgSQLとは？ [#reeb6b2d]
PostgreSQLを使用している環境で、PL/pgSQLを使った方が良いのにという場面があります。~
たとえば、バッチ処理などが該当すると思います。~
バッチ処理プログラムをJava, Ruby, Pythonなどで記述することも可能ですが、PL/pgSQLで記述することも可能です。~
(プログラム言語で記述した場合は、データベースが変わっても流用しやすいというメリットがあります。)

PL/pgSQLは、データベースを操作するプログラム言語になります。~
PL/pgSQLで記述したファンクションは、SELECTやトリガーから呼出し実行することができます。

よく、「スドアド」とか「ストアドプロシージャ」と言われるものになります。~

#htmlinsertpcsp(db-top.html,db-sp.html)
#contents

* 関連サイト [#e53564e1]
[[Wikipediaへのリンク>https://ja.wikipedia.org/]]
-[[PL/pgSQL>https://ja.wikipedia.org/wiki/PL/pgSQL]]
-[[ストアドプロシージャ>https://ja.wikipedia.org/wiki/%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3]]

* お約束 [#xcea350b]
当サイトに記載されている会社名、製品名などは一般に各社または団体の商標または登録商標です。~
当サイトの資料により直接的および間接的障害が生じても一切責任を負いませんので、あらかじめご了承ください。~
自己責任のもとで本資料をご利用ください。

* PL/pgSQLで Hello world! [#m38949d4]
PL/pgSQLでHello worldを作成してみます。~
尚、操作はすべてpsqlコマンドで行います。~
pgAdminIIIでも&color(red){赤枠};で囲んだボタンで実行可能です。~
または、以下に記すファンクションSQLをpgAdminIIIと関連付けしておけば、pgAdminIIIが自動起動され、&color(red){赤枠};で囲んだボタンをクリックしたときの画面が表示されます。~
(WindowsのpgAdminIII v1.16.1 @ Windows で確認)
#ref(01.png)

以下のPL/pgSQLファンクションは、ターミナル上にHello world!と表示し、クエリー結果としてHello World!を表示するサンプルです。
#ref(helloworld.sql)
 -- 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ファンクションの登録 [#x766fb47]
上記の&ref(helloworld.sql);をDBに登録する手順を以下に記します。~
尚、使用するのはpsqlコマンドとなります。~
pgAdminIII等のGUIツールで動作確認する場合は、読み替えてください。

** cut&pasteで張り付ける [#tf2333f1]
今回、サンプルソースも小さいのでcut&pasteで張り付けてもよいと思います。
+データベースに接続~
今回、テスト用にsakuradbというものを用意しました。皆さんの環境に合うように読み替えてください。
+helloworld.sqlの内容を貼り付ける~
pasteすると以下のように表示され、''CREATE FUNCTION''と表示されているのが確認できます。
 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コマンドでファンクションを登録する [#ec6eab37]
psqlコマンドを使用してファンクションを登録します。~
オプション-fによりファイルを指定してファンクションを登録しています。~
 sakura@cygwin ~/PLpgSQL$ psql -U sakura -h localhost sakuradb -f helloworld.sql
 Password for user sakura:
 CREATE FUNCTION

* helloworldファンクションを実行 [#f3468706]
上記の操作で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=>
#br
+helloworldファンクションをSELECT構文を使用して実行します。
 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! が返却されているのが確認できます。

* ファンクション一覧の確認 [#u358b588]
登録されているファンクションの一覧を確認する場合は、以下のSQLを実行します。

** ファンクション名のみ [#l9a4ef42]
以下の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'
 ;
***実行例 [#h0cb65b8]
 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)

** ファンクション名とスクリプト内容 [#s68b602c]
以下のSQLでファンクション名およびスクリプトが表示されます。
 SELECT proname, prosrc FROM pg_catalog.pg_namespace n
 JOIN   pg_catalog.pg_proc p ON pronamespace = n.oid
 WHERE  nspname = 'public'
 ;
***実行例 [#k9823da3]
 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)

* ファンクションを削除(DROP)する [#ha666bb7]
作成したhelloworldファンクションを削除(DROP)する方法を以下に記します。~
ファンクションを削除(DROP)する構文は以下の通りです。
 DROP FUNCTION ファンクション名
今回作成した、helloworld()ファンクションを削除(DROP)してみます。
 sakuradb=> DROP FUNCTION helloworld();
 DROP FUNCTION
削除されたのが確認できます。
 sakuradb=> SELECT helloworld();
 ERROR:  関数helloworld()は存在しません
 LINE 1: SELECT helloworld();
                ^
 HINT:  指定名称、指定引数型に合う関数がありません。明示的な型キャストが必要かもしれません
 sakuradb=>

* まとめ [#w83dd434]
+CREATE OR REPLACE FUNCTION でファンクションを作成する。
+作成したFUNCTIONを実行すれば登録される。
+SELECT FUNCTION名 で実行できる。
+DROP FUNCTION名 で登録したファンクションを削除できる。

----
&smile;''[[PL/pgSQL入門ページに戻る>PL/pgSQL入門]]''

#htmlinsertpcsp(db-btm.html,db-sp.html)
PL/pgSQLとは？ の変更点

PL/pgSQLとは？の変更点
