From: Michael Fuhr <mike@fuhr.org>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT INSTEAD
Date: Thu, 29 Mar 2007 00:48:34 -0600
Message-ID: <20070329064833.GA47987@winnie.fuhr.org> (raw)
In-Reply-To: <00ab01c771c4$1ec9aa50$9b0014ac@wbaus090>
References: <00ab01c771c4$1ec9aa50$9b0014ac@wbaus090>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, phillip.smith@weatherbeeta.com.au
Subject: Re: SELECT INSTEAD
In-Reply-To: <20070329064833.GA47987@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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