Re: Hey! ORDER BY in VIEWS?

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Josh Berkus writes:
>> Hey! I thought you couldn't do ORDER BY in views ... yet I just did.
>> Is this a new thing, or am I just getting my Trasact-SQL and my
>> PostgreSQL mixed up again?

> I think it was allowed from 7.1 on to enable LIMIT in views to work
> sensibly.

The point being that ORDER BY + LIMIT is actually a useful computational
extension. ORDER BY per se, in a view, is rather pointless since any
query that selects from the view will feel free to rearrange the data
for its convenience.

Hmm, I just realized that there's a bug here: let's say you have

CREATE VIEW latest AS
SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1;

ie, this view gives you the latest news story. If you do

SELECT * FROM latest WHERE story_text LIKE '%Joe Smith%';

what you will get in 7.1 is the latest story mentioning Joe Smith,
because the planner will push down the WHERE clause into the view's
SELECT, where it'll be applied before the LIMIT. Perhaps some would
call this useful behavior, but I'd say it has to be considered a bug :-(.
The outer WHERE should not cause the VIEW to return a different row
than it otherwise would.

regards, tom lane