node-postgres: как выполнить запрос «WHERE col IN (<список динамических значений>)»?

Я пытаюсь выполнить такой запрос:

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

Проблема в том, что список идентификаторов, по которым я хочу фильтровать, не является постоянным и должен отличаться при каждом выполнении. Мне также нужно было бы избежать идентификаторов, потому что они могут быть получены из ненадежных источников, хотя я бы на самом деле избежал всего, что идет в запросе, независимо от достоверности источника.

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 ]), так как, похоже, нет особой обработки параметров массива.

Динамическое построение шаблона запроса в соответствии с количеством элементов в массиве и расширение массива идентификаторов в массив параметров запроса (который в моем конкретном случае также содержит другие параметры, помимо списка идентификаторов) представляется необоснованно обременительным. Жесткое кодирование списка идентификаторов в шаблоне запроса также кажется нежизнеспособным, так как node-postgres не предоставляет методов, позволяющих избежать значений.

Это похоже на очень распространенный вариант использования, поэтому я предполагаю, что я действительно что-то упускаю, а не то, что невозможно использовать общийIN (values) Оператор SQL с нод-постгрес.

Если кто-нибудь решил эту проблему более элегантно, чем те, которые я перечислил выше, или если я действительно что-то упустил из-за node-postgres, пожалуйста, помогите.

 Ryan LaBarre24 мая 2012 г., 07:24
Я не пробовал это сам, но похоже, что вы хотите передать вложенный массив в качестве первого (и в данном случае только) элемента вашего массива подстановок, поскольку он ожидает, что каждый элемент в этом параметре будет значением подстановки. Пример: client.query («SELECT * FROM table WHERE id IN ($ 1)», [[arrayOfIds]]);
 lanzz24 мая 2012 г., 08:15
Нет, не работает таким образом (как и ожидалось). Очевидно, он пытается представить массив [1, 2, 3] как строковое значение «1,2,3», и сервер возвращает ошибку «неверный синтаксис ввода для целого числа».
 lanzz26 мая 2012 г., 19:32
Я не вижу никакого способа получить полный запрос, так как фактическое сообщение сетевого протокола передает шаблон запроса и список параметров отдельно серверу postgres, и вся окончательная компоновка происходит на стороне сервера. Даже в журнале postgres нет полного запроса:2012-05-26 20:31:08 EEST ERROR: invalid input syntax for integer: "1,3" 2012-05-26 20:31:08 EEST STATEMENT: SELECT * FROM users WHERE id IN ($1)
 Ryan LaBarre25 мая 2012 г., 19:12
Можете ли вы опубликовать полный запрос, который он пытается выполнить при передаче массива таким образом? Я не так хорошо знаком с Posgres, как MySQL, но разве вы не представляете этот запрос? IN (1,2,3) выглядит правильно для меня. Если я полностью не понял, что вы пытаетесь сделать.

Ответы на вопрос(7)

ANY работать с Postgres & apos; приведение массива. Это позволяет сопоставить столбец с произвольным массивом значений, как если бы вы выписалиcol IN (v1, v2, v3), Это подход вответ pero но здесь я показываю, что производительностьANY такой же какIN.

Query

Ваш запрос должен выглядеть так:

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

Этот бит в конце, который говорит$1::int[] можно изменить в соответствии с типом вашего & quot; идентификатора & quot; колонка. Например, если тип ваших идентификаторовuuidВы пишете$1::uuid[] привести аргумент к массиву UUID.Смотрите здесь список типов данных Postgres.

Это проще, чем написание кода для построения строки запроса, и это безопасно от SQL-инъекций.

Example

С помощью node-postgres полный пример JavaScript выглядит следующим образом:

var pg = require('pg');

var client = new pg.Client('postgres://username:[email protected]/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);
    }
  );
});
Performance

Один из лучших способов понять производительность 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 обрабатываются таким же образом.

 27 февр. 2018 г., 11:23
Обратите внимание, что хотя это верно для формы ЛЮБОЙ, принимающей набор, для каждой IN () и = ANY () существует вторая форма, и они не полностью эквивалентны. Рассматривать:stackoverflow.com/questions/34627026/…
 21 сент. 2016 г., 19:55
