How to make pagination with dynamic condition in PostgreSQL?

Question

PostgreSQL

Hey! There is a large table containing a list of files (chunks that are later glued together (for the sitemap)), their weight and number of lines (changes periodically):

There are 2 variables: ROWS_LIMIT and SIZE_LIMIT It is
required to do pagination using the above conditional variables. \Page\, is considered until the sum of the selected data (rowCount or fileSize rows) hits the set limit.
Is such an implementation possible in pure PostgreSQL?

Answer 1

I think window functions will suit you, something like:

WITH 
file_list AS    (  
                SELECT  id
                        ,SUM(filesize) OVER (PARTITION BY component ORDER BY id) AS filesize_summary
                        ,SUM(rowcount) OVER (PARTITION BY component ORDER BY id) AS rowcount_summary
                FROM file_chunks
                )
SELECT * 
FROM file_chunks        c
INNER JOIN file_list    l ON c.id=l.id
WHERE   TRUE
        AND (filesize_summary<=_SIZE_LIMIT OR rowcount_summary<=ROWS_LIMIT);

Here in the CTE we select the id of the files that fall under the condition, and then by these ids we already select everything that is new from the main table. PARTITION BY is the area within which we summarize the data (that is, in the example, the sums by componentid will be different), ORDER BY is the order in which we accumulate the total.