X-Antivirus-mail.weatherbeeta.com.au-Mail-From: 
 phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au
X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):.
	Processed in 0.233865 secs Process 9927)
Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155)
	by mail.weatherbeeta.com.au with SMTP; 29 Mar 2007 16:58:40 +1000
From: "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
To: "'Michael Fuhr'" <mike@fuhr.org>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: SELECT INSTEAD
Date: Thu, 29 Mar 2007 16:58:35 +1000
Organization: Weatherbeeta Pty Ptd
Message-ID: <00ca01c771cf$aee1efc0$9b0014ac@wbaus090>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
In-Reply-To: <20070329064833.GA47987@winnie.fuhr.org>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Scanned-By-wbeetaau1: Virus scan performed by network-box
X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1175151523.635-20753-000
X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in
	0.02/0.07 secs)
X-Scanned-By-wbeetaau1: Spam-Check-Result: No,
	hits=0 required=7 tests= autolearn=no version=2.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/344
X-Sequence-Number: 27982

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