Skip site navigation (1) Skip section navigation (2)

Views

From: "Nicolas (dot)(dot)(dot)" <nicolas_p25(at)hotmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Views
Date: 2002-10-25 13:32:13
Message-ID: F23hgiJDDXr2yKNJJz40000a8e0@hotmail.com (view raw or flat)
Thread:
Lists: 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



_________________________________________________________________
Surf the Web without missing calls!Get MSN Broadband.  
http://resourcecenter.msn.com/access/plans/freeactivation.asp


pgsql-novice by date

Next:From: Tom LaneDate: 2002-10-25 14:11:40
Subject: Re: function problems.
Previous:From: Ludwig LimDate: 2002-10-25 10:34:56
Subject: Re: function problems.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group