sql - 创建自连接查询时创建 postgresql 意外或隐式 View

Question

我每天查询我们的 EMR(电子病历)数据库。 EMR 的供应商说查询正在数据库上创建“ View ”。这些 View 干扰了我们安装补丁时运行的表更新例程。我在查询中没有查看命令。

我是否使用以下查询创建了某种隐式 View

SELECT 
"Scheduled"."foo1",
"Scheduled"."foo2",
"Scheduled"."foo3",
FROM
(SELECT
"public".profile.prof_c_foop1 AS "foo1",
"public".profile.prof_c_foop2 AS "foo2",
"public".profile.prof_c_foop3 AS "foo3",

FROM
"public".scheduling_event
JOIN "public".scheduling_appointment ON "public".scheduling_appointment.sch5appt_eventid = "public".scheduling_event.sch5event_id
JOIN "public".profile ON "public".scheduling_event.sch5event_profile = "public".profile.prof_c_profilenum
JOIN "public".scheduling_appointment_resource ON "public".scheduling_appointment_resource.sch5apptres_apptid = "public".scheduling_appointment.sch5appt_id
JOIN "public".scheduling_resource ON "public".scheduling_appointment_resource.sch5apptres_resid = "public".scheduling_resource.sch5res_id
JOIN "public".scheduling_location ON "public".scheduling_location.sch5loc_id = "public".scheduling_resource.sch5res_id
JOIN "public".scheduling_event_task ON "public".scheduling_event_task.sch5evtks_event_id = "public".scheduling_event.sch5event_id
JOIN "public".scheduling_task ON "public".scheduling_event_task.sch5evtks_task_id = "public".scheduling_task.sch5task_id
JOIN "public".scheduling_appointment_status ON "public".scheduling_appointment_status.sch5apptstat_apptid = "public".scheduling_appointment.sch5appt_id
WHERE
"public".profile.prof_c_ip1p_phone > 0
AND
public.scheduling_appointment.sch5appt_datetime > CURRENT_DATE + interval '2 day' 
AND
public.scheduling_appointment.sch5appt_datetime < CURRENT_DATE + interval '3 days'
AND
"public".scheduling_appointment_status.sch5apptstat_code = 'S'
AND
"public".scheduling_task.sch5task_desc NOT SIMILAR TO '(SURGERY|EUFLEXXA)' ) 

   as "Scheduled"  --<<<<<<<< i think this might be where views are created maybe???<<<<<

LEFT JOIN
(SELECT
"public".scheduling_appointment_status.sch5apptstat_apptid as "ApptID"
FROM
"public".scheduling_event
JOIN "public".scheduling_appointment ON "public".scheduling_appointment.sch5appt_eventid = "public".scheduling_event.sch5event_id
JOIN "public".profile ON "public".scheduling_event.sch5event_profile = "public".profile.prof_c_profilenum
JOIN "public".scheduling_appointment_resource ON "public".scheduling_appointment_resource.sch5apptres_apptid = "public".scheduling_appointment.sch5appt_id
JOIN "public".scheduling_resource ON "public".scheduling_appointment_resource.sch5apptres_resid = "public".scheduling_resource.sch5res_id
JOIN "public".scheduling_location ON "public".scheduling_location.sch5loc_id = "public".scheduling_resource.sch5res_id
JOIN "public".scheduling_event_task ON "public".scheduling_event_task.sch5evtks_event_id = "public".scheduling_event.sch5event_id
JOIN "public".scheduling_task ON "public".scheduling_event_task.sch5evtks_task_id = "public".scheduling_task.sch5task_id
JOIN "public".scheduling_appointment_status ON "public".scheduling_appointment_status.sch5apptstat_apptid = "public".scheduling_appointment.sch5appt_id
WHERE
"public".profile.prof_c_ip1p_phone > 0 --ERIC SUCKS DAN RULES
AND
public.scheduling_appointment.sch5appt_datetime > CURRENT_DATE + interval '2 day' 
AND
public.scheduling_appointment.sch5appt_datetime < CURRENT_DATE + interval '3 days'
AND
"public".scheduling_appointment_status.sch5apptstat_code = 'A' ) as "Cancelled"
ON
"Scheduled"."ApptID" = "Cancelled"."ApptID"
WHERE
"Cancelled"."ApptID" is NULL

Answer 1

你不能从查询中分辨出来。例如:

创建空表等以暗示创建隐式 View :

t=# create table tt(i int);
CREATE TABLE

t=# create function trick() returns table (i int) as
$$begin
create view vv as select 1;
return query select * from vv;
end;
$$
language plpgsql
;
CREATE FUNCTION
t=# CREATE RULE "_RETURN" AS
t-#     ON SELECT TO tt
t-#     DO INSTEAD
t-#         SELECT * FROM trick();
CREATE RULE

现在 View 不存在:

t=# select * from vv;
ERROR:  relation "vv" does not exist
LINE 1: select * from vv;
                      ^

您在现有的空表上运行您的选择:

t=# select * from tt;
 i
---
 1
(1 row)

并且创建了 View :

t=# select * from vv;
 ?column?
----------
        1
(1 row)

如果您想知道创建了什么 View (如果有)或挖掘结构，请与您的供应商联系 - 查询不足以告诉您