Thread: Variable LIMIT and OFFSET in SELECTs

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

Reg Me Please <regmeplease@gmail.com> writes:
> create table limoff( l int, o int );
> insert into limoff values ( 10,2 );
> select a.* from atable a,limoff limit l offset o;

I am truly curious what you think the semantics of that ought to be.

            regards, tom lane

In any case, what'd be the benefit for not allowing "variables" as LIMIT and
OFFSET argument?

--
Reg me Please

Hello

LIMIT has impact on execution plan, so there cannot be variables. Use
SRF function and dynamic statements instead.

Regards
Pavel

On 15/11/2007, Reg Me Please <regmeplease@gmail.com> wrote:
> In any case, what'd be the benefit for not allowing "variables" as LIMIT and
> OFFSET argument?
>
> --
> Reg me Please
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

On 11/15/07, Reg Me Please <regmeplease@gmail.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 :)

Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> On 11/15/07, Reg Me Please <regmeplease@gmail.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

On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote:
> Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> > On 11/15/07, Reg Me Please <regmeplease@gmail.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 :)
>
> The question is: why not correcting the syntax checker to allow also the
> first solution?

In relational algebra terms, try thinking about what would happen if you
did something like:

  SELECT * FROM foo LIMIT val;

Where the table foo has more than one row (and val had different values
for each row).  Which row would the database use?  I believe these are
the semantics Trevor was referring to.

In implementation terms, the problem is that a query is planned without
getting any data from the database.  If you're planning a query it helps
to know how many rows you're getting back.  If you're getting few rows
back then it's probably better to make the query work differently than
if it's returning lots of rows.  Therefore, knowing what the LIMIT is,
at planning time, makes a lot of difference.  How would this work in the
presence of arbitrary expressions for LIMIT?


  Sam

Il Thursday 15 November 2007 17:55:42 Sam Mason ha scritto:
> On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote:
> > Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto:
> > > On 11/15/07, Reg Me Please <regmeplease@gmail.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 :)
> >
> > The question is: why not correcting the syntax checker to allow also the
> > first solution?
>
> In relational algebra terms, try thinking about what would happen if you
> did something like:
>
>   SELECT * FROM foo LIMIT val;
>
> Where the table foo has more than one row (and val had different values
> for each row).  Which row would the database use?  I believe these are
> the semantics Trevor was referring to.
>
> In implementation terms, the problem is that a query is planned without
> getting any data from the database.  If you're planning a query it helps
> to know how many rows you're getting back.  If you're getting few rows
> back then it's probably better to make the query work differently than
> if it's returning lots of rows.  Therefore, knowing what the LIMIT is,
> at planning time, makes a lot of difference.  How would this work in the
> presence of arbitrary expressions for LIMIT?
>
>
>   Sam

Your remarks make a lot of sense. Of course.

But then why allowing the LIMIT and the OFFSET as coming from function
argument evaluations?
Isn't this breaking the planner?
Of course I would say no, as in my case it's just working fine, only
more complex to write that the "simpler" way.

--
Reg me Please

Sam Mason <sam@samason.me.uk> writes:
> In relational algebra terms, try thinking about what would happen if you
> did something like:

>   SELECT * FROM foo LIMIT val;

> Where the table foo has more than one row (and val had different values
> for each row).  Which row would the database use?  I believe these are
> the semantics Trevor was referring to.

Exactly: if the numbers are coming from within the query then there is
no guarantee that there's a unique value to use.  The numbers can be
variable so long as they come from "outside" the query and so are fixed
over any one query execution.  That includes the case of a subselect
getting its limit from the outer query, for instance ... but not what
the OP was trying to do.

            regards, tom lane

On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote:
> Your remarks make a lot of sense. Of course.

good! it's interesting to see how things like this fit together.

> But then why allowing the LIMIT and the OFFSET as coming from function
> argument evaluations?

I believe the query is planned by ignoring the LIMIT and OFFSET.

> Isn't this breaking the planner?
> Of course I would say no, as in my case it's just working fine, only
> more complex to write that the "simpler" way.

