スポンサーリンク

PREPARE文と，PL/pgSQL の入門　　（PostgreSQLで「動的に」SQLを実行するために，プリペアド・クエリやストアドファンクションを定義しよう）

DB PostgreSQL PL/pgSQL

複数のINSERT文を「動的に」発行するには，どうしたらよいか？



例えば，都合のいいサンプルデータをDBに大量投入したいとか。

その場合，INSERT文を大量に発行することになる。どうやって？

  • （案１）SQLをExcelで大量生成
  • （案２）PREPARE文をExcelで大量生成
  • （案３）PL/pgSQLを使って，手続き型の処理を実行


できれば，「forループ」のような制御構文と「INSERT文」を組み合わせたい。

そのためには，上記（案３）のPL/pgSQLを使うとよい。

本稿では，そこまでの道のりを，順を追って解説する。


（案１）SQLをExcelで大量生成

「大量のSQL文を生成して実行するのが面倒だ！」という時，一番安直なのは，

Excelのフィル機能を使う方法。

手順：

  • （１）DBの列の値を，Excelのシート上にコピペ
  • （２）Excelのセル上で， & 演算子やCONCATINATE()などを使って，SQL文を作成
  • （３）フィル機能を使って，SQLを大量生成
  • （４）シート上から全SQLをコピーし，DBクライアントにペーストして実行

これは結局，手動だ。

もっと簡易化・自動化できないか。


（案２）PREPARE文をExcelで大量生成

PostgreSQLでは，「プリペアード・クエリ」を定義し，実行できる。

一時的に定義された「関数」のようなもの。


PREPARE文でプリペアドクエリを定義：

PREPARE
  予約名 ( 引数の型 )
AS
  SQL文
;

SQL文中で，引数は $1，$2 などして参照できる。


定義したクエリの実行：

EXECUTE 予約名( 引数の値 );

PostgreSQL の PREPARE
http://www.odin.hyork.net/write/write...

  • PREPARE は PostgreSQL において SQL 文の予約をしてくれるメソッドです。 SQL 文の一時的な関数化


PREPARE文
http://www.postgresql.jp/document/pg7...

  • プリペアードクエリは、パフォーマンスの最適化に利用できるサーバ側オブジェクトです
  • PREPARE 文を実行すると、指定された問い合わせが構文解析され、書き換えられ、そして計画されます。その後に EXECUTE 文が発行されると、プリペアードクエリは実行される
  • プリペアードクエリは、ローカル (現行のバックエンド) に格納され、現行のデータベースセッションの間のみ存在


Prepared Statementの威力
http://www.postgresql.jp/blog/88

  • 実行するSQL文をあらかじめバックエンド側に渡しておき、実行時にはパラメータだけを渡して実行する、というもの
  • バックエンドでSQL文を実行するたびに処理されている「構文解析（パーサ）」と「実行プランの作成と最適化（プランナとオプティマイザ）」の処理を飛ばして性能を向上させることができる


以下は具体例。


INSERT文をプリペアードクエリにする方法：

-- 定義
PREPARE hoge_sql( integer )
AS
  INSERT INTO
    fuga
    ( col1, col2 )
  VALUES
    ( $1, 'aaa' )
;

-- 実行
EXECUTE hoge_sql( 1 );
EXECUTE hoge_sql( 2 );
EXECUTE hoge_sql( 3 );


「EXECUTE」だけで済むので，SQL全文を繰り返し書くよりは楽だ。


だが結局，EXECUTE文を複数並べた部分は，（Excelのフィル機能とかで）手動で作るしかない。

もっと自動化するには，どうしたらよいか。


（案３）PL/pgSQLを使って，手続き型の処理を実行

PostgreSQLでは，ユーザ定義の関数（ストアドファンクション）を作成できる。

この場合，関数を書くためのプログラミング言語として

  • language 'sql'
  • language 'plpgsql'

などの中からどれか選ぶ。



前者の　language 'sql'　だと，できる事は非常に少ない。

SELECT文を１つ書く事しかできず，INSERT文は書けない。

これでは，先に見たPREPARE文と比べてメリットが無い。

SQLによるユーザ定義関数
http://www.fireproject.jp/feature/pos...

  • 複雑なSELECT文を関数化できる


