Re: pg_dump and schema names

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> pg_dump goes to great lengths not to hard-code the schema name into
> commands like CREATE TABLE, instead setting the search_path before
> creating the table; these commands:

> CREATE SCHEMA xx;
> CREATE TABLE xx.test(x int);

> generates this output:

> SET search_path = xx, pg_catalog;
> CREATE TABLE test (
> x integer
> );

> If you dump a schema and want to reload it into another schema, you
> should only need to update that one search_path line. However, later in
> the dump file, we hardcode the schema name for setting the object owner:

> ALTER TABLE xx.test OWNER TO postgres;

> Could we use search_path here to avoid the schema designation?

Perhaps, but that's not likely to reduce the number of places you have to
edit, unless your dump is only one schema anyway.

The practical difficulties involved can be seen by reading the comments
and code for_getObjectDescription().

regards, tom lane