node-postgres :“WHERE col IN (

node-postgres :“WHERE col IN ()”쿼리?

---

다음과 같은 쿼리를 실행하려고합니다.

SELECT * FROM table WHERE id IN (1,2,3,4)

문제는 필터링하려는 ID 목록이 일정하지 않고 실행될 때마다 달라야한다는 것입니다. 소스의 신뢰성에 관계없이 실제로 쿼리에 들어가는 모든 것을 이스케이프하지만 신뢰할 수없는 소스에서 올 수 있기 때문에 ID도 이스케이프해야합니다.

node-postgres는 바인딩 된 매개 변수로 독점적으로 작동하는 것으로 보입니다 : client.query('SELECT * FROM table WHERE id = $1', [ id ]); 알려진 수의 값 ( client.query('SELECT * FROM table WHERE id IN ($1, $2, $3)', [ id1, id2, id3 ]))이 있으면 작동하지만 client.query('SELECT * FROM table WHERE id IN ($1)', [ arrayOfIds ])배열 매개 변수에 대한 특별한 처리가없는 것 같으므로 배열에서 직접 작동 하지 않습니다.

배열의 항목 수에 따라 쿼리 템플릿을 동적으로 빌드하고 ids 배열을 쿼리 매개 변수 배열로 확장합니다 (실제 경우에는 id 목록 외에 다른 매개 변수도 포함됨). node-postgres는 값 이스케이프 방법을 제공하지 않기 때문에 쿼리 템플릿의 ID 목록을 하드 코딩하는 것도 불가능 해 보입니다.

이것은 매우 일반적인 사용 사례처럼 보이므로 실제로는 무언가를 간과하고 IN (values)있으며 node-postgres와 함께 일반적인 SQL 연산자 를 사용할 수 없다는 것이 아닙니다 .

위에 나열된 것보다 더 우아한 방식으로이 문제를 해결 한 사람이 있거나 node-postgres에 대해 정말로 누락 된 부분이 있다면 도와주세요.

---

우리는 이전에 github 문제 목록에서이 질문을 보았습니다. 올바른 방법은 배열을 기반으로 매개 변수 목록을 동적으로 생성하는 것입니다. 이 같은:

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('$' + i);
}
var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')';
client.query(queryText, arr, function(err, cb) {
 ...
});

그렇게하면 postgres 매개 변수화 된 이스케이프를 얻을 수 있습니다.

---

@ebohlman의 답변 에 대한 귀하의 의견을 바탕으로 친한 것 같습니다 . 사용할 수 있습니다 WHERE id = ANY($1::int[]). PostgreSQL은 배열을 매개 변수가에서 캐스트되는 유형으로 변환 합니다 $1::int[]. 그래서 여기 저에게 맞는 인위적인 예가 있습니다.

var ids = [1,3,4]; 

var q = client.query('SELECT Id FROM MyTable WHERE Id = ANY($1::int[])',[ids]);

q.on('row', function(row) {
  console.log(row);
})

// outputs: { id: 1 }
//          { id: 3 }
//          { id: 4 }

---

내가 찾은 최고의 해결책 ANY은 Postgres의 배열 강제와 함께 함수 를 사용하는 것입니다. 이렇게하면 마치 작성한 것처럼 임의의 값 배열과 열을 일치시킬 수 있습니다 col IN (v1, v2, v3). 이것은의 접근 방식 페로의 대답 하지만 나는 여기의 성능을 보여 ANY와 동일합니다 IN.

질문

쿼리는 다음과 같아야합니다.

SELECT * FROM table WHERE id = ANY($1::int[])

끝에있는 비트는 $1::int[]"id"열의 유형과 일치하도록 변경할 수 있습니다. 예를 들어 ID 유형이 인 경우 인수를 UUID 배열에 강제로 uuid작성 $1::uuid[]합니다. Postgres 데이터 유형 목록은 여기를 참조하십시오 .

이것은 쿼리 문자열을 생성하는 코드를 작성하는 것보다 간단하며 SQL 삽입에 대해 안전합니다.

예

