> For a 'standalone' view, this is fine, but if the view is used in
another view or a function then that will break (I think I'm teaching my
Grandmother to suck eggs here Jean-Michel!).
> 1) Attempt to create a view with the new definition to ensure it's valid.
> 2) Drop the old view.
> 3) Create the new view.
> 4) Re-apply any comments and ACLs.
> 5) Query pg_class for the updated OID.
Dear Friends,
I did not get this email on pgadmin-hackers. We need view dependency
checking, otherwise there is no chance that I can one day migrate from
pgAdmin I to pgAdmin II. Hopefully, updating a view is not too difficult:
- Attempt to create a view with the new definition to ensure it's valid.
- Open transaction (in locking mode as we may drop triggers in many tables).
- Drop dependent views in OID order. Keep CREATE SQL strings for future usage.
- Drop dependent triggers. Keep CREATE SQL strings for future usage.
- Drop dependent rules. Keep CREATE SQL strings for future usage.
- Drop the old view and create the new view.
- Create dependent views, triggers and rules.
- Re-apply any comments and ACLs.
- Commit transaction.
- Query pg_class for the updated OID.
Any feedback?
Another issue is that views get very complex when commited. An example
would be:
CREATE VIEW "view_data_source"
AS SELECT * FROM table 1
LEFT JOIN table 2 ON (xx=ccc)
LEFT JOIN table 3 ON (xx=ccc)
When committed, this view becomes a nightmare because it can hardly be
read. Another subsequent problem is that views with SELECT * FROM table1
need updating when fields are added/dropped in tables. In the end we always
come up with the conclusion that changes should be applied internally to
PostgreSQL.
I am going to have a look at updating views within a single transaction.
Are there special guidelines for compiling phSchema?
Best regards,
Jean-Michel
