Go and pgx. Pagination in Postgres database queries

Good day! I would like to share a life hack for implementing a universal method of pagination in arbitrary database queries to obtain lists.

We initially have the task of implementing an HTTP REST CRUD server in Go. Database – PostgreSQL. The driver used is pgx – PostgreSQL Driver and Toolkit.

The task is generally trivial – to make sure that queries receive pagination parameters as input (page number, number of records per page) and output table fields and the total available number of pages.

It’s easy to get the total number of pages in a database table:

SELECT count(*) FROM client_accounts WHERE <условия>

Also, without any problems, we can select the desired page using LIMIT and OFFSET:

SELECT * FROM client_accounts
WHERE <условия>		
LIMIT a_limit
OFFSET a_offset;

The whole point is that it is extremely undesirable to do this directly with two queries to the database. Among other things, purely theoretically, by the time the second query is executed, the data may become irrelevant if a record was added/deleted to the table at that time. You can, of course, use a transaction, however, this approach seemed cumbersome and not the most successful to me. Two requests are almost always worse than one. In addition, the WHERE sections in queries must be strictly identical.

It’s not that the task of combining these queries into one in itself is mega difficult, however, I personally couldn’t come up with or find a ready-made example.

As a result, after some time a solution was born, which I want to share.

Create a function:

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;
$$;

Here we are returning a table with two fields – one storing the table row with all the fields, the other storing the number of records. This is possible because whenever a table is created in PosgreSQL, a composite type is automatically created along with it to represent the row type of the table, whose name will be the table name.

The second nuance is the use of the OVER() construct, which turns the COUNT() aggregate function into a window function and allows us to place the total number of full_count records in each row of our sample in a separate column. As a result of executing this query, we will be returned a table with two columns. The first column will contain rows from the client_accounts table, the second will repeat the full_coun value corresponding to the total number of rows in the sample.

To get all the table columns plus the number of records column, we need to “expand” the first column in the selection. Therefore, when calling a function, we use the following query:

SELECT (list).*, full_count FROM get_account_list()

All that remains is to unmarshal the query result into a slice of structures. In this case I used the pgx driver and the pgxscan package:

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

In real projects, it will be convenient to create some kind of universal procedure for unmarshalling such queries, taking as input the name of the PostgreSQL function, its parameters, limit, offset, and a link to a slice of structures of an arbitrary type into which the resulting data will be placed.

The complete example code is available at GItHub