Удивительно! Это должен быть правильный ответ.
Решение Вопроса

динамически генерировать список параметров на основе массива. Что-то вроде этого:

var arr = [1, 2, "hello"];
var params = [];
for(var i = 1; i <= arr.length; i++) {
  params.push('

Таким образом вы получите параметризованный выход postgres.

+ i); } var queryText = 'SELECT id FROM my_table WHERE something IN (' + params.join(',') + ')'; client.query(queryText, arr, function(err, cb) { ... });

Таким образом вы получите параметризованный выход postgres.

 08 мар. 2014 г., 06:59
Предложение @srigi содержит ошибку. Это должно быть: var params = arr.map (function (item, idx) {return & reg; $ & reg; + (idx + 1);});
 03 мар. 2014 г., 22:22
Собирался закричать кровавое убийство при вводе открытого текста в запрос! Оказывается, это всего лишь символы доллара и цифры -_- ...
 15 апр. 2015 г., 17:42
Этот пример сломается, если какой-либо текстовый элемент в массиве будет содержать символы в одинарных кавычках ».
 10 февр. 2014 г., 15:42
Поскольку мы находимся в Node.js, вы можете безопасно использовать native map (). Это еще больше упрощает код: params = arr.map (function (item, idx) {return & reg; $ & reg; + idx});
 14 окт. 2016 г., 18:41
Обновление к этому ответу теперь находится в FAQ по node-postgres. Следующие работы:client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...); Посмотреть здесь:github.com/brianc/node-postgres/wiki/…

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) +')';

вы были близки, основываясь на вашем комментарии к @ ebohlman & apos; sответ, Ты можешь использовать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 }
 lanzz31 мая 2012 г., 10:35
Это означает, что еслиarr не содержит целых чисел, но, скажем, строк, которые содержат запятые или фигурные скобки, ваш код не будет работать или будет работать неправильно.
 lanzz31 мая 2012 г., 10:20
Здесь не хватает цитирования значений вarr, а такжеnode-postgres не предоставляет никаких методов цитирования. Я ищу "правильный" Таким образом, я не хотел реализовывать свой собственный код цитирования литералов SQL. Кроме того, если я продолжу в этом направлении, я бы предпочел встроить список идентификаторов непосредственно в шаблон запроса, а не подготавливать литерал массива только для повторного его анализа на стороне сервера.
 08 февр. 2014 г., 04:50
Таким образом, учитывая, что этот ответ использует параметризованный запрос и, следовательно, параметры анализируются и экранируются на сервере postgres, где угроза безопасности? Если есть, то есть большие проблемы.
 31 мая 2012 г., 10:27
Можете ли вы остановиться на том, что вы подразумеваете под "не хватает цитирования значений вarr& Quot; пожалуйста?
 31 мая 2012 г., 10:43
Конечно, это просто пример. Конечно, вы бы продезинфицироватьIN Параметры пункта до подготовки заявления независимо от реализации. Может быть, я неправильно понял основание вашего вопроса.

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
     });
});

Имейте в виду, что любой тип массива может быть использован

UNNEST функционировать так:

 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);
    }
);

Я использовал это в хранимой процедуре, и она работает нормально. Поверьте, это должно работать также из кода node-pg.

Вы можете прочитать о функции UNNESTВот.

 lanzz26 мар. 2015 г., 13:11
Это кажется огромным излишним, по сравнению сid = ANY($1) решения

пг-обещаниеэто хорошо работает через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);
    });

И для решения проблемы различных типов данных,:csv Модификатор сериализует массив в CSV, преобразуя все значения в их правильный формат PostgreSQL, в соответствии с их типом JavaScript, даже поддерживаяФорматирование пользовательских типов.

И если у вас есть смешанные значения типа этого:const values = [1, 'two', null, true], вы все равно получите правильно экранированный SQL:

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

UPDATE

С версии 7.5.1,пг-обещание начал поддерживать:list как сменный псевдоним для:csv фильтр:

db.any('SELECT * FROM tabl,e WHERE id IN ($1:list)', [values])
 03 мая 2018 г., 13:48
Люблю эту библиотеку

Ваш ответ на вопрос