pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT INSTEAD
4+ messages / 3 participants
[nested] [flat]
* SELECT INSTEAD
@ 2007-03-29 05:35 Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-29 06:48 ` Re: SELECT INSTEAD Michael Fuhr <mike@fuhr.org>
2007-03-29 06:49 ` Re: SELECT INSTEAD Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Phillip Smith @ 2007-03-29 05:35 UTC (permalink / raw)
To: pgsql-sql
Hi all,
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.
SELECT DISTINCT ON (m1.id)
UPPER(m1.id) AS id,
UPPER(m1.first_name) AS first_name,
UPPER(m1.last_name) AS last_name,
UPPER(m1.company) AS company,
UPPER(m1.address1) AS address1,
UPPER(m1.address2) AS address2,
UPPER(m1.suburb) AS suburb,
UPPER(m1.state) AS state
INTO TEMP TABLE mail_duplicates
FROM maillist as m1
INNER JOIN maillist as m2 ON
( m1.id <> m2.id
AND UPPER(m1.first_name) = UPPER(m2.first_name)
AND UPPER(m1.last_name) = UPPER(m2.last_name)
AND UPPER(m1.suburb) = UPPER(m2.suburb)
)
WHERE TRIM(m1.first_name) <> ''
AND TRIM(m1.last_name) <> ''
;
SELECT d.id AS "ID",
INITCAP(d.first_name) AS first_name,
INITCAP(d.last_name) AS last_name,
INITCAP(d.company) AS company,
INITCAP(d.address1) AS address1,
INITCAP(d.address2) AS address2,
d.suburb AS suburb,
d.state AS state
FROM mail_duplicates AS d
ORDER BY d.last_name, d.first_name, d.suburb, d.id
;
*******************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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT INSTEAD
2007-03-29 05:35 SELECT INSTEAD Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-03-29 06:48 ` Michael Fuhr <mike@fuhr.org>
2007-03-29 06:58 ` Re: SELECT INSTEAD Phillip Smith <phillip.smith@weatherbeeta.com.au>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Fuhr @ 2007-03-29 06:48 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT INSTEAD
2007-03-29 05:35 SELECT INSTEAD Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-29 06:48 ` Re: SELECT INSTEAD Michael Fuhr <mike@fuhr.org>
@ 2007-03-29 06:58 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Phillip Smith @ 2007-03-29 06:58 UTC (permalink / raw)
To: 'Michael Fuhr' <mike@fuhr.org>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SELECT INSTEAD
2007-03-29 05:35 SELECT INSTEAD Phillip Smith <phillip.smith@weatherbeeta.com.au>
@ 2007-03-29 06:49 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2007-03-29 06:49 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-03-29 06:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-29 05:35 SELECT INSTEAD Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-29 06:48 ` Michael Fuhr <mike@fuhr.org>
2007-03-29 06:58 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-03-29 06:49 ` Richard Huxton <dev@archonet.com>
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