Strictly speaking, I don't see why you couldn't put an IMMUTABLE
expression into a LIMIT or OFFSET.  The query could be planned, by
ignoring the LIMIT and OFFSET, and then when the actual query was
executed they would be evaluated and the results fed into the right
places.  I guess it's not been a problem before so nobody's spent the
time/effort to write all the code.


  Sam

Sam Mason <sam@samason.me.uk> writes:
> On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote:
>> But then why allowing the LIMIT and the OFFSET as coming from function
>> argument evaluations?

> I believe the query is planned by ignoring the LIMIT and OFFSET.

No, it still knows there will be a LIMIT in effect, but it has to guess
at how tight the limit is.  From memory I think it assumes 10% of the
total rows will be retrieved, which is a compromise to try to avoid
being spectacularly bad for either small or large limits.

            regards, tom lane

Sorry but I don't understand.

Either the LIMIT and OFFSET are to be definitely CONSTANT or not.

In the SQL function body the LIMIT and the OFFSET *are definitely not*
CONSTANT. And the planner can do its job at best as usual.

As Sam says I should be able to "put an IMMUTABLE expression into a LIMIT or
OFFSET". And under some circumstances (SQL function body) it's true even with
VARIABLE expressions like function call arguments.

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.

--
Reg me Please

Reg Me Please wrote:
> Sorry but I don't understand.
>
> Either the LIMIT and OFFSET are to be definitely CONSTANT or not.

They must be constant during the execution of the query.

> In the SQL function body the LIMIT and the OFFSET *are definitely not*
> CONSTANT. And the planner can do its job at best as usual.

Well, they're constant during one execution of the query, but I grant
you the planner doesn't know what values they will have.

> As Sam says I should be able to "put an IMMUTABLE expression into a LIMIT or
> OFFSET". And under some circumstances (SQL function body) it's true even with
> VARIABLE expressions like function call arguments.

And you can.

CREATE FUNCTION limfunc() RETURNS integer AS 'SELECT 2' LANGUAGE SQL
IMMUTABLE;

SELECT * FROM fit LIMIT limfunc();
  a | b
---+----
  1 | 43
  2 | 43
(2 rows)


> 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 :-)

--
   Richard Huxton
   Archonet Ltd

Il Thursday 15 November 2007 20:28:17 hai scritto:
> Reg Me Please wrote:
> > Sorry but I don't understand.
> >
> > Either the LIMIT and OFFSET are to be definitely CONSTANT or not.
>
> They must be constant during the execution of the query.
>
> > In the SQL function body the LIMIT and the OFFSET *are definitely not*
> > CONSTANT. And the planner can do its job at best as usual.
>
> Well, they're constant during one execution of the query, but I grant
> you the planner doesn't know what values they will have.
>
> > As Sam says I should be able to "put an IMMUTABLE expression into a LIMIT
> > or OFFSET". And under some circumstances (SQL function body) it's true
> > even with VARIABLE expressions like function call arguments.
>
> And you can.
>
> CREATE FUNCTION limfunc() RETURNS integer AS 'SELECT 2' LANGUAGE SQL
> IMMUTABLE;
>
> SELECT * FROM fit LIMIT limfunc();
>   a | b
> ---+----
>   1 | 43
>   2 | 43
> (2 rows)
>
> > 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.

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

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

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.

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


--
Reg me Please

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

On Thu, Nov 15, 2007 at 07:28:17PM +0000, Richard Huxton wrote:
> Reg Me Please wrote:
> >As Sam says I should be able to "put an IMMUTABLE expression into
> >a LIMIT or OFFSET". And under some circumstances (SQL function
> >body) it's true even with VARIABLE expressions like function call
> >arguments.
>
> And you can.
>
> CREATE FUNCTION limfunc() RETURNS integer AS 'SELECT 2' LANGUAGE SQL
> IMMUTABLE;
>
> SELECT * FROM fit LIMIT limfunc();
>  a | b
> ---+----
>  1 | 43
>  2 | 43
> (2 rows)

wow, that's kind of fun isn't it.  I only thought you could put a
constant in there.  Maybe I should have had a look in the grammar/tested
it first!


  Sam

