1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: Karsten Hilbert
--
-- ==============================================================
drop view if exists blobs.v_obj4doc_no_data cascade;
create view blobs.v_obj4doc_no_data as
select
b_vdm.pk_patient
as pk_patient,
b_do.pk
as pk_obj,
b_do.seq_idx
as seq_idx,
octet_length(coalesce(b_do.data, ''))
as size,
b_vdm.clin_when
as date_generated,
b_vdm.type
as type,
b_vdm.l10n_type
as l10n_type,
b_vdm.ext_ref
as ext_ref,
b_vdm.episode
as episode,
b_vdm.comment
as doc_comment,
b_do.comment
as obj_comment,
b_do.filename
as filename,
b_do.fk_intended_reviewer
as pk_intended_reviewer,
exists(select 1 from blobs.reviewed_doc_objs where fk_reviewed_row = b_do.pk)
as reviewed,
exists (
select 1 from blobs.reviewed_doc_objs
where
fk_reviewed_row = b_do.pk and
fk_reviewer = (select pk from dem.staff where db_user = current_user)
) as reviewed_by_you,
exists (
select 1 from blobs.reviewed_doc_objs
where
fk_reviewed_row = b_do.pk and
fk_reviewer = b_do.fk_intended_reviewer
) as reviewed_by_intended_reviewer,
b_vdm.pk_doc
as pk_doc,
b_vdm.pk_type
as pk_type,
b_vdm.pk_encounter
as pk_encounter,
b_vdm.pk_episode
as pk_episode,
b_vdm.pk_health_issue
as pk_health_issue,
b_do.xmin
as xmin_doc_obj
from
blobs.v_doc_med b_vdm
inner join blobs.doc_obj b_do on (b_do.fk_doc = b_vdm.pk_doc)
where
b_vdm.pk_doc = b_do.fk_doc
;
comment on view blobs.v_obj4doc_no_data is
'denormalized metadata for blobs.doc_obj but without the data itself';
GRANT SELECT ON blobs.v_obj4doc_no_data TO GROUP "gm-doctors";
-- --------------------------------------------------------------
select gm.log_script_insertion('v21-blobs-v_obj4doc_no_data.sql', '21.0');
|
