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 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.klaver@aklaver.com