MySQL 派な自分にとって、初体験中の PostgreSQL の話です。CLI から利用する場合の（MySQL との）コマンドの違いに戸惑いましたが、まあ慣れてしまえばさほどは気になりません。

ただ１つ困ったことがありました。前提として自分はプログラミングで Node.js を使っていて、Node.js から PostgreSQL にアクセスするには node-postgres(pg) というライブラリパッケージを使っています。

今、ある配列（数値または文字列）があったとして、「その配列内のいずれかの値と一致する ID を持つレコードをすべて取り出す」という処理を実行したいとします。PostgreSQL 含めて一般的なリレーショナル・データベースであれば、"in" 句を使って以下のように処理できます：

> select * from mytable where id in ( '000', '001', '002' );

node-postgres を使った場合、この処理は以下のように記述することで同様に実行することができます：

var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    client.query( { text: "select * from mytable where id in ( '000', '001', '002' )", values: [] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

困ったことというのは、上述の配列部分を変数にした場合です。例えば以下のようにすると文法エラーにはなりませんが、（期待通りに展開されないのか）該当データが存在していても結果は空でした：

var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in ( $1 )", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

文法的にはこっちの書き方のほうが正しいかな、と思って以下の SQL に変えてみると、今度は文法エラーになってしまいました：

var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id in $1", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

-> syntax error at or near "$1"

いずれにせよ、配列部分を SQL 内で直接記述して具体的に指定すれば動くのですが、配列を変数化して実行する正しい方法がわかりませんでした。配列をループさせて SQL 文を直に作ればできないこともなさそうですが、そうすると SQL インジェクションにも気を付ける必要がでてくるので、配列変数のままでうまいこと実行する術はないだろうか、、、と悩んでいました。

で、やっとその解決策を見つけることができました。具体的には以下の方法です：

var PG = require( 'pg' );
var pg = new PG.Client({ 
    "postgres://user:pass@host:5432/db"
});
pg.connect( function( err, client ){
  if( err ){
    console.log( 'err00', err );
  }else{
    var ids = [ '000', '001', '002' ];
    client.query( { text: "select * from mytable where id = any($1::varchar[])", values: [ ids ] }, function( err, result ){
      if( err ){
        console.log( 'error', err );
      }else{
        console.log( 'success', result );
      }
    });
  }
});

SQL 文中の "varchar" 部分はテーブル定義した際の配列要素の型です。上の例では id は文字列（varchar）だったのでこのように varchar[] となりますが、int 型で定義していた場合は int[] などとなります。

このように記述すると PostgreSQL 側が実行時に any($1::varchar[]) 部を指定された型（varchar）のパラメータに自動変換してくれるらしく、SQL インジェクションの心配もなく実現できるようでした。


（参考）
https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query

タグ ：

#nodejs

#postgres

#postgresql

#node-pg

#node.js