sql insert function
http://bytes.com/topic/postgresql/ans...

  • language 'sql' でINSERT文を発行したいが，返り値が必要なのでSELECT文を後ろに書いている
  • でも，複数の文からなる関数は 　language 'sql'　では作れない。
  • なので，INSERT文を発行することができない。


PostgreSQL 編19 - ストアドファンクション、function、PL/pgSQL、トリガー
http://homepage2.nifty.com/sak/w_sak3...

  • language 'sql' では、単純なものしか作成できない。
  • Oracle の PL/SQL と同等のものは、以下で説明する PL/pgSQL で作成する。

なので，INSERT文の動的発行などの複雑な処理を関数化したい場合，

後者の　「language 'plpgsql'」を選択する。


つまり，PL/pgSQLを使って，ストアドファンクションを作成すればよい。


以下はその入門。


導入

PL/pgSQLのインストール方法(Linux)

/usr/local/pgsql/bin/createlang plpgsql データベース名


※念のため，アンインストール方法：

/usr/local/pgsql/bin/droplang plpgsql データベース名

Chapter 18. 手続き言語
http://www.postgresql.jp/document/pg7...

  • PostgreSQLでは、関数やプロシージャを書くために新たなプログラミング言語を追加することが可能です。これらは手続き言語(PL)と呼ばれています。


18.2. 手続き言語のインストール
http://www.postgresql.jp/document/pg7...

かんたんな関数を定義してみよう

引数に１を足して返却するような関数を定義：

-- int型の引数を１つ取る関数とする
CREATE OR REPLACE FUNCTION func_hoge( arg_num int ) RETURNS int
AS $$
  DECLARE
    temp_num int;
  BEGIN
    -- 引数に１を足して返却する
    temp_num = arg_num + 1;
    RETURN temp_num;
  END;
$$ LANGUAGE plpgsql
;

関数宣言時には，引数に型（int とか）を添える。



作った関数を実行：

SELECT func_hoge( 1 ); -- 2が返る
SELECT func_hoge( 2 ); -- 3が返る


作った関数のソースコードを確認：

-- prosrcカラムの中にソースコードが表示される
SELECT
  *
FROM
  pg_proc
WHERE
  proname = 'func_hoge'
;

なお，同じ関数名であっても，引数の型や個数が異なれば別個の関数として保存される。


作った関数を削除する方法：

-- 引数の型も指定して特定する必要がある
DROP FUNCTION
  func_hoge( int )
;

A Basic Introduction to Postgres Stored Procedures
http://www.eioba.com/a70583/a_basic_i...

  • the very basics of writing stored procedures with PostgreSQL
  • SELECT COUNT(*) INTO qty で，SELECT文の実行結果を変数に代入して返却している


PostgreSQL 編21 - PL/pgSQL、構文書式、制御構造、メッセージ、動的 SQL
http://homepage2.nifty.com/sak/w_sak3...

  • 関数の構文
  • execute 文字列;　とすれば，動的にSQLを実行できる


CREATE FUNCTIONにて定義された関数（ストアドファンクション）が既に存在しています。その定義内容を確認したい
http://q.hatena.ne.jp/1174293797


なお，もし「SELECT文の実行結果」を変数に代入したい場合は，「INTO」を使えばよい。

38.5.3. 1行の結果を返す問い合わせの実行
http://www.postgresql.jp/document/8.4...

  • 1行を返す（多分、複数列の）SQL コマンドの結果は、レコード変数、行型の変数、スカラ変数のリストに代入することができます。これは、基本的なSQL コマンドを記述して、それにINTO句を追加することによって行われます。


PostgreSQL 編22 - PL/pgSQL、カーソル for ループ、カーソル制御、FETCH
http://homepage2.nifty.com/sak/w_sak3...

  • select count(*) into ct from testm;

INSERT文を動的に実行してみよう

本題。

INSERT文を動的に発行するには，どうしたらよいか。


具体的に言うと，

特定のテーブルを参照しつつ，そこから引っ張ってきたデータにあわせたINSERT文を動的に実行したい。


サンプルとなる状況：

  • usersテーブルのnameカラムには，ユーザの氏名が格納されている。
  • bookmarksテーブルに，全ユーザについて，サンプルのレコードを投入したい。

つまり，特定のテーブル内のデータにあわせて，動的に複数の（大量の）INSERT文を構築＋発行したい。



