Go и pgx. Пагинация в запросах к БД Postgres
Доброго дня! Хочу поделиться лайфхаком по реализации универсального способа пагинации в произвольных запроса к БД для получания списков.
Имеем изначально задачу по реализации HTTP REST CRUD сервера на Go. База данных — PostgreSQL. Используемый драйвер — pgx — PostgreSQL Driver and Toolkit.
Задача в общем тривиальная — сделать так, чтоб запросы получали на вход параметры пагинации (номер страницы, количество записей на страницу) и отдавали на выход поля таблицы и общее доступное количество страниц.
Общее количество страниц в таблице БД получить несложно:
SELECT count(*) FROM client_accounts WHERE <условия>Также, без проблем мы можем выбрать нужную страницу используя LIMIT и OFFSET:
SELECT * FROM client_accounts
WHERE <условия>
LIMIT a_limit
OFFSET a_offset;Вся соль в том, что крайне нежелательно делать это в лоб двумя запросами к БД. Помимо прочего, чисто теоретически, к моменту выполнения второго запроса данные могут стать неактуальными, если в это время была добавлена/удалена запись в таблицу. Можно, конечно, использовать транзакцию, тем не менее, такой подход показался мне громоздким и не самым удачным. Два запроса — почти всегда хуже, чем один. Кроме того, в запросах секции WHERE должны быть строго одинаковыми.
Не то чтобы задача объединить эти запросы в один сама по себе — мега сложная, тем не менее, сходу придумать или найти готовый пример лично у меня не получилось.
В итоге, через какое-то время родилось решение, которым и хочу поделиться.
Создаем функцию:
CREATE OR REPLACE FUNCTION get_account_list(
IN a_limit INT,
IN a_offset INT
)
RETURNS TABLE (
list client_accounts,
full_count BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT a, count(*) OVER() AS full_count FROM client_accounts AS a
ORDER BY a.id ASC
LIMIT a_limit
OFFSET a_offset;
END;
$$;
Здесь мы возвращаем таблицу с двумя полями — в одном хранится строка таблицы со всеми полями, в другом — количество записей. Это возможно благодаря тому, что всякий раз, когда в PosgreSQL создается таблица, вместе с ней автоматически создается составной тип, представляющий тип строки таблицы, именем которого будет имя таблицы.
Второй нюанс заключается в использовании конструкции OVER (), которая превращает агрегатную функцию COUNT () в оконную и позволяет помещать общее количество записей full_count в каждую строку нашей выборки, в отдельную колонку. В результате выполнения данного запроса нам будет возвращаться таблица с двумя колонками. В первой колонке будут содержаться строки из таблицы client_accounts, во второй — будет повторяться значение full_coun, соответствующее общему количеству строк в выборке.
Чтобы получить все колонки таблицы плюс колонку количества записей, нам нужно «раскрыть» первую колонку в выборке. Поэтому, при вызове функции используем такой запрос:
SELECT (list).*, full_count FROM get_account_list()Остается демаршалить результат запроса в слайс структур. В данном случае я использовал драйвер pgx и пакет pgxscan:
err = pgxscan.Select(ctx, db.Pool, &data, "SELECT (list).*, full_count FROM get_account_list(10, 0)")
if err == nil {
for i, v := range data {
log.Printf("%d) Name: %s; Login: %s; Password: %s; EMail: %s; Role: %s",
i, v.Name, v.Login, v.Password, v.EMail, v.Role)
}
if len(data) > 0 {
log.Println("----------------------------------------")
log.Printf("Record count: %d", int(data[0].FullCount))
} else {
log.Println("there are no rows in the table")
}
}
В реальных проектах удобно будет создать некую универсальную процедуру для демаршалинга подобных запросов, принимающую на вход имя функции PostgreSQL, её параметры, limit, offset, и ссылку на слайс структур произвольного типа, в который будут помещены результирующие данные.
Полный код примера доступен на GItHub
