I've have an application which uses the public schema for all of its relations.
I want to change the name of the schema (in preparation to merging the database into that of another application, which also uses the public schema for its tables).
I could qualify all of the tables and sequence with the schema names in the source code SQL, but that is a lot of tedious work and I don't think it even counts as an improvement. I think I'd rather use search_path in the application being migrated, and only use fully qualified names for the (small) part of the code that will explicitly need to bridge both systems.
Once I rename the schema ("alter schema public rename to new_schema;") I can set the search path either on the db side, or on the app side. That is, by "alter role web_user set search_path TO new_schema", or by changing the centralized subroutine used by the application to get a database handle, something like this:
sub getdbh {
require DBI;
my $dbh = DBI->connect(..., {AutoCommit=>1, RaiseError=>1, PrintError=>0});
$dbh->do("set search_path to new_schema");
return $dbh;
};
Is there a reason to choose one of these options over the other? Or is it purely a matter of taste?
I'm leaning towards the latter method, because it seems the future application maintainer is more likely to benefit from the clue about the search_path than the future DBA (assuming those roles get split).
Thanks,
Jeff
