Re: Variable LIMIT and OFFSET in SELECTs

Reg Me Please wrote:
> Il Thursday 15 November 2007 20:28:17 hai scritto:
>> Reg Me Please wrote:
>>> In my opinion I would say it's more a problem with the syntax checker
>>> that with the planner ("semantics" in my lingo). But I could be wrong.
>> Well, what it won't let you do is have a subquery in the LIMIT clause.
>> That's probably due to a combination of:
>> 1. The spec probably says something about it
>> 2. There is an obvious approach involving functions/prepared queries
>> 3. You're the first person to have asked for it.
>>
>> Now if you can get a couple of hundred to join you at #3, you might have
>> a feature request :-)
>
> Hmmm ...
>
> It also works with STABLE functions, though.

Well, STABLE means the value won't change during the query.

> 1. Unluckily I've been too lazy t read the specs.

A wise choice. They're not my idea of fun either.

> 2. I am not willing to put subqueries there, just need to drive the
> "windowing" mechanism by means of a second table (limoff).

And how do you plan to get information from limoff unless it's by a
subquery?

> 3. Dont' think so :)
>
> The solution I'm using now (two functions) allows for really variable limit
> and offset argument. It's just UGLY to write. But it works.
> And if it works it should be made available in a easier way (unless the specs
> advise otherwhise) with a simple table field (or a function parameter) as the
> argument for the LIMIT and for the OFFSET. Maybe with a huge warning about
> possible drawbacks with the query planner.

But you're back to subqueries here and the possibility of multiple
values from limoff. Even if you do something like:
   SELECT ... LIMIT (SELECT l FROM limoff LIMIT 1)
That doesn't guarantee you one row - you might get zero.

> I'll check whether I can drop a feature request, even without undreds of
> fellows.

Hey, anyone can request a feature. You're more likely to get it
implemented with a mix of coding skills, money and user-demand though.

--
   Richard Huxton
   Archonet Ltd