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

On 3/26/20 10:16 AM, David Gauthier wrote:
> Here's an interesting one for you...
> 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.


Well first, the current minor version of 9.6 is .17 so you are 10 
releases behind. In fact the 9.6.8 release includes changes that impact 
the below:
https://www.postgresql.org/docs/9.6/release-9-6-8.html

> 
> 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 ?

By manually changing the definition? It is not an error for a VIEW in 
one schema to refer to tables in other schemas. AFAIK the code has no 
way of knowing you want to move the underlying tables just by specifying 
a search_path.

> 
> Thanks in Advance.


-- 
Adrian Klaver
adrian.klaver@aklaver.com