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 по дате отправления:

Предыдущее
От: "Peter Galbavy"
Дата:
Сообщение: Re: [ADMIN] pg or DBI
Следующее
От: Pam Wampler
Дата:
Сообщение: Re: show all tables?