Eric Worden wrote:
Can anyone recommend a reasonably efficient system for changing a view definition (say by adding a column) when it has a bunch of dependent functions? Right now I work with the output from pg_dump to recreate things after doing "DROP VIEW ... CASCADE". But the pg_dump schema output is only approximately sorted by dependencies, and "create table..." is sprinkled all through it. That means I have to carefully comb through and select the pieces I need. Is there a way to just script the view definitions, then the functions? Or maybe I'm looking at it the wrong way?
I've run into the situation similar to yours but with views and foreign key dependent on tables. Same process probably would work for you.
I use pgAdmin III to run my desired change script, which might include a DELETE...CASCADE or ALTER ... command(s), wrapped inside a BEGIN ... ROLLBACK block (since this may require a number of iterations). The messages in the pgAdmin output pane identify dependencies specifically related to the changes you intend to make.
Then for each dependency, I build out my script by inserting it in between the DROP ... CREATE commands identified for each dependency in each iteration of the process until all the dependencies are resolved.
This is not necessarily what I would think of as ideal, since it is not really very well automated to the extent I'd like to see, but it has worked, and it has proven "efficient enough" for a process that is not a routine, everyday task.
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
