"Scott Muir" <wsmuir@islandnet.com> writes:
> if I happen to make a modification (using pgaccess) to a view which
> has another view which uses it, it corrupts things and I have to drop
> all of the views which make reference to the modified one and recreate
> them... OR I can use pg_dump to allow me to make the modifications to
> a dumpfile, drop the db and recreate using the modified dump.
Cross-references between views and functions are stored in terms of OIDs.
If you drop and re-create a view or function, the new object has a new
OID, so it doesn't satisfy the pre-existing references.
We really need some kind of ALTER FUNCTION/ALTER VIEW command so that
you can modify the contents of a function or view without changing its
OID.
In the meantime, what most people do is to define sets of related
functions and views in scripts:
DROP FUNCTION foo(...);
CREATE FUNCTION foo(...) ...;
DROP VIEW v;
CREATE VIEW v ...;
If you need to make a change, edit the script and then feed it to psql.
Being able to use a real text editor on the script is a big improvement
over typing directly at psql anyway.
regards, tom lane