Re: Variable LIMIT and OFFSET in SELECTs

"Reg Me Please" <regmeplease(at)gmail(dot)com> writes:

> Of course, in my opinion at least, there's no real reason for the above
> syntax limitation, as the sematics is not.

Is not what? Is not sensible?

> create or replace function f_limoff_1( l int, o int )
> returns setof atable as $$
> select * from atable limit $1 offset $2
> $$ language sql;

I would guess what you're looking for is something like this:

select * from atable
where ...
order by ...
limit (select l from limoff where ...)
offset (select o from limoff where ...)

I can't think of any way to get those two subqueries down to one though.

If you create a set returning function like you have above then you can do
funny things with it to, eg, return all the rows in the ranges concatenated.

select (f_limoff_1(l,o)).*
from limoff
where ...

Note that you'll want to modify your function to include an ORDER BY

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!