PostgreSQL/PREPAREでPreparedStatementsを作成する

• Prev
• Next
• PostgreSQL

PostgreSQLのPREPAREでPreparedStatementsを作成する方法 †

JavaのPreparedStatementや、PHPの$dbh->prepare(...)などと同じような動作をPostgreSQLを使用してSQL文で実現する方法を以下に記します。

関連記事 †

• MySQLのPREPAREでPreparedStatementsを作成する方法
• MySQLでPREPAREしたクエリーを削除する方法・DEALLOCATE
• PostgreSQLでPREPAREしたクエリーを削除する方法・DEALLOCATE

動作確認環境 †

以下の環境で動作確認を行いました。

• OS
  CentOS6 x86_64
• PostgreSQL
  PostgreSQL 8.4.13

PREPAREを使ってみる †

PREPAREを使うとSQLの一時的な関数(のようなもの)を作成することができます。
以下にいくつかのPREPARE使用例を記します。
尚、このPREPAREで指定した設定内容は接続が切れると無くなります。

select now(); を PREPARE †

psqlで接続し以下のように入力します。
select now();をshow_nowという名前で登録します。
execute show_now;でPREPAREで指定したSQLが実行されているのが確認できます。

sakura=# PREPARE show_now as
sakura-# select now();
PREPARE
sakura=# EXECUTE show_now;
             now              
------------------------------
 2013-01-23 21:23:58.49768+09
(1 行)

上記で操作したSQLを添付しておきます。

prepare01.sql

以下のようにすれば端末から実行できます。

psql < prepare01.sql

実行結果

$ psql < prepare01.sql 
PREPARE
              now              
-------------------------------
 2013-01-23 21:28:05.100399+09
(1 行)

PREPAREで引数を指定する方法 †

JavaやPHPのPreparedStatementを使う場合、?を使いますよね。
以下、PREPAREで引数を指定する方法を記します。

1. テーブルの作成

   sakura=# CREATE TABLE foo (a int, b text);
   CREATE TABLE

2. データのインサート

   sakura=# INSERT INTO foo VALUES(1,'sakura');
   INSERT 0 1

3. データの確認

   sakura=# SELECT * FROM foo;
    a |   b    
   ---+--------
    1 | sakura
   (1 行)

4. PREPAREでINS_SQLを定義

   sakura=# PREPARE INS_SQL (integer, varchar) AS
   sakura-# INSERT INTO foo VALUES($1, $2);
   PREPARE

5. PREPAREでUPD_SQLを定義

   sakura=# PREPARE UPD_SQL (integer, varchar) AS
   sakura-# UPDATE foo SET b=$2 WHERE a=$1;
   PREPARE

6. EXECUTEを使ってINS_SQLを実行

   sakura=# EXECUTE INS_SQL(2,'tsubaki');
   INSERT 0 1
   sakura=# EXECUTE INS_SQL(3,'suzuran');
   INSERT 0 1

7. 上記で実行したINS_SQLでデータがインサートされているかを確認

   sakura=# SELECT * FROM foo;
    a |    b    
   ---+---------
    1 | sakura
    2 | tsubaki
    3 | suzuran
   (3 行)

8. EXECUTEを使ってUPD_SQLを実行

   sakura=# EXECUTE UPD_SQL(1,'さくら');
   UPDATE 1
   sakura=# EXECUTE UPD_SQL(2,'つばき');
   UPDATE 1
   sakura=# EXECUTE UPD_SQL(3,'すずらん');
   UPDATE 1

9. 上記で実行したUPD_SQLでデータがインサートされているかを確認

   sakura=# SELECT * FROM foo;
    a |    b     
   ---+----------
    1 | さくら
    2 | つばき
    3 | すずらん
   (3 行)

上記で操作したSQLを添付しておきます。

prepare02.sql

以下のようにすれば端末から実行できます。

psql < prepare02.sql

実行結果

$ psql < prepare02.sql 
DROP TABLE
CREATE TABLE
INSERT 0 1
 a |   b    
---+--------
 1 | sakura
(1 行)

PREPARE
PREPARE
INSERT 0 1
INSERT 0 1
 a |    b    
---+---------
 1 | sakura
 2 | tsubaki
 3 | suzuran
(3 行)

UPDATE 1
UPDATE 1
UPDATE 1
 a |    b     
---+----------
 1 | さくら
 2 | つばき
 3 | すずらん
(3 行)