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

[David Gauthier <davegauthierpg@xxxxxxxxx>]

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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx