I have a database which over many years and clients has grown to about
140 tables with attendant triggers , rules and views. Having transferred
to the postgresql community from another database provider the design
did not include the use of schema.
During a recent review it struck me just how useful the schema are as a
management tool and I would like to now separate the various
sub-sections in this database under different schema.
My problem. The time and the risk of error. There does not appear to be
a simple command like:
alter table public.xxx rename to yyy.xxx;
or
transfer [table][view][rule] public.abc to schema yyy;
As this is probably a common issue many have faced as they upgrade their
database designs can someone please advise on the recommended method?
Bear in mind that their are a number of client systems out there that
need the same treatment.
I can see perhaps doing a dump to plain text and then editing the schema
references..... but the opportunity for error would be pretty big and
the editing task would have to be repeated many times.
if a new command were available to transfer between schemas then the
wishlist would eventually extend to ...
transfer [table] [view] [rule] public.* to schema yyy;
Where * would mean all entries of the selected type
or since I have used prefixes to table names to indicate the modules in
which they are used the ideal in my case would be..
transfer [table][view][rule] public.xxx_* to schema yyy;
which would transfer all objects of the selected type whoose names match
"xxx_*" to schema yyy
I am not sure how these suggestions sit with the intended use of schemas
and will appreciate any help possible in effecting the transfer.
Much thanks
Richard
