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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
