Greetings!
Today's request for the impossible:
My database has a view that is not filtered enough for one of my
reports. The report (done in CR XI) has a dynamic parameter based on
that view, but only records with a certain field (complete = 1) should
be shown.
The easiest way to do that is to copy the existing view into a new view
with a more restrictive WHERE clause. Also, the new view is likely to
be useful in various places throughout our system.
The old view and the new view should have the same fields. So, I tried
something like this:
CREATE VIEW new_view AS
SELECT * FROM old_view
WHERE complete = 1;
The query was accepted and the new_view was created.
But when I went back into PGAdmin and looked at the definition of
new_view, I saw something like this:
CREATE VIEW new_view AS
SELECT old_view.field1, old_view.field2, <snip> FROM old_view
WHERE complete = 1;
That's not what I want. That means that if old_view changes, new_view
will not reflect the changes. Is there any way to get new_view to
automatically include all fields from old_view, no matter how many or
how few fields there are?
I'm guessing not, just because of the fact that if I try to add a field
to a view, I get an error telling me I can't change the number of
columns in the view unless I drop it first. I'm not sure of the reason
behind that, but if I can't do it directly, I'm pretty durn sure I
wouldn't be able to do it indirectly by using SELECT * in the view's
definition.
RobR