Печать страницы - Postgres параметры SELECT запроса списком.

Название: Postgres параметры SELECT запроса списком.
Отправлено: RedDog от Август 07, 2018, 19:56

Господа, как правильно в SELECT запрос передать список значений для фильтрации?

Вот такой код пишет, что ему мало параметров

Код

C++ (Qt)
QVariantList keys
    {
    QUuid("08a34f7d-7c20-4c58-a034-fba625ee11eb"),
    QUuid("96f86679-9b77-456d-8204-644e6f500d4c"),
    QUuid("a98c85b0-80b5-4a32-bb1f-7c3a7196cae8")
    };
 
    const QString sqlText = R"(SELECT * FROM "TestTable" WHERE "Id" IN(?);)";
 
    QSqlQuery select(m_database);
 
    select.prepare(sqlText);
    select.addBindValue(keys);
 
    if (!select.exec())
        qDebug() << select.lastError().text();

Цитировать

"ОШИБКА: неверное число параметров для подготовленного оператора \"qpsqlpstmt_1\"\nПОДРОБНОСТИ: Ожидалось параметров: 1, получено: 0.

попробовал вот через преобразования в текст делать типа такого:

Код:

SELECT * FROM "TestTable" WHERE "Id"::text IN(?);

тоже не работает.

В чем моя ошибка?

PS: запросы на вставку/удаление/обновление с переданными списками execBatch() работают.

Решение:

Код

SQL
SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);

Ключи преобразовать в строку с разделителем запятой, и эту строку передать как параметр в запрос.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: schmidt от Сентябрь 06, 2018, 15:29

Ну, разумеется ;)

С каждым параметром запроса можно связать только одно значение.

Цитировать

https://doc.qt.io/qt-5/sql-sqlstatements.html
If you want to insert many records at the same time, it is often more efficient to separate the query from the actual values being inserted. This can be done using placeholders. Qt supports two placeholder syntaxes: named binding and positional binding. Here's an example of named binding:

Код:

    QSqlQuery query;
    query.prepare("INSERT INTO employee (id, name, salary) "
                  "VALUES (:id, :name, :salary)");
    query.bindValue(":id", 1001);
    query.bindValue(":name", "Thad Beaumont");
    query.bindValue(":salary", 65000);
    query.exec();

Цитировать

SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);

Если вам заранее известны ваши UUID, то запрос делает лишнюю работу, вызывая unnest() и string_to_array(). Почему бы не хранить нужные вам UUID в другой таблице?

Цитировать

SELECT * FROM "TestTable" WHERE "Id" IN( SELECT "uuid" from needed_uuids )

Если список UUID хранится в исходном тексте, придется перекомпилировать программу, чтобы изменить условие запроса. Если нет возможности добавить в БД нужную таблицу, вынесите идентификаторы в конфиг приложения (есть QSettings). Тогда условие IN в запросе можно строить, используя QStringList::join().

Конечно, придется самому позаботиться и о проверке того, что включается в SQL-запрос: а содержит ли список корректные UUID или кто-то зловредный переписал их и включил опасный SQL-код? Например, пройтись по QStringList и попробовать создать из каждого QUuid. Делать такую проверку достаточно один раз - при запуске приложения во время чтения конфига.

Цитировать

Тема: можно ли записать QStringList в QSettings одним махом?
http://www.prog.org.ru/topic_14123_0.html

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: RedDog от Сентябрь 06, 2018, 19:11

Клиент запрашивает с сервера Id-шники, затем он их фильтрует, и посылает на сервак запрос на получение объектов по списку Id-шников, сервак, соответственно должен выдать только те объекты, которые есть в списке от клиента.
Так же клиент может независимо, свой список сгенерить по связям между объектами, или из своего кеша.
В общем случае, на сервере я не знаю, что запросит клиент.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: sergek от Сентябрь 06, 2018, 23:12

Просто сделайте текст sql-запроса без параметров. Например:

Код

C++ (Qt)
        QStringList keys;
        QString list = keys.join("','");
        QString sql = "SELECT * FROM TestTable WHERE Id IN('"+list+"')";
 
        QSqlQuery query(m_database);
        query.exec(sql);
 
        QSqlRecord rec = query.record();
        while(query.next()){
            QString id = query.value(rec.indexOf("id")).toString();
            ...
        }

Здесь для удобства я QVariantList заменил на QStringList, чтобы использовать join. Обратите внимание, в условии IN список строк, поэтому каждый uuid заключен в одинарные кавычки.
Писал в блокноте, могут быть синтаксические ошибки.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: RedDog от Сентябрь 07, 2018, 07:31

Так и было изначально. Но мое имхо, что это не совсем красиво и постгря будет кажлый раз компилять новый текст запроса. Когда как параметризированный, она компиляет единожды, и потом в него просто подставляет параметры.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: sergek от Сентябрь 07, 2018, 09:45

Цитата: RedDog от Сентябрь 07, 2018, 07:31

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

Поясните.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: RedDog от Сентябрь 07, 2018, 09:52

Если по простому prepare() на текст запроса не надо каждый раз вызывать

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: schmidt от Сентябрь 10, 2018, 09:06

1. Если данные приходят от клиента, обязательна проверка (validation) перед включением в запрос.

Код

C++ (Qt)
QStringList keys;
foreach (QString param, http_post.uuids) {
    keys.append(sqlEscapeString(param));
}
// И только после экранирования можно так:
QString list = keys.join("','");
QString sql = "SELECT * FROM TestTable WHERE Id IN('"+list+"')"

2. Мне все же кажется, что prepare() хорош для запроса, число параметров которого заранее известно:

Код

SQL
SELECT * FROM TABLE WHERE c1 = ? AND c2 = ? AND c3 = ?

В вашем случае IN - это не параметр, а условие с подзапросом. Число uuid заранее не известно - придут от клиента. Если использовать

Код

C++ (Qt)
QSqlQuery select("SELECT * FROM "TestTable" WHERE "Id" IN(SELECT unnest(string_to_array(?, ','))::uuid);");
select.addBindValue(comma_separated_string);

то вы, скорее всего, выиграете от prepare() - подготовленный запрос с одним параметром типа "строка" скомпилируется однажды, но за это заплатите временем на unnest и string_to_array. Если очень надо, поиграйтесь с замерами по времени на разных вариантах.

Название: Re: Postgres параметры SELECT запроса списком.
Отправлено: RedDog от Сентябрь 10, 2018, 20:32

1. От клиента приходит список QUuid-ов. Зачем еще валидация?
2. Запросы в параметризированном виде выполняются менее 20мс. Для моих целей этого более чем достаточно.
3. Подобная запись в моей текущей архитектуре позволяет унифицировать много моментов.

Russian Qt Forum

Qt => Базы данных => Тема начата: RedDog от Август 07, 2018, 19:56