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

sqf-> SELECT sr.project,
sqf-> sr.sqf_id,
sqf-> wa.wa_path,
sqf-> sr.cbwa_type,
sqf-> sr.status,
sqf-> sr.nightly_rg_cl,
sqf-> ( SELECT max(fse.end_datetime) AS max
sqf(> FROM public.flow_step_events fse
sqf(> WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
sqf-> ( SELECT DISTINCT f.perl_sub_name
sqf(> FROM public.flows f,
sqf(> public.flow_step_events fse
sqf(> WHERE f.flow_type = fse.flow_type AND fse.sqf_id =
sr.sqf_id AND f.step_number = (( SELECT max(fse2.step_number) AS max
sqf(> FROM public.flow_step_events fse2
sqf(> WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
sqf-> FROM public.sqf_runs sr,
sqf-> public.workareas wa
sqf-> WHERE wa.current_user_sqf_id = sr.sqf_id
sqf-> ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime) AS
max
sqf(> FROM public.flow_step_events fse
sqf(> WHERE fse.sqf_id = sr.sqf_id));
CREATE VIEW

sqf=> \d+ current_workarea_users;
View "public.current_workarea_users"
Column | Type | Modifiers | Storage |
Description
-------------------+--------------------------+-----------+----------+-------------
project | text | | extended |
sqf_id | text | | extended |
wa_path | text | | extended |
cbwa_type | text | | extended |
status | text | | extended |
nightly_rg_cl | integer | | plain |
last_sqf_step_end | timestamp with time zone | | plain |
last_step_run | text | | extended |
View definition:
SELECT sr.project,
sr.sqf_id,
wa.wa_path,
sr.cbwa_type,
sr.status,
sr.nightly_rg_cl,
( SELECT max(fse.end_datetime) AS max
FROM flow_step_events fse
WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
( SELECT DISTINCT f.perl_sub_name
FROM flows f,
flow_step_events fse
WHERE f.flow_type = fse.flow_type AND fse.sqf_id = sr.sqf_id AND
f.step_number = (( SELECT max(fse2.step_number) AS max
FROM flow_step_events fse2
WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
FROM sqf_runs sr,
workareas wa
WHERE wa.current_user_sqf_id = sr.sqf_id
ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime) AS max
FROM flow_step_events fse
WHERE fse.sqf_id = sr.sqf_id));

sqf=>

You can see the "public." refs in the create view, but not echoed in the
stored view def.

On Thu, Mar 26, 2020 at 1:58 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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