From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hey! ORDER BY in VIEWS?
Date: Sun, 15 Jul 2001 17:14:43 -0400
Message-ID: <2121.995231683@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.30.0107152129390.677-100000@peter.localdomain>
References: <Pine.LNX.4.30.0107152129390.677-100000@peter.localdomain>
Peter Eisentraut <peter_e@gmx.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter_e@gmx.net, josh@agliodbs.com
Subject: Re: Hey! ORDER BY in VIEWS?
In-Reply-To: <2121.995231683@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox