Re: How can I recreate a view in a new schema such that the view def references tables in the new schema ?

On Thu, 2020-03-26 at 13:16 -0400, David Gauthier wrote:
> psql (9.6.7, server 11.3) on linux
>
> I have 2 DBs, differnet servers/instances. I want to take all the metadata and data for a set of tables/views
> in the public schema of one DB and move it all over to be inside a schema of a second DB/instance.
>
> I'm using pg_dump to create the script and I believe I can insert a "set search_path=myschem" in the output
> of pg_dump such that when it runs, the "CREATE TABLE", "CREATE VIEW", "GRANT...", etc.... commands,
> will all go into the new schema (which I have prepared). Problem is the view defs.
> The view defs do not prefix the referenced tables with "myschem.", so the CREATE VIEW xyx commands fail.
>
> Is there a way to do this ?

The best way would be to use pg_dump to move the schema definitions over
while preserving the "public" schema, and then use

ALTER ... SET SCHEMA newschema;

on all objects.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com