 > 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

Tue, 30 Oct 2001 08:56:34 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: DROP/CREATE