Paginação para grupos no postgres

O Postgres trata um groupby como sendo uma tabela, ou seja, não há como utilizar limit ou offset para cada grupo resultante do groupby.

A alternativa encontrada no blog Show archive.org snapshot . A solução utiliza windows functions do postgres.

Primeiro criamos os grupos com partition, depois enumeramos as linhas de cada grupo com row_number(), por fim, utilizamos o where row_number > :offset_param and row_number < :limit_param.

Exemplo:

Este exemplo irá listar 10 linhas de resultado do join entre tabela A e B caso offset_param e limit_param sejam substituídos por 1 e 10 respectivamente.

CopySELECT *
FROM (SELECT
		  b.id                                    AS tabela_b_id,
		  row_number()
		  OVER (
			  PARTITION BY b.id
			  ORDER BY a.fk_b DESC )          AS linha_no_grupo,
		  a.*
	  FROM tabela_a a
		  JOIN tabela_b b ON a.fk_b = b.id) grupos
where grupos.linha_no_grupo >= :offset_param and grupos.linha_no_grupo <= :limit_param;

Observação: Deve lembrar que o limit_param não é igual o limit, pois deve ser calculado, limit_param = offset_param + limit