PL/pgSQLで，以下の関数を定義すればよい。

-- INSERT文を複数回発行してくれる関数。
-- ・引数のない関数（＝単なるプロシージャ）とする。
-- ・手続き実行完了時にはtrueを返す。
CREATE OR REPLACE FUNCTION func_hoge() RETURNS bool
AS $$
  DECLARE
    -- usersテーブル内の１行のレコードを表す変数
    user_record record;
  BEGIN
    -- usersテーブル内の全レコードについて，ループする
    FOR user_record IN ( SELECT * FROM users ) LOOP
      -- INSERT文を文字列として構築し，実行
      EXECUTE
        'INSERT INTO bookmarks ( name, url ) VALUES ( ''' ||
        user_record.name ||
        ''', ''http://hoge.com'' );'
      ;
    END LOOP;
    
    -- 処理が終了したのでとりあえず真を返す
    RETURN true;
  END;
$$ LANGUAGE plpgsql
;

FORループで全ユーザをスキャンし，各ユーザごとに新規レコードをINSERTしている。


INSERT文は文字列として構築されているので，変数と組み合わせることにより，

テーブル名やカラム名を動的に指定することもできる。

これはストアドファンクションの大きな特徴だ。

プリペアド・クエリだと，それはできない。（事前コンパイルがあるから）


実行方法：

SELECT
  func_hoge()
;

これで，usersテーブル内の全「ユーザ情報」について，それぞれ「ブックマーク情報」が新規登録された。


発展：PL/pgSQLの学習を進めてゆくために

以下のサイトを読んで，PL/pgSQLの学習を進めてゆける。


サンプル集：

PostgreSQLのストアドプロシージャ　ファンクションの例
http://ja.wikipedia.org/wiki/%E3%82%B...


PL/pgSQLサンプル
http://sites.google.com/site/itigyoun...

  • 関数のサンプル集


文法のまとめ：

第 35章PL/pgSQL - SQL手続き言語
http://www.postgresql.jp/document/pg8...


PostgreSQL 編21 - PL/pgSQL、構文書式、制御構造、メッセージ、動的 SQL
http://homepage2.nifty.com/sak/w_sak3...


PostgreSQL 編22 - PL/pgSQL、カーソル for ループ、カーソル制御、FETCH
http://homepage2.nifty.com/sak/w_sak3...


PostgreSQL 編23 - PL/pgSQL、テーブルレコード件数一覧表示
http://homepage2.nifty.com/sak/w_sak3...


PostgreSQL 編24 - PL/pgSQL、テーブル構造一覧表示、標準、動的SQL、LIKE
http://homepage2.nifty.com/sak/w_sak3...


ところで，PL/pgSQLはOracleのPL/SQLの類似品だ。

Oracleの情報のほうが多い。

できれば，そっちから入ったほうが覚えやすい。


とはいっても，Oracleは個人で買える代物ではない。学習の機会も限られる。

また，世の中はOracleのほうが情報が多いので，

「PL/SQLを抜きにして，PL/pgSQLだけに特化した情報」というのは得にくい。


だから，開発用のメイン言語としてPL/pgSQLを選択するのは，控えたほうがよいかもしれない。

もしプログラミング中に壁にぶつかった時，その壁を乗り越えるための情報が得づらいのだ。




とはいっても，開発「サポート用」の言語としてPL/pgSQLは非常に有用だ。

例えば，

  • DB内にサンプルデータを大量に投入したい
  • スキーマ変更に伴い，全データをちょっと加工したい

といったニーズが発生したとする。

通常は，わざわざちょっとしたDB操作だけのために，真面目にプログラムを書かなければならない。

DBに接続する所から書き始めないといけないだろう。



しかし，もしPL/pgSQLを知っていれば，

「DB上で【直接】動作してくれる関数」

を作って，すばやくデータ操作できるのだ。



ちょうど，日常の雑務を手早く片付けるためにバッチやrakeタスクを書くのと同じ感覚だ。

雑務を手早く処理したいという目的で，ストアドファンクションを書いて実行する。

PL/pgSQLは，そういった「ちょっとしたデータ一括操作」をこなすポジションにあるプログラミング言語，と言えるだろう。



　

language_and_engineering 読者になる

広告を非表示にする
スポンサーリンク
スポンサーリンク