How to do pagination with a dynamic condition in PostgreSQL?

Hi! There is a large table containing a list of files (chunks which are later glued together (for Sitemap)), their weight and the number of lines (changing periodically):
5e27130abbad7803528959.png

There are 2 variables: ROWS_LIMIT and SIZE_LIMIT
You want to do pagination using the above conditional variables. \Page\ considered until the amount of fetched data (rows rowCount or fileSize) it hits the limit.

If such a clean implementation for PostgreSQL?
April 4th 20 at 13:12
1 answer
April 4th 20 at 13:14
Solution
I think you will fit window functions, 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 l ON c file_list.id=l.id
WHERE TRUE
 AND (filesize_summary<=_SIZE_LIMIT OR rowcount_summary<=ROWS_LIMIT);


Here in CTE select id files that fall under the condition, and then on these id already choose all that nuyno from the main table. PARTITION BY is the area within which summarize the data (that is, in the example, the amount of componentid will be different), ORDER BY is the order in which we accumulate the result.
Thank you very much, I will understand! - Barne commented on April 4th 20 at 13:17

Find more questions by tags PostgreSQLSQL