Re: Variable LIMIT and OFFSET in SELECTs

Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> On 11/15/07, Reg Me Please <regmeplease(at)gmail(dot)com> wrote:
> > In any case, what'd be the benefit for not allowing "variables" as LIMIT
> > and OFFSET argument?
>
> When you can fully describe the semantics of your example, you'll
> probably be able to answer that question too :)

OK, I presume I've been unclear.

I need to have a "function returning a set of records" to send a "window" of
the complete data set. In my mind, LIMIT and OFFSET predicates are meant for
this purpose.

My original idea was a solution like this:

create table limoff( l int, o int ); -- only 1 line in this table
insert into limoff values ( 10,2 );
select a.* from atable a,limoff limit l offset o;

Unluckily this yelds the known problem about "variables".
I've tried to workaround the problem and infact this is doable:

First step, I encpasulate the LIMIT+OFFSET predicate in a SQL function.

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

It works.
Second step, I encapsulate the access to the limoff table in
another function:

create or replace function f_limoff()
returns setof atable as $$
select * from f_limoff_1( (select l from limoff),(select i from limoff) );
$$ language sql;

Also this works.
Please not that neither the LIMIT nor the OFFSET argument is
constant and are both contained in the limoff table.

So, in my opinion, the variable LIMIT and OFFSET is not a real problem
as in both cases the actual values of the arguments would be known only
at runtime. But for some reason, the first simpler solution leads to an error.

The question is: why not correcting the syjntax checker to allow also the
first solution?

--
Reg me Please