"Tom Lane" <tgl@xxxxxxxxxxxxx> writes: > Sam Mason <sam@xxxxxxxxxxxxx> 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! ---------------------------(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
