Parameters for views based on other views
От | Nicolas Prentzas |
---|---|
Тема | Parameters for views based on other views |
Дата | |
Msg-id | 000501c27b7e$5301d540$0200a8c0@nicolas обсуждение исходный текст |
Список | pgsql-novice |
Hi, I have a complex query that I want to make more manageable by splitting it into two separate views and using a third view to bring the two together. So I have something like CREATE VIEW vw1 AS SELECT A.Field1, B.Field2 FROM A, B WHERE A.Field3=B.Field3; CREATE VIEW vw2 AS SELECT B.Field5, C.Field6 FROM B, C WHERE B.Field7=C.Field7; CREATE VIEW vw3 AS SELECT * FROM vw1, vw2 WHERE vw1.Field1=vw2.Field5; (the real case for the above is of course much more complicated) I can now open the third view by SELECT * FROM vw3 WHERE Field1=13 AND Field2=23; However this query runs very slow. I tried defining the views with parameters in the WHERE clauses in each view and I got a MUCH BETTER performance. So my questions are: * Why does this happen? Doesn't PG use the parameters to open each view? * How can I define views with variables as parameters so that I can assign values to the parameters and then open the view? If there is a way for this, does it improve performance? * I am thinking of writing a function that will drop these views and recreate them with parameters in each view and return the result of the select statement above. Is this a good idea? * What is the general way of using parameterized queries in PG? Thanks for any suggestions Nicolas
В списке pgsql-novice по дате отправления: