このエントリーをはてなブックマークに追加


[smile]PL/pgSQL入門ページに戻る


PL/pgSQLとは?

PostgreSQLを使用している環境で、PL/pgSQLを使った方が良いのにという場面があります。
たとえば、バッチ処理などが該当すると思います。
バッチ処理プログラムをJava, Ruby, Pythonなどで記述することも可能ですが、PL/pgSQLで記述することも可能です。
(プログラム言語で記述した場合は、データベースが変わっても流用しやすいというメリットがあります。)

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

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

関連サイト

Wikipediaへのリンク

お約束

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

PL/pgSQLで Hello world!

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

01.png

以下のPL/pgSQLファンクションは、ターミナル上にHello world!と表示し、クエリー結果としてHello World!を表示するサンプルです。

-- 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ファンクションの登録

上記のfilehelloworld.sqlをDBに登録する手順を以下に記します。
尚、使用するのはpsqlコマンドとなります。
pgAdminIII等のGUIツールで動作確認する場合は、読み替えてください。

cut&pasteで張り付ける

今回、サンプルソースも小さいのでcut&pasteで張り付けてもよいと思います。

  1. データベースに接続
    今回、テスト用にsakuradbというものを用意しました。皆さんの環境に合うように読み替えてください。
  2. 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コマンドでファンクションを登録する

psqlコマンドを使用してファンクションを登録します。
オプション-fによりファイルを指定してファンクションを登録しています。

sakura@cygwin ~/PLpgSQL$ psql -U sakura -h localhost sakuradb -f helloworld.sql
Password for user sakura:
CREATE FUNCTION

helloworldファンクションを実行

上記の操作でhelloworldファンクションの登録が完了しました。
早速、このファンクションを実行してみます。

  1. データベースに接続します。
    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=>
     
  2. 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! が返却されているのが確認できます。

ファンクション一覧の確認

登録されているファンクションの一覧を確認する場合は、以下の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)

ファンクションを削除(DROP)する

作成したhelloworldファンクションを削除(DROP)する方法を以下に記します。
ファンクションを削除(DROP)する構文は以下の通りです。

DROP FUNCTION ファンクション名

今回作成した、helloworld()ファンクションを削除(DROP)してみます。

sakuradb=> DROP FUNCTION helloworld();
DROP FUNCTION

削除されたのが確認できます。

sakuradb=> SELECT helloworld();
ERROR:  関数helloworld()は存在しません
LINE 1: SELECT helloworld();
               ^
HINT:  指定名称、指定引数型に合う関数がありません。明示的な型キャストが必要かもしれません
sakuradb=>

まとめ

  1. CREATE OR REPLACE FUNCTION でファンクションを作成する。
  2. 作成したFUNCTIONを実行すれば登録される。
  3. SELECT FUNCTION名 で実行できる。
  4. DROP FUNCTION名 で登録したファンクションを削除できる。

[smile]PL/pgSQL入門ページに戻る


添付ファイル: filehelloworld.sql 553件 [詳細] file01.png 933件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2016-10-22 (土) 00:11:32