> -----Original Message-----
> From: Jean-Michel POURE [mailto:[EMAIL PROTECTED]]
> Sent: 30 October 2001 10:39
> To: [EMAIL PROTECTED]
> Cc: dave Page; [EMAIL PROTECTED]
> Subject: RE: DROP/CREATE
>
>
>
> >Yes (and I agree that it would be a good feature), but that
> will still
> >require full client side parsing of the code to figure out the
> >dependencies
> >- I for one, do not wish to try to recreate (and keep up-to-date) the
> >PostgreSQL parser in VB. Besides which, if we take it that
> far then we might
> >just as well use reverse engineered SQL to scan for
> dependencies. I know you
> >don't like reverse engineered code, but bear in mind that
> the important bits
> >are reported directly from PostgreSQL (e.g. pg_proc.prosrc).
>
> IMHO view modification can be achieved within one
> transaction, without
> development table nor PL/pgSQL.
Yes, I agree. As I said in my first message, there is no problem with
standalone views, but (and this is the killer) if your view is a dependency
of something else like an SQL function or another view then you have a
problem. The problem is even bigger (i.e. harder to detect) if the rowtype
of the view is used as a parameter to or return value from a function (is
this actually possible? I know it is with a table).
> Could you give me your feedback again for view modification:
> - Attempt to create a view with the new definition to ensure
> it's valid.
Yes.
> - Open transaction (in locking mode as we may drop triggers
> in many tables).
Yes.
> - Drop dependent views in OID order. Keep CREATE SQL strings
> for future usage.
Yes.
> - Drop dependent triggers. Keep CREATE SQL strings for future usage.
Can triggers be dependent on views?
> - Drop dependent rules. Keep CREATE SQL strings for future usage.
Yes.
> - Drop the old view and create the new view.
Yes.
> - Create dependent views, triggers and rules.
Yes.
> - Re-apply any comments and ACLs.
Yes.
> - Commit transaction.
I wouldn't be surprised if some of these actions are not 'transactionable'.
Things like create user/database for example definitely aren't.
> - Query pg_class for the updated OID.
Yes.
>
> This would allow migration from pgAdmin I to pgAdmin II.
Incidently, pgAdmin II (and pgSchema) has no concept of objects being
defined on Views at present. I'll add that to my To-Do list - presumable
it's only Rules (and Triggers?).
Dave.
