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