node-postgres를 사용하면 완전한 JavaScript 예제는 다음과 같습니다.

var pg = require('pg');

var client = new pg.Client('postgres://username:password@localhost/database');
client.connect(function(err) {
  if (err) {
    throw err;
  }

  var ids = [23, 65, 73, 99, 102];
  client.query(
    'SELECT * FROM table WHERE id = ANY($1::int[])',
    [ids],  // array of query arguments
    function(err, result) {
      console.log(result.rows);
    }
  );
});

공연

SQL 쿼리의 성능을 이해하는 가장 좋은 방법 중 하나는 데이터베이스가 쿼리를 처리하는 방법을 보는 것입니다. 샘플 테이블에는 약 400 개의 행과 "id"유형의 기본 키가 text있습니다.

EXPLAIN SELECT * FROM tests WHERE id = ANY('{"test-a", "test-b"}');
EXPLAIN SELECT * FROM tests WHERE id IN ('test-a', 'test-b');

두 경우 모두 Postgres는 동일한 쿼리 계획을보고했습니다.

Bitmap Heap Scan on tests  (cost=8.56..14.03 rows=2 width=79)
  Recheck Cond: (id = ANY ('{test-a,test-b}'::text[]))
  ->  Bitmap Index Scan on tests_pkey  (cost=0.00..8.56 rows=2 width=0)
        Index Cond: (id = ANY ('{test-a,test-b}'::text[]))

테이블 크기, 인덱스가있는 위치 및 쿼리에 따라 다른 쿼리 계획이 표시 될 수 있습니다. 쿼리 위의 사람을 좋아한다,뿐만 ANY와 IN같은 방식으로 처리됩니다.

---

pg-promise를 사용 하면 CSV 필터 (쉼표로 구분 된 값) 를 통해 잘 작동합니다 .

const values = [1, 2, 3, 4];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [values])
    .then(data => {
        console.log(data);
    })
    .catch(error => {
        console.log(error);
    });

And to address the concern about various data types, :csv modifier serializes the array into csv, while converting all values into their proper PostgreSQL format, according to their JavaScript type, even supporting the Custom Type Formatting.

And if you have mixed-type values like this: const values = [1, 'two', null, true], you still will get the correctly escaped SQL:

SELECT * FROM table WHERE id IN (1, 'two', null, true)

UPDATE

From v7.5.1, pg-promise started supporting :list as an interchangeable alias for the :csv filter:

db.any('SELECT * FROM table WHERE id IN ($1:list)', [values])

---

Another possible solution is to use the UNNEST function like this:

 var ids = [23, 65, 73, 99, 102];
 var strs = ['bar', 'tar', 'far']
 client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [ids],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);
client.query(
   'SELECT * FROM table WHERE id IN(SELECT(UNNEST($1))',
    [strs],  // array of query arguments
    function(err, result) {
       console.log(result.rows);
    }
);

I've used this in a stored procedure and it works fine. Believe it should work also from node-pg code.

You can read about the UNNEST function here.

---

Another posible solution is for example for REST API in NODE JS:

var name = req.body;//Body is a objetc that has properties for example provinces
var databaseRB = "DATABASENAME"
var conStringRB = "postgres://"+username+":"+password+"@"+host+"/"+databaseRB; 

var filter_query = "SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json((parameters) As properties FROM radiobases As lg WHERE lg.parameter= ANY($1) )As f) As fc";

var client = new pg.Client(conStringRB);
client.connect();
var query = client.query(new Query(filter_query,[name.provinces]));
query.on("row", function (row, result) {
  result.addRow(row);
});
query.on("end", function (result) {
 var data = result.rows[0].row_to_json
   res.json({
     title: "Express API",
     jsonData: data
     });
});

Keep in mind that any type of array can be used

---

The idea generally:

var invals = [1,2,3,4], cols = [...fields];
var setvs = vs => vs.map(v=> '$'+ (values.push(v))  ).join();

var values = [];
var text = 'SELECT '+ setvs(cols) +' FROM table WHERE id IN (' + setvs(invals) +')';

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