Very Computer

if i've got a view that joins three or four tables, is there a
way to SELECT on that view to bypass any of the joins if they're
not needed in the result?

        create view course as
                select
                        _course.name as course,
                        _topic.name  as topic,
                        _school.name as school,
                        _state.name  as state,
                        _school.zip  as zip
                where
                        _course.topic = _topic.id
                        and
                        _topic.school = _school.id
                        and
                        _state.abbr   = _school.state
                ;

        select * from course ;
        -- shows all fields via all tables

        select topic from course where course like '%comput%' ;
        -- not asking for state, we don't need to join the state table

here the optimizer might know we don't need to join the static
lookup '_state' table. can this be made to happen?

or is it just best to have "one view, one purpose"?

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
                -- Isaac Asimov, 'The Genetic Code'

http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

But you still do need to join, because the join affects which rows
will be returned.  The fact that you don't happen to use any values
out of one of the joined tables in your SELECT list is not very
relevant.

In this example, you might happen to know (or think you know) that there
will be one and only one state row matching any possible row from the
subjoin of the other tables, so in the end it wouldn't affect the number
of rows output.  This is not an assumption the planner is prepared to
make, however.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html