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