Search Postgresql Archives

Re: Variable LIMIT and OFFSET in SELECTs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux