Обсуждение: Creating a view: ERROR: rules on SELECT must have action INSTEAD SELECT
I get the following error when trying to create a view with the following trivial example. SQL error: ERROR: rules on SELECT must have action INSTEAD SELECT In statement: CREATE VIEW "nutrition"."test_view" AS SELECT 2 as two, 3 as three INTO TEMP temp_table; SELECT *,two+three as five FROM temp_table; If I rewrite the statement to maybe make more sense: SQL error: ERROR: temporary tables cannot specify a schema name In statement: SELECT 2 as two, 3 as three INTO TEMP nutrition.temp_table; CREATE VIEW "nutrition.test_view" AS SELECT *,two+three as five FROM nutrition.temp_table; So the question is how do I use the powerful feature of temporary tables with views? And if this is not possible, how do I do something similar for optimization reasons (ie pretend "two+three as five" is some very expensive operation and "two" and "three" were also expensive operations)?
Re: Creating a view: ERROR: rules on SELECT must have action INSTEAD SELECT
От
Filip Rembiałkowski
Дата:
David, You cannot mix CREATE TEMP TABLE and SELECT in one CREATE VIEW statement I really recommend looking at WITH statement http://www.postgresql.org/docs/8.4/static/queries-with.html Adjusting to your example: CREATE VIEW test_view AS WITH q AS ( SELECT 2 as two, 3 as three ) SELECT *, two+three as five FROM q; 2010/3/19 David Waddy <david@littleriver.ca>: > I get the following error when trying to create a view with the > following trivial example. > > SQL error: > ERROR: rules on SELECT must have action INSTEAD SELECT > > In statement: > CREATE VIEW "nutrition"."test_view" AS SELECT 2 as two, 3 as three > INTO TEMP temp_table; SELECT *,two+three as five FROM temp_table; > > If I rewrite the statement to maybe make more sense: > > SQL error: > > ERROR: temporary tables cannot specify a schema name > > In statement: > > SELECT 2 as two, 3 as three INTO TEMP nutrition.temp_table; CREATE > VIEW "nutrition.test_view" AS SELECT *,two+three as five FROM > nutrition.temp_table; > > So the question is how do I use the powerful feature of temporary > tables with views? And if this is not possible, how do I do something > similar for optimization reasons (ie pretend "two+three as five" is > some very expensive operation and "two" and "three" were also > expensive operations)? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Filip Rembiałkowski JID,mailto:filip.rembialkowski@gmail.com http://filip.rembialkowski.net/