From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Reg Me Please <regmeplease(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Sam Mason <sam(at)samason(dot)me(dot)uk> |
Subject: | Re: Variable LIMIT and OFFSET in SELECTs |
Date: | 2007-11-15 19:28:17 |
Message-ID: | 473C9DD1.3060402@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | adrobj | 2007-11-15 19:46:16 | Clustered/covering indexes (or lack thereof :-) |
Previous Message | Reg Me Please | 2007-11-15 18:48:15 | Re: Variable LIMIT and OFFSET in SELECTs |