Variable LIMIT and OFFSET in SELECTs

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Variable LIMIT and OFFSET in SELECTs
Date: 2007-11-15 01:21:07
Message-ID: 200711150221.08096.regmeplease@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.

I'd need to implement a "parametric windowed select" over a table
called "atable". The idea is to have a one row table to maintain
the LIMIT and the OFFSET for the selects. If I try this:

create table limoff( l int, o int );
insert into limoff values ( 10,2 );
select a.* from atable a,limoff limit l offset o;

I get "ERROR: argument of OFFSET must not contain variables".
(You get the error also on LIMIT if you put a constant as the offset).

But I can do the following:

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

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;

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

Wouldn't it be a nice enhacement to allow variable LIMIT and OFFSET in
SELECTs?

--
Reg me Please

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2007-11-15 02:06:47 Re: Path to top of tree
Previous Message Martin Gainty 2007-11-15 00:37:09 Re: stripping HTML, SQL injections ...