Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6FLFBa27279 for ; Sun, 15 Jul 2001 17:15:11 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6FLEhX02124; Sun, 15 Jul 2001 17:14:43 -0400 (EDT) To: Peter Eisentraut cc: Josh Berkus , pgsql-sql@postgresql.org Subject: Re: Hey! ORDER BY in VIEWS? In-reply-to: References: Comments: In-reply-to Peter Eisentraut message dated "Sun, 15 Jul 2001 21:30:29 +0200" Date: Sun, 15 Jul 2001 17:14:43 -0400 Message-ID: <2121.995231683@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/189 X-Sequence-Number: 3984 Peter Eisentraut 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