> select drop_column(mytable, mycolumn);
IMHO first at least a LOCK should be executed on all tables which are in
any reference with "mytable". If LOCK is not enough, the entire database
should be locked (in pg_hba.conf) for all users except for the maintainer.
> > 1) Mark objects for deletion
> > * mark columns in "table_from" for deletion,
> > * mark primary keys in "table_from" for deletion,
> > * mark foreign keys in "table_from" for deletion,
* check all other tables if they have any references to the columns of "table_from" marked to be deleted; if check
fails,STOP
* lock all tables which appear in FOREIGN KEYS of "table_from" and all tables which have FOREIGN KEYS references to
"table_from"
> > 2) Copy schema and data
> > * copy "table_to" structure out of "table_from" keeing only
> > marked objects,
> > * copy data from "table_from" to "table_to",
> >
> > 3) Add rules and triggers, rename
> > * add "table_from" triggers to "table_to",
> > * add "table_from" rules to "table_to",
> > * drop table "table_from",
* (postgres will automatically drop referential integrity triggers from all tables referencing the the dropped table
"table_from")
> > * rename "table_to".
* recreate referential integrity triggers in all tables described above
* unlock all locked tables
I'm afraid LOCK is not available inside a PLPGSQL function (I write almost
everything in PLPGSQL). However, a shell script should do this easily, but
it's no so smart to call a shell script from a PLPGSQL function (although
I do this some time), if Cristopher would like to use it with a single
SELECT.
Regards, Zoltan
Kov\'acs, Zolt\'an kovacsz@pc10.radnoti-szeged.sulinet.hu
http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
