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