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