Re: SELECT INSTEAD - Mailing list pgsql-sql
From | Phillip Smith |
---|---|
Subject | Re: SELECT INSTEAD |
Date | |
Msg-id | 00ca01c771cf$aee1efc0$9b0014ac@wbaus090 Whole thread Raw |
In response to | Re: SELECT INSTEAD (Michael Fuhr <mike@fuhr.org>) |
List | pgsql-sql |
Thanks Michael - The 2 queries (sorry, should have said transaction) are a simple way to identify duplicates in our mailing list. If there are triplicates, the first query will return 6 rows into the temp table, 1 for each of the 2 duplicates), but I only need to show the 3 triplicates once each, not twice each. The DISTINCT ON does that, and that's basically the whole reason I need to put it in to a temp table and re-select from that, otherwise I have to sort by the id column (ORDER BY must match DISTINCT ON expression). Sorting by the ID column doesn't "group" the duplicate / triplicate rows together for review - that's why I need the ORDER BY. I guess I could remove the ORDER BY, which eliminates my need to use the temp table and 2 queries, then do the ordering when I select the view...? Cheers, ~p -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Thursday, 29 March 2007 16:49 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SELECT INSTEAD On Thu, Mar 29, 2007 at 03:35:52PM +1000, Phillip Smith wrote: > I'm trying to create a view of the query below, but I'm being barked at > about "rules on SELECT must have action INSTEAD SELECT". I don't have any > rules in my database, and I don't know how this query is trying to create > one. http://www.postgresql.org/docs/8.2/interactive/rules-views.html "Views in PostgreSQL are implemented using the rule system." > The query does work if I just run it interactively. Any help would be > much appreciated. You showed two queries, not one. The error occurs because you're trying to create a view that creates a temporary table; you'll need to write the two queries as a single query that doesn't use a temporary table. Also, are you sure you need DISTINCT ON (m1.id)? What do you intend for that to do? Is id unique (PRIMARY KEY or UNIQUE)? And unless ORDER BY is necessary to determine the result set (as with DISTINCT ON) then consider leaving it out of the view definition -- if the outermost query (the query that selects from the view) needs a certain order than that's the proper place for ORDER BY. -- Michael Fuhr *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments