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 11:38 AM, David Gauthier wrote:
> 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.
>

See this post:

https://www.postgresql.org/message-id/31367.1572815723%40sss.pgh.pa.us

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