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

On 3/26/20 10:55 AM, David Gauthier wrote:
> Thanks Adrian for the quick reply.
> I don't have a lot of choice regarding PG version.  I work for a large
> corp with an IT dept which offers the version I have.  They create VMs
> which are DB servers and this is the best they offer.  But I could
> request something newer.  Never hurts to try.
>
> Ya, I kinda figured that there's nothing wrong with referencing tables
> from the default (public) schema.  So I tried to redefine the view by
> referencing the public tables literally, as in "public.thetable".  The
> plan was to do some sort of global replace of "public." with "myschem."
> in the output of pg_dump, maybe with sed or something.  But even after
> explicitly using "public.", it didn't stick in the view def.

Can we see an example view definition?

>
> On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com