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
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 ... |