Dumped on 2024-12-23
This logs access to the database and to records. Needed for HIPAA compliance among other things.
| F-Key | Name | Type | Description |
|---|---|---|---|
| pk_audit | integer | NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass) | |
| row_version | integer | NOT NULL | |
| modified_when | timestamp with time zone | NOT NULL DEFAULT now() | |
| modified_by | name | NOT NULL DEFAULT "current_user"() | |
| pk | serial | PRIMARY KEY | |
| user_action | text | NOT NULL |
| Name | Constraint |
|---|---|
| audit_audit_fields_sane_modified_when | CHECK (((modified_when <= clock_timestamp()) IS TRUE)) |
| non_empty_user_action | CHECK ((gm.is_null_or_blank_string(user_action) IS FALSE)) |
All tables that want to send standard notifications must be recorded in this table. Notification triggers will be generated automatically for all tables recorded here.
| F-Key | Name | Type | Description |
|---|---|---|---|
| pk | serial | PRIMARY KEY | |
| schema_name | name | NOT NULL DEFAULT 'public'::name | |
| table_name | name | NOT NULL |
this table holds the revisions of all SQL scripts ever inserted into this database, the values are preferably provided by CVS tags in the scripts themselves, see above for a convenient way to do that
| F-Key | Name | Type | Description |
|---|---|---|---|
| pk | serial | PRIMARY KEY | |
| filename | text |
UNIQUE#1
NOT NULL
the name of the script, handled most easily by CVS via "RCSfile" |
|
| version | text |
UNIQUE#1
NOT NULL
the version of the script, handled most easily by CVS via "Revision" |
|
| imported | timestamp with time zone |
NOT NULL
DEFAULT now()
when this script was imported, mainly for debugging |
DECLARE _is_owner boolean; BEGIN -- is _account member of current db group ? -- PERFORM 1 FROM pg_auth_members -- WHERE -- roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database()) -- AND -- member = (SELECT oid FROM pg_roles WHERE rolname = _account) -- ; -- IF FOUND THEN -- -- should catch people on staff, gm-dbo, and postgres -- RETURN TRUE; -- END IF; -- postgres IF _account = 'postgres' THEN RETURN TRUE; END IF; -- on staff list PERFORM 1 FROM dem.staff WHERE db_user = _account; IF FOUND THEN RETURN TRUE; END IF; -- owner SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = current_database(); IF _is_owner IS TRUE THEN RETURN TRUE; END IF; RETURN FALSE; END;
Add array aggregate and array unnesting to PostgreSQL versions lacking this functionality (IOW < 8.4).
DECLARE
BEGIN
-- array_agg
perform 1 from pg_catalog.pg_aggregate where aggfnoid::oid = (select oid from pg_catalog.pg_proc where proname = 'array_agg'::name limit 1);
if FOUND then
raise NOTICE '[gm.add_missing_array_bits]: aggregate <array_agg> already exists';
else
raise NOTICE '[gm.add_missing_array_bits]: aggregate <array_agg> does not exist (probably PostgreSQL <8.4), creating';
CREATE AGGREGATE array_agg(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
comment on aggregate array_agg(anyelement) is
'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
end if;
-- unnest()
perform 1 from pg_catalog.pg_proc where
proname = 'array_unnest'::name
and
pronamespace = (select oid from pg_namespace where nspname = 'gm'::name)
;
if FOUND then
raise NOTICE '[gm.add_missing_array_bits]: function "gm.array_unnest()" already exists';
else
raise NOTICE '[gm.add_missing_array_bits]: function "gm.array_unnest()" does not exist, creating';
CREATE OR REPLACE FUNCTION gm.array_unnest(anyarray)
RETURNS SETOF anyelement
AS '
SELECT $1[i] FROM
generate_series (
array_lower($1,1),
array_upper($1,1)
) i
;'
LANGUAGE 'sql'
IMMUTABLE
;
comment on function gm.array_unnest(anyarray) is
'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
end if;
perform 1 from pg_catalog.pg_proc where
proname = 'unnest'::name
and
pronamespace = (select oid from pg_namespace where nspname = 'pg_catalog'::name)
;
if FOUND then
raise NOTICE '[gm.add_missing_array_bits]: function "pg_catalog.unnest()" exists';
-- also exists in public ?
perform 1 from pg_catalog.pg_proc where
proname = 'unnest'::name
and
pronamespace = (select oid from pg_namespace where nspname = 'public'::name)
;
if FOUND then
raise NOTICE '[gm.add_missing_array_bits]: function "unnest()" also exists in schema "public", removing';
drop function public.unnest(anyarray) cascade;
end if;
else
raise NOTICE '[gm.add_missing_array_bits]: function "pg_catalog.unnest()" does not exist (probably PostgreSQL <8.4)';
-- exists in public ?
perform 1 from pg_catalog.pg_proc where
proname = 'unnest'::name
and
pronamespace = (select oid from pg_namespace where nspname = 'public'::name)
;
if FOUND then
raise NOTICE '[gm.add_missing_array_bits]: function "public.unnest()" already exists';
else
raise NOTICE '[gm.add_missing_array_bits]: function "public.unnest()" does not exist either, creating';
CREATE OR REPLACE FUNCTION public.unnest(anyarray)
RETURNS SETOF anyelement
AS 'SELECT gm.array_unnest($1);'
LANGUAGE 'sql'
IMMUTABLE
;
comment on function public.unnest(anyarray) is
'Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.';
end if;
end if;
return TRUE;
END;
Mark given table for notification trigger generator. Parameters are: (schema, table). Defaults signal to table name.
select gm.register_notifying_table($1, $2);
Only gm-dbo is GRANTed EXECUTE on this function. This way users need to know the gm-dbo (GNUmed admin) password to execute it.
DECLARE
_username alias for $1;
_target_group alias for $2;
_valid_groups name[];
_group name;
_query text;
BEGIN
-- verify user
perform 1 from pg_user where usename = _username;
if not FOUND then
raise warning '[gm.add_user_to_permission_group]: user [%] does not exist', _username;
return False;
end if;
-- verify logical group validity
-- no "gm-nurse", "gm-admin" just yet
_valid_groups := ARRAY[quote_ident('gm-public'), quote_ident('gm-staff'), quote_ident('gm-doctors')];
if quote_ident(_target_group) <> all(_valid_groups) then
raise warning '[gm.add_user_to_permission_group]: invalid group [%]', _target_group;
return False;
end if;
-- verify group existance
perform 1 from pg_group where groname = _target_group;
if not FOUND then
raise warning '[gm.add_user_to_permission_group]: group [%] does not exist', _target_group;
return False;
end if;
-- drop user from all groups
--FOREACH _group IN ARRAY _valid_groups LOOP
FOR _group IN SELECT unnest(_valid_groups) LOOP
_query := 'alter group ' || _group || ' drop user ' || quote_ident(_username) || ';';
execute _query;
END LOOP;
-- add user to desired group
_query := 'alter group ' || quote_ident(_target_group) || ' add user ' || quote_ident(_username) || ';';
execute _query;
return True;
END;
Missing on PG 8.3, needed for vaccination handling starting with conversion from gnumed_v13 to gnumed_v14.
SELECT $1[i] FROM generate_series ( array_lower($1,1), array_upper($1,1) ) i ;
declare _struct text; begin select into _struct gm.concat_table_structure_v19_and_up(); return _struct; end;
declare _db_ver alias for $1; _struct text; begin if _db_ver < 6 then select into _struct gm.concat_table_structure_v1(); return _struct; end if; if _db_ver < 8 then select into _struct gm.concat_table_structure_v2(); return _struct; end if; if _db_ver < 16 then select into _struct gm.concat_table_structure_v3(); return _struct; end if; if _db_ver < 17 then select into _struct gm.concat_table_structure_v16_and_up(); return _struct; end if; if _db_ver < 18 then select into _struct gm.concat_table_structure_v17_and_up(); return _struct; end if; if _db_ver < 19 then select into _struct gm.concat_table_structure_v18_and_up(); return _struct; end if; select into _struct gm.concat_table_structure_v19_and_up(); return _struct; end;
copy of gm_concat_table_structure() until gnumed_v5, works on public, dem, clin, blobs
declare
_row record;
_total text;
begin
_total := '';
-- schema.table.column.data_type
for _row in
select * from information_schema.columns cols
where cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in ('public', 'dem', 'clin', 'blobs') and
tabs.table_type = 'BASE TABLE'
)
order by
md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
loop
_total := _total
|| _row.table_schema || '.'
|| _row.table_name || '.'
|| _row.column_name || '::'
|| _row.udt_name || '
';
end loop;
return _total;
end;
new concat_table_structure() starting with gnumed_v16, works on dem, clin, blobs, cfg, ref, i18n, sorts properly by bytea
declare
_row record;
_total text;
begin
_total := '';
-- schema.table.column.data_type
for _row in
select *
from information_schema.columns cols
where
cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n')
and
tabs.table_type = 'BASE TABLE'
) and
cols.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n')
order by
decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
loop
_total := _total
|| _row.table_schema || '.'
|| _row.table_name || '.'
|| _row.column_name || '::'
|| _row.udt_name || E'
';
end loop;
return _total;
end;
new concat_table_structure() starting with gnumed_v17, works on dem, clin, blobs, cfg, ref, i18n, bill, sorts properly by bytea
declare
_row record;
_total text;
begin
_total := '';
-- schema.table.column.data_type
for _row in
select *
from information_schema.columns cols
where
cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
and
tabs.table_type = 'BASE TABLE'
) and
cols.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
order by
decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
loop
_total := _total
|| _row.table_schema || '.'
|| _row.table_name || '.'
|| _row.column_name || '::'
|| _row.udt_name || E'
';
end loop;
return _total;
end;
new concat_table_structure() starting with gnumed_v18, works on dem, clin, blobs, cfg, ref, i18n, bill, includes primary keys, sorts properly by bytea
declare
_table_desc record;
_pk_desc record;
_column_desc record;
_total text;
begin
_total := '';
-- find relevant tables
for _table_desc in
select * from information_schema.tables tabs where
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
and
tabs.table_type = 'BASE TABLE'
order by
decode(md5(tabs.table_schema || tabs.table_name), 'hex')
-- loop over tables
loop
-- where are we at ?
_total := _total || 'TABLE:' || _table_desc.table_schema || '.' || _table_desc.table_name || E'
';
-- find PKs of that table
for _pk_desc in
select * from (
select
pg_class.oid::regclass || '.' || pg_attribute.attname || '::' || format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS primary_key_column
from
pg_index, pg_class, pg_attribute
where
--pg_class.oid = 'TABLENAME'::regclass
pg_class.oid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
AND
indrelid = pg_class.oid
AND
pg_attribute.attrelid = pg_class.oid
AND
pg_attribute.attnum = any(pg_index.indkey)
AND
indisprimary
) AS PKs
order by
decode(md5(PKs.primary_key_column), 'hex')
-- and loop over those PK columns
loop
_total := _total || 'PK:' || _pk_desc.primary_key_column || E'
';
end loop;
-- find columns of that table
for _column_desc in
select *
from information_schema.columns cols
where
cols.table_name = _table_desc.table_name
and
cols.table_schema = _table_desc.table_schema
order by
decode(md5(cols.column_name || cols.data_type), 'hex')
-- and loop over those columns
loop
-- add columns in the format "schema.table.column::data_type"
_total := _total || 'COL:'
|| _column_desc.table_schema || '.'
|| _column_desc.table_name || '.'
|| _column_desc.column_name || '::'
|| _column_desc.udt_name || E'
';
end loop;
end loop;
return _total;
end;
new concat_table_structure() starting with gnumed_v19, works on dem, clin, blobs, cfg, ref, i18n, bill, includes primary keys and constraints, sorts properly by bytea
declare
_table_desc record;
_pk_desc record;
_column_desc record;
_constraint_def record;
_total text;
begin
_total := '';
-- find relevant tables
for _table_desc in
select * from information_schema.tables tabs where
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n', 'bill')
and
tabs.table_type = 'BASE TABLE'
order by
decode(md5(tabs.table_schema || tabs.table_name), 'hex')
-- loop over tables
loop
-- where are we at ?
_total := _total || 'TABLE:' || _table_desc.table_schema || '.' || _table_desc.table_name || E'\n';
-- find PKs of that table
for _pk_desc in
select * from (
select
pg_class.oid::regclass || '.' || pg_attribute.attname || '::' || format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS primary_key_column
from
pg_index, pg_class, pg_attribute
where
--pg_class.oid = 'TABLENAME'::regclass
pg_class.oid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
AND
indrelid = pg_class.oid
AND
pg_attribute.attrelid = pg_class.oid
AND
pg_attribute.attnum = any(pg_index.indkey)
AND
indisprimary
) AS PKs
order by
decode(md5(PKs.primary_key_column), 'hex')
-- and loop over those PK columns
loop
_total := _total || 'PK:' || _pk_desc.primary_key_column || E'\n';
end loop;
-- find columns of that table
for _column_desc in
select *
from information_schema.columns cols
where
cols.table_name = _table_desc.table_name
and
cols.table_schema = _table_desc.table_schema
order by
decode(md5(cols.column_name || cols.data_type), 'hex')
-- and loop over those columns
loop
-- add columns in the format "schema.table.column::data_type"
_total := _total || 'COL:'
|| _column_desc.table_schema || '.'
|| _column_desc.table_name || '.'
|| _column_desc.column_name || '::'
|| _column_desc.udt_name || E'\n';
end loop;
-- find and loop over CONSTRAINTs of that table
for _constraint_def in
select * from
(select
tbl.contype,
'CONSTRAINT:type='
|| tbl.contype || ':'
|| replace(pg_catalog.pg_get_constraintdef(tbl.oid, true), ' ', '_')
|| '::active='
|| tbl.convalidated
as condef
from pg_catalog.pg_constraint tbl
where
tbl.conrelid = (_table_desc.table_schema || '.' || _table_desc.table_name)::regclass
-- include FKs only because we may have to add/remove
-- other (say, check) constraints in a minor release
-- for valid reasons which we do not want to affect
-- the hash, if however we need to modify a foreign
-- key that would, indeed, warrant a hash change
AND
tbl.contype = 'f'
) as CONSTRAINTs
order by
CONSTRAINTs.contype,
decode(md5(CONSTRAINTs.condef), 'hex')
loop
_total := _total || _constraint_def.condef || E'\n';
end loop;
end loop; -- over tables
return _total;
end;
new concat_table_structure() starting with gnumed_v6, works on dem, clin, blobs, cfg, ref, i18n
declare
_row record;
_total text;
begin
_total := '';
-- schema.table.column.data_type
for _row in
select * from information_schema.columns cols
where cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n') and
tabs.table_type = 'BASE TABLE'
)
order by
md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type)
loop
_total := _total
|| _row.table_schema || '.'
|| _row.table_name || '.'
|| _row.column_name || '::'
|| _row.udt_name || '
';
end loop;
return _total;
end;
new concat_table_structure() starting with gnumed_v8, works on dem, clin, blobs, cfg, ref, i18n, sorts properly by bytea
declare
_row record;
_total text;
begin
_total := '';
-- schema.table.column.data_type
for _row in
select * from information_schema.columns cols
where cols.table_name in (
select tabs.table_name from information_schema.tables tabs where
-- those which hold clinical data
tabs.table_schema in ('dem', 'clin', 'blobs', 'cfg', 'ref', 'i18n') and
tabs.table_type = 'BASE TABLE'
)
order by
decode(md5(cols.table_schema || cols.table_name || cols.column_name || cols.data_type), 'hex')
loop
_total := _total
|| _row.table_schema || '.'
|| _row.table_name || '.'
|| _row.column_name || '::'
|| _row.udt_name || '
';
end loop;
return _total;
end;
(Re)create sanity check triggers for all tables which have both fk_encounter and fk_episode.
DECLARE _schema name; _table name; _qualified_table2check name; _fk_encounter_col name; _fk_episode_col name; _total_success boolean; BEGIN raise notice 'gm.create_all_enc_epi_sanity_check_triggers()'; _total_success := True; -- loop over tables with encounter AND episode FKs -- (assumes there is only one of each) for _schema, _table in select (select pg_n.nspname from pg_catalog.pg_namespace pg_n where pg_n.oid = pg_c.relnamespace), pg_c.relname from pg_class pg_c where pg_c.oid in ( select distinct fk_tbl.conrelid from pg_catalog.pg_constraint fk_tbl where exists ( select 1 from pg_catalog.pg_constraint fk_tbl1 where fk_tbl1.contype = 'f' and fk_tbl1.conrelid = fk_tbl.conrelid and fk_tbl1.confrelid = 'clin.encounter'::regclass and fk_tbl1.confkey[1] = ( select attnum from pg_catalog.pg_attribute col_tbl where col_tbl.attname = 'pk' AND col_tbl.attrelid = 'clin.encounter'::regclass ) ) and exists ( select 1 from pg_catalog.pg_constraint fk_tbl2 where fk_tbl2.contype = 'f' and fk_tbl2.conrelid = fk_tbl.conrelid and fk_tbl2.confrelid = 'clin.episode'::regclass and fk_tbl2.confkey[1] = ( select attnum from pg_catalog.pg_attribute col_tbl where col_tbl.attname = 'pk' AND col_tbl.attrelid = 'clin.episode'::regclass ) ) ) loop _qualified_table2check := _schema || '.' || _table; raise notice 'gm.create_all_enc_epi_sanity_check_triggers(): processing %', _qualified_table2check; -- find encounter FK column name select col_tbl.attname into _fk_encounter_col from pg_catalog.pg_attribute col_tbl where col_tbl.attrelid = _qualified_table2check::regclass and col_tbl.attnum = ( select fk_tbl.conkey[1] from pg_catalog.pg_constraint fk_tbl where fk_tbl.contype = 'f' and fk_tbl.conrelid = _qualified_table2check::regclass and fk_tbl.confrelid = 'clin.encounter'::regclass and fk_tbl.confkey[1] = ( select col_tbl1.attnum from pg_catalog.pg_attribute col_tbl1 where col_tbl1.attname = 'pk' AND col_tbl1.attrelid = 'clin.encounter'::regclass ) ) ; -- find episode FK column name select col_tbl.attname into _fk_episode_col from pg_catalog.pg_attribute col_tbl where col_tbl.attrelid = _qualified_table2check::regclass and col_tbl.attnum = ( select fk_tbl.conkey[1] from pg_catalog.pg_constraint fk_tbl where fk_tbl.contype = 'f' and fk_tbl.conrelid = _qualified_table2check::regclass and fk_tbl.confrelid = 'clin.episode'::regclass and fk_tbl.confkey[1] = ( select col_tbl1.attnum from pg_catalog.pg_attribute col_tbl1 where col_tbl1.attname = 'pk' AND col_tbl1.attrelid = 'clin.episode'::regclass ) ) ; -- now create the trigger BEGIN PERFORM gm.create_enc_epi_sanity_check_trigger(_schema, _table, _fk_encounter_col, _fk_episode_col); EXCEPTION WHEN undefined_table OR invalid_foreign_key THEN raise warning 'gm.create_all_enc_epi_sanity_check_triggers(): error processing <%.%>, skipping', _schema, _table; _total_success := False; END; end loop; return _total_success; END;
(Re)create all table mod triggers for all registered tables.
DECLARE _notify_table record; _cmd text; _total_success boolean; BEGIN _total_success := True; -- loop over registered tables for _notify_table in select * from gm.notifying_tables loop BEGIN PERFORM gm.create_table_mod_triggers(_notify_table.schema_name, _notify_table.table_name, _drop_old_triggers); EXCEPTION WHEN undefined_table OR undefined_column THEN raise warning 'gm.create_all_table_mod_triggers(): error processing <%.%>, skipping', _notify_table.schema_name, _notify_table.table_name; _total_success := False; END; end loop; return _total_success; END;
This function can be run on any table in order to add enccounter <-> episode sanity check triggers to that table.
DECLARE
_qualified_table2check text;
_msg text;
_cmd text;
BEGIN
_qualified_table2check := _schema_name || '.' || _table_name;
raise notice 'gm.create_enc_epi_sanity_check_trigger(): % (.% vs .%)', _qualified_table2check, _fk_encounter_col, _fk_episode_col;
-- verify table exists
if not exists(select 1 from information_schema.tables where table_schema = _schema_name and table_name = _table_name) then
raise warning 'gm.create_enc_epi_sanity_check_trigger(): table <%> does not exist', _qualified_table2check;
raise exception undefined_table;
return false;
end if;
-- verify that it points to clin.encounter.pk
if not exists (
select 1
from
pg_catalog.pg_constraint fk_tbl
where
fk_tbl.contype = 'f'
AND
fk_tbl.confrelid = 'clin.encounter'::regclass
AND
fk_tbl.conrelid = _qualified_table2check::regclass
AND
fk_tbl.confkey[1] = (
select attnum from pg_catalog.pg_attribute col_tbl
where
col_tbl.attname = 'pk'
AND
col_tbl.attrelid = 'clin.encounter'::regclass
)
AND
fk_tbl.conkey[1] = (
select attnum from pg_catalog.pg_attribute col_tbl
where
col_tbl.attname = _fk_encounter_col
AND
col_tbl.attrelid = _qualified_table2check::regclass
)
) then
raise exception
'gm.create_enc_epi_sanity_check_trigger(): <%.%> does not point to clin.encounter.pk', _qualified_table2check, _fk_encounter_col
USING ERRCODE = 'invalid_foreign_key'
;
return false;
end if;
-- verify that it points to clin.episode.pk
if not exists (
select 1
from
pg_catalog.pg_constraint fk_tbl
where
fk_tbl.contype = 'f'
AND
fk_tbl.confrelid = 'clin.episode'::regclass
AND
fk_tbl.conrelid = _qualified_table2check::regclass
AND
fk_tbl.confkey[1] = (
select attnum from pg_catalog.pg_attribute col_tbl
where
col_tbl.attname = 'pk'
AND
col_tbl.attrelid = 'clin.episode'::regclass
)
AND
fk_tbl.conkey[1] = (
select attnum from pg_catalog.pg_attribute col_tbl
where
col_tbl.attname = _fk_episode_col
AND
col_tbl.attrelid = _qualified_table2check::regclass
)
) then
raise exception
'gm.create_enc_epi_sanity_check_trigger(): <%.%> does not point to clin.episode.pk', _qualified_table2check, _fk_episode_col
USING ERRCODE = 'invalid_foreign_key'
;
return false;
end if;
-- re-create trigger
_cmd := 'drop trigger if exists tr_sanity_check_enc_epi_ins_upd on ' || _qualified_table2check || ' cascade';
execute _cmd;
_cmd := 'create trigger tr_sanity_check_enc_epi_ins_upd ';
_cmd := _cmd || 'before insert or update ';
_cmd := _cmd || 'on ' || _qualified_table2check || ' ';
_cmd := _cmd || 'for each row when (NEW.fk_episode is not null) ';
_cmd := _cmd || 'execute procedure clin.trf_sanity_check_enc_epi_ins_upd(''' || _fk_encounter_col || ''', ''' || _fk_episode_col || ''')';
execute _cmd;
return True;
END;
DECLARE _name alias for $1; _indications alias for $2; _atc alias for $3; _is_live alias for $4; _generic_name text; _pk_brand integer; _pk_vaccine integer; _indication text; BEGIN _generic_name := _name || ' - generic vaccine'; raise notice 're-creating [%] (%)', _generic_name, array_to_string(_indications, '-'); -- retrieve or create ref.branded_drug entry for indication select pk into _pk_brand from ref.branded_drug where is_fake is true and description = _generic_name; if FOUND is false then insert into ref.branded_drug ( description, preparation, is_fake, atc_code ) values ( _generic_name, 'vaccine', -- this is rather arbitrary True, coalesce(_atc, 'J07') ) returning pk into _pk_brand; end if; -- retrieve or create clin.vaccine entry for generic brand select pk into _pk_vaccine from clin.vaccine where fk_brand = _pk_brand; if FOUND is false then insert into clin.vaccine ( is_live, fk_brand ) values ( _is_live, _pk_brand ) returning pk into _pk_vaccine; end if; -- link indications to vaccine delete from clin.lnk_vaccine2inds where fk_vaccine = _pk_vaccine; for _indication in select unnest(_indications) loop insert into clin.lnk_vaccine2inds ( fk_vaccine, fk_indication ) values ( _pk_vaccine, (select id from clin.vacc_indication where description = _indication) ); end loop; return true; END;
select gm.create_generic_combi_vaccine ( 'Td'::text, ARRAY['tetanus'::text,'diphtheria'::text], 'J07AM51', False ); select gm.create_generic_combi_vaccine ( 'DT'::text, ARRAY['tetanus'::text,'diphtheria'::text], 'J07AM51', False ); select gm.create_generic_combi_vaccine ( 'TdaP'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text], 'J07CA01', False ); select gm.create_generic_combi_vaccine ( 'TDaP'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text], 'J07CA01', False ); select gm.create_generic_combi_vaccine ( 'TdaP-Pol'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text,'poliomyelitis'::text], 'J07CA02', False ); select gm.create_generic_combi_vaccine ( 'TDaP-Pol'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text,'poliomyelitis'::text], 'J07CA02', False ); select gm.create_generic_combi_vaccine ( 'TDaP-Pol-HiB'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text,'poliomyelitis'::text,'haemophilus influenzae b'::text], 'J07CA06', False ); select gm.create_generic_combi_vaccine ( 'TDaP-Pol-HiB-HepB'::text, ARRAY['tetanus'::text,'diphtheria'::text,'pertussis'::text,'poliomyelitis'::text,'haemophilus influenzae b'::text,'hepatitis B'::text], 'J07CA09', False ); select gm.create_generic_combi_vaccine ( 'MMR'::text, ARRAY['measles'::text,'mumps'::text,'rubella'::text], 'J07BD52', True ); select gm.create_generic_combi_vaccine ( 'MMRV'::text, ARRAY['measles'::text,'mumps'::text,'rubella'::text,'varicella (chickenpox, shingles)'::text], 'J07BD54', True ); select gm.create_generic_combi_vaccine ( 'HepAB'::text, ARRAY['hepatitis A'::text,'hepatitis B'::text], 'J07BC20', False ); select True;
DECLARE _row record; _generic_name text; _pk_brand integer; _pk_vaccine integer; BEGIN for _row in select * from clin.vacc_indication loop _generic_name := _row.description || ' - generic vaccine'; raise notice 're-creating [%]', _generic_name; -- retrieve or create ref.branded_drug entry for indication select pk into _pk_brand from ref.branded_drug where is_fake is true and description = _generic_name; if FOUND is false then insert into ref.branded_drug ( description, preparation, is_fake, atc_code ) values ( _generic_name, 'vaccine', -- this is rather arbitrary True, coalesce(_row.atcs_single_indication[1], 'J07') ) returning pk into _pk_brand; end if; -- retrieve or create clin.vaccine entry for generic brand select pk into _pk_vaccine from clin.vaccine where fk_brand = _pk_brand; if FOUND is false then insert into clin.vaccine ( is_live, fk_brand ) values ( false, _pk_brand ) returning pk into _pk_vaccine; end if; -- link indication to vaccine delete from clin.lnk_vaccine2inds where fk_vaccine = _pk_vaccine; insert into clin.lnk_vaccine2inds ( fk_vaccine, fk_indication ) values ( _pk_vaccine, _row.id ); end loop; return true; END;
This function can be run on any table in order to add notification triggers to that table.
DECLARE
_qualified_table text;
_msg text;
_payload text;
_PK_col_name text;
_pk_accessor_SQL text;
_accessor_col text;
_col_candidate text;
_identity_accessor_SQL text;
_cmd text;
BEGIN
_qualified_table := _schema_name || '.' || _table_name;
raise notice 'gm.create_table_mod_triggers(): %', _qualified_table;
-- verify table exists
if not exists(select 1 from information_schema.tables where table_schema = _schema_name and table_name = _table_name) then
raise warning 'gm.create_table_mod_triggers(): table <%> does not exist', _qualified_table;
raise exception undefined_table;
return false;
end if;
-- find PK column
select
pg_attribute.attname into _PK_col_name
from
pg_index, pg_class, pg_attribute
where
pg_class.oid = _qualified_table::regclass
AND
indrelid = pg_class.oid
AND
pg_attribute.attrelid = pg_class.oid
AND
pg_attribute.attnum = any(pg_index.indkey)
AND
indisprimary;
if _PK_col_name is NULL then
raise warning 'gm.create_table_mod_triggers(): table <%> lacks a primary key', _qualified_table;
raise exception undefined_column;
return false;
end if;
_pk_accessor_SQL := 'select $1.' || _PK_col_name;
-- find identity accessor
-- special case
if _qualified_table = 'dem.identity' then
_identity_accessor_SQL := 'select $1.pk';
else
-- look for columns by which to retrieve affected person
_accessor_col := NULL;
foreach _col_candidate in array array['fk_identity', 'fk_patient', 'id_identity', 'fk_encounter'] loop
if exists (
select 1 from pg_class, pg_attribute where
pg_class.oid = _qualified_table::regclass
AND
pg_attribute.attname = _col_candidate
AND
pg_attribute.attrelid = pg_class.oid
) then
_accessor_col := _col_candidate;
exit;
end if;
end loop;
if _accessor_col = 'fk_encounter' then -- retrieve identity PK via fk_encounter
_identity_accessor_SQL := 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1';
elsif _accessor_col = 'fk_identity' then -- retrieve identity PK via fk_identity
_identity_accessor_SQL := 'select $1.fk_identity';
elsif _accessor_col = 'fk_patient' then -- retrieve identity PK via fk_patient
_identity_accessor_SQL := 'select $1.fk_patient';
elsif _accessor_col = 'id_identity' then -- retrieve identity PK via id_identity
_identity_accessor_SQL := 'select $1.id_identity';
else
_identity_accessor_SQL := '<NULL>';
end if;
end if;
-- drop triggers should they exist
-- old-name announcement triggers
-- remove in v22
_cmd := 'drop trigger if exists tr_announce_' || _schema_name || '_' || _table_name || '_ins_upd on ' || _qualified_table || ' cascade;';
execute _cmd;
_cmd := 'drop trigger if exists tr_announce_' || _schema_name || '_' || _table_name || '_del on ' || _qualified_table || ' cascade;';
execute _cmd;
-- new-name announcement triggers
_cmd := 'drop trigger if exists zzz_tr_announce_' || _schema_name || '_' || _table_name || '_ins_upd on ' || _qualified_table || ' cascade;';
execute _cmd;
_cmd := 'drop trigger if exists zzz_tr_announce_' || _schema_name || '_' || _table_name || '_del on ' || _qualified_table || ' cascade;';
execute _cmd;
-- remove in v21
if _drop_old_triggers is true then
_cmd := 'drop function if exists ' || _schema_name || '.trf_announce_' || _table_name || '_mod() cascade;';
execute _cmd;
_cmd := 'drop function if exists ' || _schema_name || '.trf_announce_' || _table_name || '_mod_no_pk() cascade;';
execute _cmd;
_cmd := 'drop function if exists ' || _schema_name || '.trf_announce_' || _table_name || '_generic_mod_no_pk() cascade;';
execute _cmd;
end if;
-- re-create triggers
-- 1) INSERT/UPDATE
_payload := 'table=' || _qualified_table || '::PK name=' || _PK_col_name;
_cmd := 'create constraint trigger zzz_tr_announce_' || _schema_name || '_' || _table_name || '_ins_upd';
_cmd := _cmd || ' after insert or update';
_cmd := _cmd || ' on ' || _qualified_table;
-- needed so a SELECT inside, say, _identity_accessor_SQL running
-- concurrently to a "lengthy" TX does not create a serialization
-- failure by being a rw-dependancy pivot
_cmd := _cmd || ' deferrable initially deferred';
_cmd := _cmd || ' for each row';
_cmd := _cmd || ' execute procedure gm.trf_announce_table_ins_upd(''' || _payload || ''', ''' || _pk_accessor_SQL || ''', ''' || _identity_accessor_SQL || ''');';
execute _cmd;
-- 2) DELETE
_payload := 'operation=DELETE::' || _payload;
_cmd := 'create constraint trigger zzz_tr_announce_' || _schema_name || '_' || _table_name || '_del';
_cmd := _cmd || ' after delete';
_cmd := _cmd || ' on ' || _qualified_table;
-- needed so a SELECT inside, say, _identity_accessor_SQL running
-- concurrently to a "lengthy" TX does not create a serialization
-- failure by being a rw-dependancy pivot
_cmd := _cmd || ' deferrable initially deferred';
_cmd := _cmd || ' for each row';
_cmd := _cmd || ' execute procedure gm.trf_announce_table_del(''' || _payload || ''', ''' || _pk_accessor_SQL || ''', ''' || _identity_accessor_SQL || ''');';
execute _cmd;
return True;
END;
To create users one needs to have CREATEROLE rights. Only gm-dbo is GRANTed EXECUTE. This way users need to know the gm-dbo (GNUmed admin) password to execute the function. Newly created users belong to group "gm-public" by default.
DECLARE _username alias for $1; _password alias for $2; _database text; _query text; BEGIN perform 1 from pg_user where usename = _username; if not FOUND then _query := 'create user ' || quote_ident(_username) || ' with password ' || quote_literal(_password) || ';'; execute _query; perform 1 from pg_user where usename = _username; if not FOUND then raise exception 'cannot create user [%]', _username; return false; end if; end if; _query := 'alter group "gm-logins" add user ' || quote_ident(_username) || ';'; execute _query; _query := 'alter group "gm-public" add user ' || quote_ident(_username) || ';'; execute _query; -- satisfy "database = samerole" in pg_hba.conf select into _database current_database(); _query := 'alter group ' || quote_ident(_database) || ' add user ' || quote_ident(_username) || ';'; execute _query; return true; END;
To disable users one needs to have CREATEROLE rights. Only gm-dbo is GRANTed EXECUTE. This way users need to know the gm-dbo (GNUmed admin) password to execute the function.
DECLARE _username alias for $1; _query text; BEGIN perform 1 from pg_user where usename = _username; if not FOUND then return true; end if; _query := 'alter group "gm-logins" drop user ' || quote_ident(_username) || ';'; execute _query; return true; END;
To drop users one needs to have CREATEROLE rights. Only gm-dbo is GRANTed EXECUTE. This way users need to know the gm-dbo (GNUmed admin) password to execute the function.
DECLARE _username alias for $1; _query text; BEGIN perform 1 from pg_user where usename = _username; if not FOUND then return true; end if; _query := 'drop user ' || quote_ident(_username) || ';'; execute _query; perform 1 from pg_user where usename = _username; if FOUND then return false; end if; return true; END;
select gm.get_users(current_database());
Convenience function listing all PostgreSQL accounts (roles) needed for a consistent dump of the database.
DECLARE
_db alias for $1;
_gm_users text[];
_user text;
BEGIN
-- GNUmed group roles
_gm_users := ARRAY['gm-logins', 'gm-public', 'gm-doctors', 'gm-staff', _db];
-- add roles being *members* of groups gm-logins, gm-public, _db
FOR _user in
select distinct rolname from pg_roles where oid in (
select member from pg_auth_members where roleid in (
select oid from pg_roles where rolname in ('gm-logins', 'gm-public', _db)
)
)
LOOP
continue when _user = 'postgres';
continue when _user = any(_gm_users);
_gm_users := _gm_users || _user;
END LOOP;
-- add roles mentioned in any *.modified_by fields
FOR _user in select distinct modified_by from audit.audit_fields LOOP
continue when _user = 'postgres';
continue when _user = any(_gm_users);
_gm_users := _gm_users || _user;
END LOOP;
-- add roles mentioned in dem.staff.db_user
FOR _user in select distinct db_user from dem.staff LOOP
continue when _user = 'postgres';
continue when _user = any(_gm_users);
_gm_users := _gm_users || _user;
END LOOP;
return _gm_users;
END;
input is either NULL or empty string -> True; input is not NULL and not empty -> FALSE
select (coalesce(trim($1), '') = '');
select (coalesce(trim($1), 'NULL') != '');
Function to create a chunked md5 sum on arbitrarily large LARGE OBJECTs.
DECLARE lo_id alias for $1; chunk_size alias for $2; _lo_fd integer; _lo_size integer; _chunk_count integer; _remainder integer; _md5_concat text; INV_READ constant integer := x'40000'::integer; SEEK_SET constant integer := 0; SEEK_END constant integer := 2; BEGIN -- check for existence of lo_id ? _lo_fd := lo_open(lo_id, INV_READ); -- get size _lo_size := lo_lseek(_lo_fd, 0, SEEK_END); PERFORM lo_close(_lo_fd); -- move further down if loread() proves faster -- calculate chunks and remainder _chunk_count := _lo_size / chunk_size; _remainder := _lo_size % chunk_size; -- loop over chunks _md5_concat := ''; FOR _chunk_id in 1.._chunk_count LOOP _md5_concat := _md5_concat || md5(lo_get(lo_id, (_chunk_id - 1) * chunk_size, chunk_size)); -- using loread() may be faster (as it directly accesses the -- existing lo_fd and thusly does not need to re-open the LO -- each round) END LOOP; -- add remainder _md5_concat := _md5_concat || md5(lo_get(lo_id, _chunk_count * chunk_size, _remainder)); return md5(_md5_concat); END;
This logs access to a patient EMR.
DECLARE
_action alias for $1;
BEGIN
if gm.is_null_or_blank_string(_action) then
raise exception 'gm.log_access2emr(): action detail cannot be NULL or empty';
end if;
insert into gm.access_log (user_action) values ('EMR access: ' || _action);
return;
END;
This logs access to the database.
insert into gm.access_log (user_action) values ($1);
declare
_filename alias for $1;
_version alias for $2;
_hash text;
begin
delete from gm.schema_revision where filename = _filename;
insert into gm.schema_revision (filename, version) values (
_filename,
_version
);
perform gm.log_other_access (
'database change script inserted: ' || _filename || ' (' || _version || ')'
);
select into _hash md5(gm.concat_table_structure());
return _hash;
end;
DECLARE _input alias for $1; BEGIN if _input is null then return null; end if; if trim(_input) = '' then return null; end if; return _input; END;
Register given table for notification trigger generator. Parameters are: (schema, table)
DECLARE _namespace alias for $1; _table alias for $2; dummy RECORD; BEGIN -- does table exist ? select relname into dummy from pg_class where relname = _table and relnamespace = (select oid from pg_namespace where nspname = _namespace) ; if not found then raise exception 'register_notifying_table(): Table [%.%] does not exist.', _namespace, _table; end if; -- make sure we can insert delete from gm.notifying_tables where table_name = _table and schema_name = _namespace; insert into gm.notifying_tables ( schema_name, table_name ) values ( _namespace, _table ); return true; END;
Remove fractions containing only zeros (n.000...) from NUMERICs/DECIMALs.
DECLARE _numeric_value alias for $1; _fraction numeric; _msg text; BEGIN _fraction := _numeric_value - trunc(_numeric_value); if _fraction <> 0 then return _numeric_value; end if; BEGIN return _numeric_value::bigint::numeric; EXCEPTION WHEN numeric_value_out_of_range THEN RAISE NOTICE '[gm.strip_allzeros_fraction]: cannot strip from %', _numeric_value; RETURN _numeric_value; END; END;
select gm.transfer_users($1, current_database());
This function transfers adds users from the group role given in the argument to the group role corresponding to the current database name. This enables group membership based authentication as used in GNUmed. This operation is typically only run on database upgrade and is only available to gm-dbo.
DECLARE
_source_group alias for $1;
_target_group alias for $2;
member_ids int[];
member_id int;
member_name text;
tmp text;
BEGIN
-- source group exists ?
perform 1 from pg_group where groname = _source_group;
if not FOUND then
raise exception 'gm_transfer_users(): source group [%] does not exist', _source_group;
return false;
end if;
-- target group exists ?
perform 1 from pg_group where groname = _target_group;
if not FOUND then
raise exception 'gm_transfer_users(): target group [%] does not exist', _target_group;
return false;
end if;
-- loop over group member IDs
select into member_ids grolist from pg_group where groname = _source_group;
FOR idx IN coalesce(array_lower(member_ids, 1), 0) .. coalesce(array_upper(member_ids, 1), -1) LOOP
member_id := member_ids[idx];
select into member_name usename from pg_user where usesysid = member_id;
tmp := 'gm_transfer_users(text): transferring "'
|| member_name || '" ('
|| member_id || ') from group "'
|| _source_group || '" to group "'
|| _target_group || '"';
raise notice '%', tmp;
-- satisfy "database = samegroup" in pg_hba.conf
tmp := 'alter group ' || quote_ident(_target_group) || ' add user ' || quote_ident(member_name) || ';';
execute tmp;
end LOOP;
return true;
END;
Trigger function announcing a DELETE on a table. sends signal: gm_table_mod payload: operation=DELETE, table=the table that is updated, PK name=the name of the PK column of the table (requires single column PKs), row PK=the PK of the affected row, person PK=the PK of the affected person,
declare
_payload text;
_pk_accessor_SQL text;
_pk_col_val integer;
_identity_accessor_SQL text;
_pk_identity integer;
begin
_pk_accessor_SQL := TG_ARGV[1];
EXECUTE _pk_accessor_SQL INTO STRICT _pk_col_val USING OLD;
_payload := TG_ARGV[0] || '::row PK=' || coalesce(_pk_col_val::text, 'NULL');
_identity_accessor_SQL := TG_ARGV[2];
if _identity_accessor_SQL <> '<NULL>' then
--raise notice '%.%: %', TG_TABLE_SCHEMA, TG_TABLE_NAME, _identity_accessor_SQL;
EXECUTE _identity_accessor_SQL INTO STRICT _pk_identity USING OLD;
_payload := _payload || '::person PK=' || coalesce(_pk_identity::text, 'NULL');
end if;
perform pg_notify('gm_table_mod', _payload);
return NULL;
end;
Trigger function announcing an INSERT or UPDATE to a table. sends signal: gm_table_mod payload: operation=INSERT/UPDATE, table=the table that is updated, PK name=the name of the PK column of the table (requires single column PKs), row PK=the PK of the affected row, person PK=the PK of the affected person,
declare
_payload text;
_pk_accessor_SQL text;
_pk_col_val integer;
_identity_accessor_SQL text;
_pk_identity integer;
begin
_pk_accessor_SQL := TG_ARGV[1];
EXECUTE _pk_accessor_SQL INTO STRICT _pk_col_val USING NEW;
_payload := 'operation=' || TG_OP || '::' || TG_ARGV[0] || '::row PK=' || coalesce(_pk_col_val::text, 'NULL');
_identity_accessor_SQL := TG_ARGV[2];
if _identity_accessor_SQL <> '<NULL>' then
EXECUTE _identity_accessor_SQL INTO STRICT _pk_identity USING NEW;
_payload := _payload || '::person PK=' || coalesce(_pk_identity::text, 'NULL');
end if;
perform pg_notify('gm_table_mod', _payload);
return NULL;
end;
BEGIN perform 1 from pg_user where usename = $1; if not FOUND then raise notice 'Cannot set database language. User % does not exist.', $1; return false; end if; return true; END;
select $1::text::integer;
Generated by PostgreSQL Autodoc