Sam Mason <sam@samason.me.uk> writes:
> wow, that's kind of fun isn't it.  I only thought you could put a
> constant in there.  Maybe I should have had a look in the grammar/tested
> it first!

IIRC, it used to be restricted to a constant, a few revisions back.
In current releases the only restriction that stems from laziness is
not allowing a sub-select.  (If anyone were to put forward a serious
use-case, we'd probably go fix that.)

The OP's complaint is that we don't allow a variable of the query's own
level, but AFAICT he's still not grasped the point that that leads to an
indeterminate limit value ...

            regards, tom lane

On Fri, Nov 16, 2007 at 01:38:30AM -0500, Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
> > wow, that's kind of fun isn't it.  I only thought you could put a
> > constant in there.  Maybe I should have had a look in the grammar/tested
> > it first!
>
> IIRC, it used to be restricted to a constant, a few revisions back.
> In current releases the only restriction that stems from laziness is
> not allowing a sub-select.  (If anyone were to put forward a serious
> use-case, we'd probably go fix that.)

I'm amazed it supports anything more than a constant.  The values are
almost always going to come from external code, so there doesn't seem
much point in doing anything else.

I suppose with all the expression evaluation code already in PG,
supporting what it currently does isn't hard.

> The OP's complaint is that we don't allow a variable of the query's own
> level, but AFAICT he's still not grasped the point that that leads to an
> indeterminate limit value ...

Being too close to a problem makes it very easy to forget about the
general case.  I probably do this far too often myself!


  Sam

Il Thursday 15 November 2007 23:08:10 Richard Huxton ha scritto:
> 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.

You are right: in SQl anything is a query. So I'll close this.
The thing is doable and working. The syntax parser doesn't allow it,
maybe because of a bug, but there's a workaround. This's enough for myself.

--
Reg me Please

Il Friday 16 November 2007 07:38:30 Tom Lane ha scritto:
> IIRC, it used to be restricted to a constant, a few revisions back.
> In current releases the only restriction that stems from laziness is
> not allowing a sub-select.  (If anyone were to put forward a serious
> use-case, we'd probably go fix that.)

Mine was not serious enough!
Sigh! :-)

> The OP's complaint is that we don't allow a variable of the query's own
> level, but AFAICT he's still not grasped the point that that leads to an
> indeterminate limit value ...

So it works, but it's not serious enough to be unlocked.
Sigh! :-)

--
Reg me Please

Sam Mason <sam@samason.me.uk> writes:
> On Fri, Nov 16, 2007 at 01:38:30AM -0500, Tom Lane wrote:
>> IIRC, it used to be restricted to a constant, a few revisions back.

> I'm amazed it supports anything more than a constant.  The values are
> almost always going to come from external code, so there doesn't seem
> much point in doing anything else.
> I suppose with all the expression evaluation code already in PG,
> supporting what it currently does isn't hard.

Right.  There's clearly some value in supporting a parameter ($n),
and once we do that, given the existing expression-evaluation
infrastructure, we might as well allow everything except sub-selects.
(Sub-selects would cost a bit more because the planner would have to
deal with 'em.  Maybe not even a lot more ... I've not looked at it.)

            regards, tom lane

Reg Me Please <regmeplease@gmail.com> writes:
>> The OP's complaint is that we don't allow a variable of the query's own
>> level, but AFAICT he's still not grasped the point that that leads to an
>> indeterminate limit value ...

> So it works, but it's not serious enough to be unlocked.

You really don't have a clue what this is about, do you?
Let me try to explain one more time.  You propose allowing

    select ... from
      table1 join table2 on table1.x = table2.y
      limit table1.z

Now this would be somewhat well-defined if there were only one row in
table1, or if there were many rows but they all had the same value
in column z.  But what if that's not the case?  If there are multiple
possible values for z then the query is just not sensible.

            regards, tom lane

Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:
> Reg Me Please <regmeplease@gmail.com> writes:
> >> The OP's complaint is that we don't allow a variable of the query's own
> >> level, but AFAICT he's still not grasped the point that that leads to an
> >> indeterminate limit value ...
> >
> > So it works, but it's not serious enough to be unlocked.
>
> You really don't have a clue what this is about, do you?
> Let me try to explain one more time.  You propose allowing
>
>     select ... from
>       table1 join table2 on table1.x = table2.y
>       limit table1.z
>
> Now this would be somewhat well-defined if there were only one row in
> table1, or if there were many rows but they all had the same value
> in column z.  But what if that's not the case?  If there are multiple
> possible values for z then the query is just not sensible.
>
>             regards, tom lane

You're right, maybe I have no clue at all.
(Now it seems I'm maybe a little bit less clueless).
And you are right, the fragment you propose depicts my goal.
And, once again, you are right with "this would be somewhat well-defined if
there were only one row in table1".

I know this:
---------------------------------
tmp=# CREATE SEQUENCE s1;
CREATE SEQUENCE

tmp=# CREATE SEQUENCE s2;
CREATE SEQUENCE

tmp=# CREATE TABLE seqs ( seq text primary key );
CREATE TABLE

tmp=# INSERT INTO seqs VALUES ( 's1' );
INSERT 0 1

tmp=# SELECT * from nextval( (select seq from seqs) );
 nextval
---------
       1
(1 row)

tmp=# INSERT INTO seqs VALUES ( 's2' );
INSERT 0 1

tmp=# SELECT * from nextval( (select seq from seqs) );
ERROR:  more than one row returned by a subquery used as an expression
---------------------------------

(Semantics: I put a sequence name into a table and the nextval() will be
computed over that table row content at the call time. If I put more than one
row I get an error (maybe from the planner) so I need to ensure that the table
will contain just one row).

I would not to lock features (or capabilities) to avoid people making
mistakes! Because you could hinder careful people from exploiting them.

In any case, I need to thank you all very much for the new things I've learnt
about PostgreSQL.

--
Reg me Please

On 11/16/07, Reg Me Please <regmeplease@gmail.com> wrote:
> Il Friday 16 November 2007 08:33:14 Tom Lane ha scritto:

> > Let me try to explain one more time.  You propose allowing
> >
> >       select ... from
> >         table1 join table2 on table1.x = table2.y
> >         limit table1.z
> >
> > Now this would be somewhat well-defined if there were only one row in
> > table1, or if there were many rows but they all had the same value
> > in column z.  But what if that's not the case?  If there are multiple
> > possible values for z then the query is just not sensible.

> tmp=# SELECT * from nextval( (select seq from seqs) );
> ERROR:  more than one row returned by a subquery used as an expression

This is a subselect, not a column from the query itself.  Completely
different thing.

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Sam Mason <sam@samason.me.uk> writes:
>> In relational algebra terms, try thinking about what would happen if you
>> did something like:
>
>>   SELECT * FROM foo LIMIT val;
>
>> Where the table foo has more than one row (and val had different values
>> for each row).  Which row would the database use?  I believe these are
>> the semantics Trevor was referring to.
>
> Exactly: if the numbers are coming from within the query then there is
> no guarantee that there's a unique value to use.  The numbers can be
> variable so long as they come from "outside" the query and so are fixed
> over any one query execution.  That includes the case of a subselect
> getting its limit from the outer query, for instance ... but not what
> the OP was trying to do.

I suspect there's still some confusion here. I want to try to spell out just
why the original query was entirely nonsensical.

A query like this is saying you want up to "val" records:

 SELECT * FROM foo LIMIT val

But if val is a column in foo then it's getting a new *separate* value of val
for every record. So how many records should the query return?

Worse, a query like this:

 SELECT * FROM foo OFFSET val

says the output should skip the first "val" records. But that means it somehow
has to predict the future and know that after skipping, say, 50 records it
will magically find a record that says it should in fact have skipped 50
records. Of course then if the next record says it should only have skipped 1
record then what does that mean?

The limit and offset values can be anything, even something "VOLATILE" like
random() or a subquery, but they have to be something which can be evaluated
independently of the output that will then be generated based on them.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

"Reg Me Please" <regmeplease@gmail.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!