Обсуждение: SELECT INSTEAD

<div class="Section1"><p class="MsoNormal"><font face="Tahoma" size="2"><span style="font-size:10.0pt;
font-family:Tahoma">Hi all,</span></font><p class="MsoNormal"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;
font-family:Tahoma"> </span></font><p class="MsoNormal"><font face="Tahoma" size="2"><span style="font-size:10.0pt;
font-family:Tahoma">I’m trying to create a view of the query below, but I’m being barked at about “rules on SELECT must
haveaction INSTEAD SELECT”. I don’t have any rules in my database, and I don’t know how this query is trying to create
one.The query does work if I just run it interactively. Any help would be much appreciated.</span></font><p
class="MsoNormal"style="text-indent:36.0pt"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"> </span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font
face="Tahoma"size="2"><span style="font-size:10.0pt;font-family:Tahoma">SELECT DISTINCT ON (m1.id)</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.id) AS id,</span></font><p class="MsoNormal"><font face="Tahoma"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.first_name) AS first_name,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.last_name) AS last_name,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.company) AS company,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.address1) AS address1,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.address2) AS address2,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.suburb) AS suburb,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        UPPER(m1.state) AS state</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">INTO TEMP
TABLEmail_duplicates</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">FROM   maillist as m1</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">INNER JOIN
maillistas m2 ON</span></font><p class="MsoNormal"><font face="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        (           m1.id <> m2.id</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        AND      UPPER(m1.first_name) = UPPER(m2.first_name)</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        AND      UPPER(m1.last_name) = UPPER(m2.last_name)</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        AND      UPPER(m1.suburb) = UPPER(m2.suburb)</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        )</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font
face="Tahoma"size="2"><span style="font-size:10.0pt;font-family:Tahoma">WHERE  TRIM(m1.first_name) <>
''</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma"> AND    TRIM(m1.last_name) <> ''</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">;</span></font><pclass="MsoNormal" style="text-indent:36.0pt"><font
face="Tahoma"size="2"><span style="font-size:10.0pt;font-family:Tahoma">SELECT d.id AS "ID",</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        INITCAP(d.first_name) AS first_name,</span></font><p
class="MsoNormal"><fontface="Tahoma" size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        INITCAP(d.last_name) AS last_name,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        INITCAP(d.company) AS company,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        INITCAP(d.address1) AS address1,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        INITCAP(d.address2) AS address2,</span></font><p class="MsoNormal"><font
face="Tahoma"size="2"><span style="font-size:10.0pt; 
font-family:Tahoma">                        d.suburb AS suburb,</span></font><p class="MsoNormal"><font face="Tahoma"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Tahoma">                        d.state AS state</span></font><p class="MsoNormal"
style="text-indent:36.0pt"><fontface="Tahoma" size="2"><span style="font-size:10.0pt;font-family:Tahoma">FROM   
mail_duplicatesAS d</span></font><p class="MsoNormal" style="text-indent:36.0pt"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">ORDERBY d.last_name, d.first_name, d.suburb, d.id</span></font><p
class="MsoNormal"style="text-indent:36.0pt"><font face="Tahoma" size="2"><span
style="font-size:10.0pt;font-family:Tahoma">;</span></font></div><br/><p><b>*******************Confidentiality and
PrivilegeNotice*******************</b><p> 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
replyemail. <p> Information in this message that does not relate to the official business of Weatherbeeta must be
treatedas neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not
beliable for direct, indirect or consequential loss arising from transmission of this message or any attachments <br /> 

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

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

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. The query does work if I just run it interactively. Any help would be
> much appreciated.

You have two queries here - which one is giving the problem?

> SELECT DISTINCT ON (m1.id)
...
> ;
> 
> SELECT d.id AS "ID",
...
> ;

--   Richard Huxton  Archonet Ltd