Re: alter table drop column status

> 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(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz