Date: Thu, 29 Mar 2007 00:48:34 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT INSTEAD
Message-ID: <20070329064833.GA47987@winnie.fuhr.org>
References: <00ab01c771c4$1ec9aa50$9b0014ac@wbaus090>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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