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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: karsten.hilbert@gmx.net
--
-- ==============================================================
\set ON_ERROR_STOP 1
--set default_transaction_read_only to off;
set check_function_bodies to on;
-- --------------------------------------------------------------
comment on column dem.identity.comment is
'A free-text comment on this identity.\n
\n
Can be used to to discriminate patients which are otherwise\n
identical regarding name and date of birth.\n
Should be something non-ephemereal and unique to the person\n
itself across time, place and database instance.\n
Good: place of birth\n
Good: maiden name of mother\n
Good: mother of <name>\n
Good: hash of DNA\n
Good (?): hair color of first pet\n
Bad: current address (will change)\n
Bad: primary provider in this praxis (can change, invalid in another GNUmed instance)\n
Bad: nickname (will change, can dupe as well)\n
Bad: favourite food\n
not-quite-so-bad: occupation';
-- --------------------------------------------------------------
drop function if exists dem.new_pupic() cascade;
-- --------------------------------------------------------------
drop function if exists dem.trf_sane_identity_comment() cascade;
-- disable audit trigger for update (because table layout has changed)
alter table dem.identity
disable trigger zt_upd_identity;
-- de-duplicate
update dem.identity set
comment = coalesce(comment, '') || '[auto-set by <v21-dem-identity-dynamic.sql>: ' || clock_timestamp() || ']'
where
pk in (
select pk_identity
from dem.v_persons d_vp1
where exists (
select 1 from dem.v_persons d_vp2
where
d_vp2.firstnames = d_vp1.firstnames
and
d_vp2.lastnames = d_vp1.lastnames
and
d_vp2.gender = d_vp1.gender
and
date_trunc('day', d_vp2.dob) = date_trunc('day', d_vp1.dob)
and
d_vp2.pk_identity != d_vp1.pk_identity
)
)
;
-- create function and trigger
create function clin.trf_sane_identity_comment()
returns trigger
language 'plpgsql'
as '
DECLARE
_identity_row record;
_names_row record;
BEGIN
if TG_TABLE_NAME = ''identity'' then
if TG_OP = ''UPDATE'' then
if NEW.comment IS NOT DISTINCT FROM OLD.comment then
return NEW;
end if;
end if;
_identity_row := NEW;
select * into _names_row from dem.names where id_identity = NEW.pk;
else
select * into _identity_row from dem.identity where pk = NEW.id_identity;
_names_row := NEW;
end if;
-- any row with
PERFORM 1 FROM
dem.v_all_persons
WHERE
-- same firstname
firstnames = _names_row.firstnames
and
-- same lastname
lastnames = _names_row.lastnames
and
-- same gender
gender is not distinct from _identity_row.gender
and
-- same dob (day)
dob_only is not distinct from _identity_row.dob
and
-- same discriminator
comment is not distinct from _identity_row.comment
and
-- but not the currently updated or inserted row
pk_identity != _identity_row.pk
;
if FOUND then
RAISE EXCEPTION
''% on %.%: More than one person with (firstnames=%), (lastnames=%), (dob=%), (comment=%)'',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME,
_names_row.firstnames,
_names_row.lastnames,
_identity_row.dob,
_identity_row.comment
USING ERRCODE = ''unique_violation''
;
RETURN NULL;
end if;
return NEW;
END;';
comment on function clin.trf_sane_identity_comment() is
'Ensures unique(identity.dob, names.firstnames, names.lastnames, identity.comment)';
create trigger tr_sane_identity_comment
after insert or update on dem.identity
for each row execute procedure clin.trf_sane_identity_comment();
create trigger tr_sane_identity_comment
after insert or update on dem.names
for each row execute procedure clin.trf_sane_identity_comment();
-- tob nullable
-- dob = (dyob + coalesce(tob, 11:11:11.111)) at client_timezone
-- --------------------------------------------------------------
select gm.log_script_insertion('v21-dem-identity-dynamic.sql', '21.0');
|
