Dumped on 2026-04-01

Index of database - gnumed_v23

Table: atc

holds ATC data

atc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL

holds the ATC code
term text NOT NULL

the name of the drug component
data_source.pk fk_data_source integer NOT NULL
comment text

a comment on this ATC
pk serial PRIMARY KEY
administration_route text

by what route this drug is to be given

Table atc Inherits coding_system_root,

Index - Schema ref

Table: atc_staging

used for importing ATC data

atc_staging Structure
F-Key Name Type Description
atc text
name text
ddd text
unit text
adro text
comment text

Index - Schema ref

Table: auto_hint

This table stores SQL queries and the associated hints. If the query returns TRUE the client should display the hint.

auto_hint Structure
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
query text UNIQUE

This query is run against the database.
title text UNIQUE

A short title to summarize and identify the hint.
hint text

When the query returns true this is the hint that should be displayed.
url text

An URL relevant to the hint.
is_active boolean NOT NULL DEFAULT true

Whether or not this query/hint is active.
source text

Who provided query and hint.
lang text

The language the hint is written in.
recommendation_query text
popup_type integer NOT NULL DEFAULT 1

0: do not include in any popups (= 0 popups); 1: show an individual popup for this hint (= 1 hint per popup), 2: include in list popups only (= 2 or more hints in popup)
highlight_as_priority boolean NOT NULL DEFAULT true

Whether or not user wants this hint highlighted (possibly among a list of displayed hints), the way of highlighting is up to the application.

 

auto_hint Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_auto_hint_sane_hint CHECK ((gm.is_null_or_blank_string(hint) IS FALSE))
ref_auto_hint_sane_lang CHECK ((gm.is_null_or_blank_string(lang) IS FALSE))
ref_auto_hint_sane_popup_type CHECK (((popup_type > '-1'::integer) AND (popup_type < 3)))
ref_auto_hint_sane_query CHECK ((gm.is_null_or_blank_string(query) IS FALSE))
ref_auto_hint_sane_rec_query CHECK (gm.is_null_or_non_empty_string(recommendation_query))
ref_auto_hint_sane_source CHECK ((gm.is_null_or_blank_string(source) IS FALSE))
ref_auto_hint_sane_title CHECK ((gm.is_null_or_blank_string(title) IS FALSE))
ref_auto_hint_sane_url CHECK (gm.is_null_or_non_empty_string(url))

Index - Schema ref

Table: billable

items that *can* be billed to patients

billable Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
amount numeric NOT NULL

How much to bill for this item.
currency text DEFAULT 'u20AC'::text

The currency .amount is in.
vat_multiplier numeric NOT NULL

Multiplier to apply to .amount to calculate VAT, eg 0.19 = 19%, 0 = no VAT
active boolean NOT NULL DEFAULT true

Whether this item is currently supposed to be used for billing patients.
discountable boolean NOT NULL DEFAULT false

Whether discounts can be applied to this item.

Table billable Inherits coding_system_root,

 

billable Constraints
Name Constraint
ref_billable_sane_amount CHECK ((amount >= (0)::numeric))
ref_billable_sane_currency CHECK ((gm.is_null_or_blank_string(currency) IS FALSE))
ref_billable_sane_vat_multiplier CHECK ((vat_multiplier >= (0)::numeric))

Index - Schema ref

Table: coda

Holds CodA/CodZ codes.

coda Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL

The Reason-For-Encounter like meaning of the code.
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
icd10_text text

The text of the corresponding ICD-10 code.

Table coda Inherits coding_system_root,

 

coda Constraints
Name Constraint
chk_ref_coda_sane_icd10_text CHECK ((gm.is_null_or_blank_string(icd10_text) IS FALSE))

Index - Schema ref

Table: code_thesaurus_root

Synonyms for coded terms.

code_thesaurus_root Structure
F-Key Name Type Description
pk_thesaurus serial PRIMARY KEY
fk_code integer NOT NULL
synonym text

 

code_thesaurus_root Constraints
Name Constraint
ref_code_thes_root_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))

Index - Schema ref

Table: coding_system_root

Base table for coding system tables providing common fields.

coding_system_root Structure
F-Key Name Type Description
pk_coding_system serial PRIMARY KEY
code text UNIQUE#1 NOT NULL
term text UNIQUE#2 NOT NULL
data_source.pk fk_data_source integer UNIQUE#1 UNIQUE#2 NOT NULL

links to the data source for the external reference data set
comment text

an arbitrary comment on the code and/or term, child tables will use this in different ways

Index - Schema ref

Table: data_source

lists the available coding systems, classifications, ontologies and term lists

data_source Structure
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
name_long text UNIQUE#1 NOT NULL

long, complete (, ?official) name for this reference entry
name_short text UNIQUE#2 NOT NULL

shorthand for referring to this reference entry
version text UNIQUE#1 UNIQUE#2 NOT NULL

the exact and non-ambigous version for this entry
description text

optional arbitrary description, should include external license
source text NOT NULL

non-ambigous description of source; with this info in hand it must be possible to locate a copy of the external data set
lang text

 

data_source Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: document_type

pre-installed document types, do not change these as they will be overwritten during database upgrades at the discretion of the GNUmed team

document_type Structure
F-Key Name Type Description
pk serial PRIMARY KEY
scope text

can be used to group document types according to applicability, say, per country
description text NOT NULL

Index - Schema ref

Table: dose

Links doses to consumable substances.

dose Structure
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
substance.pk fk_substance integer NOT NULL

FK linking the substance
amount numeric NOT NULL

the amount of substance (the "5" in "5mg/ml")
unit text NOT NULL

unit of amount (the "mg" in "5mg/ml")
dose_unit text

unit of reference amount, IOW the "ml" in "5mg/ml" (the reference amount is always assumed to be 1, as in "5mg/1ml"), if NULL the unit is "1 delivery unit (tablet, capsule, suppository, sachet, ...)", corresponds to "dose unit" in UCUM or "unit of product usage" in SNOMED

 

dose Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_dose_sane_amount CHECK ((amount > (0)::numeric))
ref_dose_sane_dose_unit CHECK ((gm.is_null_or_non_empty_string(dose_unit) IS TRUE))
ref_dose_sane_unit CHECK ((gm.is_null_or_blank_string(unit) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: drug_product

The medicine chest of this praxis. Stores brands of drugs patients have been taking regardless of whether that brand still exists or in fact ever existed as such (as in lifestyle thingies).

drug_product Structure
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
description text NOT NULL

the name of this drug it is marketed under by the manufacturer
preparation text NOT NULL

the preparation the drug is delivered in, eg liquid, cream, tablet, etc.
atc_code text

the Anatomic Therapeutic Chemical code for this drug, used to compute possible substitutes
is_fake boolean NOT NULL DEFAULT false

Whether this truly is an actual brand of an actual drug rather than a fake brand created for documenting a, say, lifestyle nutrient or simply a component as opposed to a particular actual brand.
data_source.pk fk_data_source integer

the data source this entry came from
external_code text

an opaque code from an external data source, such as "PZN" in Germany
external_code_type text

an opaque code type from an external data source, such as "PZN" in Germany

 

drug_product Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
desc_not_empty CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
drug_sane_external_code CHECK ((gm.is_null_or_non_empty_string(external_code) IS TRUE))
drug_sane_external_code_type CHECK ((((external_code IS NULL) AND (external_code_type IS NULL)) OR ((external_code IS NOT NULL) AND (external_code_type IS NOT NULL))))
prep_not_empty CHECK ((gm.is_null_or_blank_string(preparation) IS FALSE))
sane_atc CHECK ((gm.is_null_or_non_empty_string(atc_code) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: form_types

types of forms which are available, generally by purpose (radiology, pathology, sick leave, Therapiebericht etc.)

form_types Structure
F-Key Name Type Description
name text UNIQUE NOT NULL
pk serial PRIMARY KEY

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icd10

Holds ICD-10 codes.

icd10 Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
star_code text

The star code which needs to be combined with the primary code to define the term.
aux_code text

The auxiliary code which needs to be combined with the primary code to define the term.

Table icd10 Inherits coding_system_root,

 

icd10 Constraints
Name Constraint
chk_ref_icd10_sane_aux_code CHECK ((gm.is_null_or_non_empty_string(aux_code) IS TRUE))
chk_ref_icd10_sane_star_code CHECK ((gm.is_null_or_non_empty_string(star_code) IS TRUE))

Index - Schema ref

Table: icd9

Holds ICD-9 codes.

icd9 Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY

Table icd9 Inherits coding_system_root,

Index - Schema ref

Table: icpc

This table holds ICPC2 codes along with local extensions.

icpc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text UNIQUE#1 NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
code_extension text UNIQUE#1

An extension to the bare code as defined in, say, the Netherlands or Australia.
short_description text

A shorter term for this item
icd10 text[]

Array of corresponding ICD-10 codes.
criteria text

Criteria to guide in selection of the appropriate code.
inclusions text

Items included under this code.
exclusions text[]

Items NOT included under this code because there is another code for them.
see_also text[]

See also under these items.
icpc_component.component fk_component smallint NOT NULL
icpc_chapter.chapter fk_chapter character(1) NOT NULL

Table icpc Inherits coding_system_root,

 

icpc Constraints
Name Constraint
ref_icpc_sane_code_ext CHECK ((gm.is_null_or_non_empty_string(code_extension) IS TRUE))
ref_icpc_sane_criteria CHECK ((gm.is_null_or_non_empty_string(criteria) IS TRUE))
ref_icpc_sane_exclusions CHECK (((exclusions IS NULL) OR (array_length(exclusions, 1) > 0)))
ref_icpc_sane_icd10 CHECK (((icd10 IS NULL) OR (array_length(icd10, 1) > 0)))
ref_icpc_sane_inclusions CHECK ((gm.is_null_or_non_empty_string(inclusions) IS TRUE))
ref_icpc_sane_see_also CHECK (((see_also IS NULL) OR (array_length(see_also, 1) > 0)))
ref_icpc_sane_term CHECK ((gm.is_null_or_blank_string(term) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icpc_chapter

The chapters of the ICPC.

icpc_chapter Structure
F-Key Name Type Description
pk serial PRIMARY KEY
chapter character(1) UNIQUE NOT NULL
description text UNIQUE

 

icpc_chapter Constraints
Name Constraint
ref_icpc_chapter_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icpc_component

The Components of the ICPC chapters.

icpc_component Structure
F-Key Name Type Description
pk serial PRIMARY KEY
component smallint UNIQUE NOT NULL
description text UNIQUE
typical_soap_cat text[]

An array of SOAP categories which codes from this component are typically used for.

 

icpc_component Constraints
Name Constraint
ref_icpc_component_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: icpc_thesaurus

Synonyms for ICPC terms.

icpc_thesaurus Structure
F-Key Name Type Description
pk_thesaurus integer NOT NULL DEFAULT nextval('ref.code_thesaurus_root_pk_thesaurus_seq'::regclass)
icpc.pk_coding_system fk_code integer NOT NULL
synonym text
pk serial PRIMARY KEY

Table icpc_thesaurus Inherits code_thesaurus_root,

 

icpc_thesaurus Constraints
Name Constraint
ref_code_thes_root_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))
ref_icpc_thes_sane_synonym CHECK ((gm.is_null_or_blank_string(synonym) IS FALSE))

Index - Schema ref

Table: keyword_expansion

Arbitrary binary or textual snippets of data. Used as text macros or document "ribbons".

keyword_expansion Structure
F-Key Name Type Description
pk serial PRIMARY KEY
fk_staff integer UNIQUE#1

The provider this expansion applies to. If NULL: applies to all providers.
keyword text UNIQUE#1 NOT NULL

A keyword by which to uniquely identify this snippet. Can only exist once per provider.
textual_data text

This holds the text of non-binary snippets.
owner text NOT NULL DEFAULT "current_user"()

Who "owns" this text expansion.
encrypted boolean NOT NULL DEFAULT false

If true the snippet is encrypted with GnuPG.
binary_data bytea

This holds the binary data of non-textual snippets

 

keyword_expansion Constraints
Name Constraint
keyword_expansion_keyword_check CHECK ((btrim(keyword) <> ''::text))
ref_kwd_exp_binary_xor_textual CHECK ((((binary_data IS NULL) AND (textual_data IS NOT NULL)) OR ((binary_data IS NOT NULL) AND (textual_data IS NULL))))
ref_kwd_exp_sane_data CHECK (((binary_data IS NULL) OR (octet_length(binary_data) > 0)))
ref_kwd_exp_sane_keyword CHECK ((gm.is_null_or_blank_string(keyword) IS FALSE))
ref_kwd_exp_sane_text CHECK ((gm.is_null_or_non_empty_string(textual_data) IS TRUE))

Index - Schema ref

Table: lnk_dose2drug

Links doses to drug products

lnk_dose2drug Structure
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
drug_product.pk fk_drug_product integer NOT NULL

FK linking the drug product
dose.pk fk_dose integer NOT NULL

FK linking the dose

 

lnk_dose2drug Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Index - Schema ref

Table: lnk_indic2vaccine

Links vaccination targets to vaccines.

lnk_indic2vaccine Structure
F-Key Name Type Description
pk serial PRIMARY KEY
vacc_indication.pk fk_indication integer NOT NULL
vaccine.pk fk_vaccine integer NOT NULL

Index - Schema ref

Table: lnk_loinc2substance

Links LOINC codes to substances (for monitoring).

lnk_loinc2substance Structure
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
substance.pk fk_substance integer NOT NULL

FK linking the substance
loinc text

LOINC to monitor for substance
max_age interval

maximum recommended age of value of monitored LOINC in a patient, set this to something like "999 years" if it needs to be monitored just once, NULL = unspecified
comment text

a comment on this monitoring advice

 

lnk_loinc2substance Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_ll2s_sane_comment CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
ref_ll2s_sane_loinc CHECK ((gm.is_null_or_blank_string(loinc) IS FALSE))

Index - Schema ref

Table: loinc

holds LOINC codes

loinc Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL

holds LOINC_NUM
term text NOT NULL

holds either long_common_name or a ":".join of .component to .method_type
data_source.pk fk_data_source integer NOT NULL

points to a particular data source which in turn holds the version
comment text
pk serial PRIMARY KEY
component text
property text
time_aspect text
system text
scale_type text
method_type text
related_names_1_old text
grouping_class text
loinc_internal_source text
dt_last_change text
change_type text
answer_list text
code_status text
maps_to text
scope text
normal_range text
ipcc_units text
reference text
exact_component_synonym text
molar_mass text
grouping_class_type smallint
formula text
species text
example_answers text
acs_synonyms text
base_name text
final text
naa_ccr_id text
code_table text
is_set_root boolean
panel_elements text
survey_question_text text
survey_question_source text
units_required text
submitted_units text
related_names_2 text
short_name text
order_obs text
cdisc_common_tests text
hl7_field_subfield_id text
external_copyright_notice text
example_units text
inpc_percentage text
long_common_name text

Table loinc Inherits coding_system_root,

Index - Schema ref

Table: ops

Holds OPS (German ICPM-CM) codes.

ops Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY
second_code text
requires_laterality boolean

Table ops Inherits coding_system_root,

Index - Schema ref

Table: other_code

Holds codes from "other" coding systems for which no specific tables exist just yet.

other_code Structure
F-Key Name Type Description
pk_coding_system integer NOT NULL DEFAULT nextval('ref.coding_system_root_pk_coding_system_seq'::regclass)
code text NOT NULL
term text NOT NULL
data_source.pk fk_data_source integer NOT NULL
comment text
pk serial PRIMARY KEY

Table other_code Inherits coding_system_root,

Index - Schema ref

Table: papersizes

papersizes Structure
F-Key Name Type Description
pk serial PRIMARY KEY
name text UNIQUE NOT NULL
size point NOT NULL

(cm, cm)

Index - Schema ref

Table: paperwork_templates

form and letter template definitions

paperwork_templates Structure
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
form_types.pk fk_template_type integer NOT NULL
instance_type text

default document type to store documents generated from this form under, note that this may generate rows in blobs.doc_type if set to a non-existant document type
name_short text UNIQUE#2 NOT NULL

a short name for use in a GUI or some such
name_long text UNIQUE#1 UNIQUE#2 NOT NULL

a long name unambigously describing the form
external_version text UNIQUE#1 NOT NULL
gnumed_revision double precision UNIQUE#1

GnuMed internal form def version, may occur if we rolled out a faulty form def
engine text NOT NULL DEFAULT 'O'::text

the business layer forms engine used to process this form, currently: - T: plain text (generic postprocessing) - L: LaTeX - H: HTML - O: OpenOffice - I: image editor (visual progress notes) - G: gnuplot scripts (test results graphing) - P: PDF form (FDF based) - A: AbiWord - X: Xe(La)TeX - S: XSLT
in_use boolean NOT NULL DEFAULT true

whether this template is currently actively used in a given practice
filename text

the filename from when the template data was imported if applicable, used by some engines (such as OOo) to differentiate what to do with certain files, such as *.ott vs. *.ods, GNUmed uses it to derive a file extension when exporting the template data
data bytea

the template complete with placeholders in the format accepted by the engine defined in ref.paperwork_templates.engine
edit_after_substitution boolean NOT NULL DEFAULT true

Whether to offer last-minute, manual, generic editing inbetween placeholder substitution and final output generation.

 

paperwork_templates Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_templates_engine_range CHECK ((engine = ANY (ARRAY['T'::text, 'L'::text, 'H'::text, 'O'::text, 'I'::text, 'G'::text, 'P'::text, 'A'::text, 'X'::text, 'S'::text])))

Index - Schema ref

Table: substance

Holds substances that are consumed by patients for various reasons.

substance Structure
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
description text

the name of the substance
atc text

the ATC of the substance
intake_instructions text

any intake instructions for the substance

 

substance Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_substance_sane_desc CHECK ((gm.is_null_or_non_empty_string(description) IS TRUE))
ref_substance_sane_instructions CHECK ((gm.is_null_or_non_empty_string(intake_instructions) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: tag_image

Text+image tags that can be applied to a person for characterization.

tag_image Structure
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
description text UNIQUE

A textual description of the meaning of the tag. Keep this reasonably short.
filename text

An example filename, mainly for preserving the file suffix. Set during import, suffix used during export.
image bytea NOT NULL

An image showing the meaning of the tag.

 

tag_image Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
ref_tag_image_sane_desc CHECK ((gm.is_null_or_blank_string(description) IS FALSE))
ref_tag_image_sane_filename CHECK ((gm.is_null_or_non_empty_string(filename) IS TRUE))

Index - Schema ref

View: v_atc

v_atc Structure
F-Key Name Type Description
pk_atc integer
atc text
term text
administration_route text
comment text
is_group_code boolean
atc_level integer
name_long text
name_short text
version text
lang text
pk_coding_system integer
pk_data_source integer 
SELECT a.pk AS pk_atc
,
    a.code AS atc
,
    a.term
,
    a.administration_route
,
    a.comment
,
    
(octet_length
     (a.code) < 7
) AS is_group_code
,
    
(octet_length
     (a.code) - 
     (octet_length
           (a.code) / 3
     )
) AS atc_level
,
    rds.name_long
,
    rds.name_short
,
    rds.version
,
    rds.lang
,
    a.pk_coding_system
,
    a.fk_data_source AS pk_data_source
   
FROM (ref.atc a
     
  JOIN ref.data_source rds 
    ON (
           (rds.pk = a.fk_data_source)
     )
);

Index - Schema ref

View: v_auto_hints

v_auto_hints Structure
F-Key Name Type Description
pk_auto_hint integer
query text
recommendation_query text
title text
hint text
url text
is_active boolean
source text
lang text
popup_type integer
highlight_as_priority boolean
rationale4suppression text
recommendation text
md5_sum text
xmin_auto_hint xid 
SELECT pk AS pk_auto_hint
,
    query
,
    recommendation_query
,
    title
,
    hint
,
    url
,
    is_active
,
    source
,
    lang
,
    popup_type
,
    highlight_as_priority
,
    NULL::text AS rationale4suppression
,
    NULL::text AS recommendation
,
    md5
(
     (
           (
                 (
                       (COALESCE
                             (query
                                   ,''::text
                             ) || COALESCE
                             (recommendation_query
                                   ,''::text
                             )
                       ) || COALESCE
                       (title
                             ,''::text
                       )
                 ) || COALESCE
                 (hint
                       ,''::text
                 )
           ) || COALESCE
           (url
                 ,''::text
           )
     )
) AS md5_sum
,
    xmin AS xmin_auto_hint
   
FROM ref.auto_hint;

Index - Schema ref

View: v_billables

v_billables Structure
F-Key Name Type Description
pk_billable integer
billable_code text
billable_description text
raw_amount numeric
amount_with_vat numeric
currency text
comment text
vat_multiplier numeric
active boolean
discountable boolean
catalog_long text
catalog_short text
catalog_version text
catalog_language text
pk_data_source integer
pk_coding_system_root integer
xmin_billable xid 
SELECT r_b.pk AS pk_billable
,
    r_b.code AS billable_code
,
    r_b.term AS billable_description
,
    r_b.amount AS raw_amount
,
    
(r_b.amount + 
     (r_b.amount * r_b.vat_multiplier)
) AS amount_with_vat
,
    r_b.currency
,
    r_b.comment
,
    r_b.vat_multiplier
,
    r_b.active
,
    r_b.discountable
,
    r_ds.name_long AS catalog_long
,
    r_ds.name_short AS catalog_short
,
    r_ds.version AS catalog_version
,
    r_ds.lang AS catalog_language
,
    r_b.fk_data_source AS pk_data_source
,
    r_b.pk_coding_system AS pk_coding_system_root
,
    r_b.xmin AS xmin_billable
   
FROM (ref.billable r_b
     
LEFT JOIN ref.data_source r_ds 
    ON (
           (r_b.fk_data_source = r_ds.pk)
     )
);

Index - Schema ref

View: v_coded_terms

This view aggregates all official (reference) terms, including "official" synonyms, for which a corresponding code is known to the system.

v_coded_terms Structure
F-Key Name Type Description
code text
term text
coding_system text
coding_system_long text
version text
lang text
pk_generic_code integer 
SELECT r_csr.code
,
    r_csr.term
,
    r_ds.name_short AS coding_system
,
    r_ds.name_long AS coding_system_long
,
    r_ds.version
,
    r_ds.lang
,
    r_csr.pk_coding_system AS pk_generic_code
   
FROM (ref.coding_system_root r_csr
     
  JOIN ref.data_source r_ds 
    ON (
           (r_csr.fk_data_source = r_ds.pk)
     )
)
UNION
 
SELECT ri.code
,
    r_it.synonym AS term
,
    r_ds.name_short AS coding_system
,
    r_ds.name_long AS coding_system_long
,
    r_ds.version
,
    r_ds.lang
,
    r_it.fk_code AS pk_generic_code
   
FROM (
     (ref.icpc_thesaurus r_it
     
   LEFT JOIN ref.icpc ri 
          ON (
                 (r_it.fk_code = ri.pk_coding_system)
           )
     )
     
LEFT JOIN ref.data_source r_ds 
    ON (
           (ri.fk_data_source = r_ds.pk)
     )
);

Index - Schema ref

View: v_drug_components

v_drug_components Structure
F-Key Name Type Description
pk_component integer
product text
substance text
amount numeric
unit text
dose_unit text
preparation text
l10n_preparation text
intake_instructions text
loincs json[]
atc_substance text
atc_drug text
external_code text
external_code_type text
is_fake_product boolean
pk_drug_product integer
pk_dose integer
pk_substance integer
pk_data_source integer
xmin_lnk_dose2drug xid 
SELECT r_ld2d.pk AS pk_component
,
    r_dp.description AS product
,
    r_vsd.substance
,
    r_vsd.amount
,
    r_vsd.unit
,
    r_vsd.dose_unit
,
    r_dp.preparation
,
    _
(r_dp.preparation) AS l10n_preparation
,
    r_vsd.intake_instructions
,
    r_vsd.loincs
,
    r_vsd.atc_substance
,
    r_dp.atc_code AS atc_drug
,
    r_dp.external_code
,
    r_dp.external_code_type
,
    r_dp.is_fake AS is_fake_product
,
    r_dp.pk AS pk_drug_product
,
    r_vsd.pk_dose
,
    r_vsd.pk_substance
,
    r_dp.fk_data_source AS pk_data_source
,
    r_ld2d.xmin AS xmin_lnk_dose2drug
   
FROM (
     (ref.lnk_dose2drug r_ld2d
     
        JOIN ref.drug_product r_dp 
          ON (
                 (r_ld2d.fk_drug_product = r_dp.pk)
           )
     )
     
  JOIN ref.v_substance_doses r_vsd 
    ON (
           (r_ld2d.fk_dose = r_vsd.pk_dose)
     )
);

Index - Schema ref

View: v_drug_products

v_drug_products Structure
F-Key Name Type Description
pk_drug_product integer
product text
preparation text
l10n_preparation text
atc text
external_code text
external_code_type text
is_fake_product boolean
is_vaccine boolean
components json[]
pk_data_source integer
xmin_drug_product xid 
SELECT pk AS pk_drug_product
,
    description AS product
,
    preparation
,
    _
(preparation) AS l10n_preparation
,
    atc_code AS atc
,
    external_code
,
    external_code_type
,
    is_fake AS is_fake_product
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM ref.vaccine c_v
          
       WHERE (c_v.fk_drug_product = r_dp.pk)
     )
) AS is_vaccine
,
    ARRAY
(
SELECT row_to_json
     (component_row.*) AS row_to_json
           
  FROM (
      SELECT r_vsd.substance
           ,
                    r_vsd.amount
           ,
                    r_vsd.unit
           ,
                    r_vsd.dose_unit
           ,
                    r_vsd.intake_instructions
           ,
                    r_vsd.loincs
           ,
                    r_vsd.atc_substance
           ,
                    r_ld2d.pk AS pk_component
           ,
                    r_vsd.pk_dose
           ,
                    r_vsd.pk_substance
                   
        FROM (ref.lnk_dose2drug r_ld2d
                     
              JOIN ref.v_substance_doses r_vsd 
                ON (
                       (r_ld2d.fk_dose = r_vsd.pk_dose)
                 )
           )
                  
       WHERE (r_ld2d.fk_drug_product = r_dp.pk)
     ) component_row
) AS components
,
    fk_data_source AS pk_data_source
,
    xmin AS xmin_drug_product
   
FROM ref.drug_product r_dp;

Index - Schema ref

View: v_generic_codes

Denormalized generic codes.

v_generic_codes Structure
F-Key Name Type Description
pk_generic_code integer
code text
term text
name_long text
name_short text
version text
lang text
code_table regclass
pk_data_source integer 
SELECT r_csr.pk_coding_system AS pk_generic_code
,
    r_csr.code
,
    r_csr.term
,
    r_ds.name_long
,
    r_ds.name_short
,
    r_ds.version
,
    r_ds.lang
,
    
(r_csr.tableoid)::regclass AS code_table
,
    r_csr.fk_data_source AS pk_data_source
   
FROM (ref.coding_system_root r_csr
     
  JOIN ref.data_source r_ds 
    ON (
           (r_ds.pk = r_csr.fk_data_source)
     )
);

Index - Schema ref

View: v_icpc

View over denormalized ICPC2 data.

v_icpc Structure
F-Key Name Type Description
code text
code_extension text
extended_code text
term text
short_description text
code_chapter character(1)
chapter text
l10n_chapter text
code_component smallint
component text
l10n_component text
typical_soap_cat text[]
icd10 text[]
criteria text
inclusions text
exclusions text[]
see_also text[]
comment text
name_short text
name_long text
version text
lang text
pk_icpc integer
pk_data_source integer 
SELECT ri.code
,
    ri.code_extension
,
    
(ri.code || COALESCE
     (ri.code_extension
           ,''::text
     )
) AS extended_code
,
    ri.term
,
    ri.short_description
,
    ri.fk_chapter AS code_chapter
,
    rich.description AS chapter
,
    _
(rich.description) AS l10n_chapter
,
    ri.fk_component AS code_component
,
    rico.description AS component
,
    _
(rico.description) AS l10n_component
,
    rico.typical_soap_cat
,
    ri.icd10
,
    ri.criteria
,
    ri.inclusions
,
    ri.exclusions
,
    ri.see_also
,
    ri.comment
,
    rds.name_short
,
    rds.name_long
,
    rds.version
,
    rds.lang
,
    ri.pk AS pk_icpc
,
    ri.fk_data_source AS pk_data_source
   
FROM (
     (
           (ref.icpc ri
     
              JOIN ref.data_source rds 
                ON (
                       (ri.fk_data_source = rds.pk)
                 )
           )
     
        JOIN ref.icpc_chapter rich 
          ON (
                 (ri.fk_chapter = rich.chapter)
           )
     )
     
  JOIN ref.icpc_component rico 
    ON (
           (ri.fk_component = rico.component)
     )
);

Index - Schema ref

View: v_icpc_thesaurus

View over denormalized ICPC2 thesaurus.

v_icpc_thesaurus Structure
F-Key Name Type Description
pk_icpc_thesaurus integer
code text
term text
synonym text
pk_thesaurus integer
pk_coding_system integer
pk_icpc integer 
SELECT rit.pk AS pk_icpc_thesaurus
,
    ri.code
,
    ri.term
,
    rit.synonym
,
    rit.pk_thesaurus
,
    rit.fk_code AS pk_coding_system
,
    ri.pk AS pk_icpc
   
FROM (ref.icpc_thesaurus rit
     
  JOIN ref.icpc ri 
    ON (
           (rit.fk_code = ri.pk_coding_system)
     )
);

Index - Schema ref

View: v_indications4vaccine

Denormalizes indications per vaccine.

v_indications4vaccine Structure
F-Key Name Type Description
pk_vaccine integer
indication gm.nonempty_text
l10n_indication text
pk_indication integer
atc_indication gm.nonempty_text
vaccine text
preparation text
l10n_preparation text
atc_product text
atc_vaccine gm.nonempty_text
external_code text
external_code_type text
is_live boolean
min_age interval
max_age interval
comment text
all_indications json[]
pk_drug_product integer
pk_data_source integer
xmin_vaccine xid 
SELECT r_v.pk AS pk_vaccine
,
    r_vi.target AS indication
,
    _
(
     (r_vi.target)::text
) AS l10n_indication
,
    r_vi.pk AS pk_indication
,
    r_vi.atc AS atc_indication
,
    r_dp.description AS vaccine
,
    r_dp.preparation
,
    _
(r_dp.preparation) AS l10n_preparation
,
    r_dp.atc_code AS atc_product
,
    r_v.atc AS atc_vaccine
,
    r_dp.external_code
,
    r_dp.external_code_type
,
    r_v.is_live
,
    r_v.min_age
,
    r_v.max_age
,
    r_v.comment
,
    ARRAY
(
SELECT row_to_json
     (indication_row.*) AS row_to_json
           
  FROM (
      SELECT r_vi_2.target AS indication
           ,
                    _
           (
                 (r_vi_2.target)::text
           ) AS l10n_indication
           ,
                    r_vi_2.atc AS atc_indication
                   
        FROM (ref.lnk_indic2vaccine r_li2v_2
                     
              JOIN ref.vacc_indication r_vi_2 
                ON (
                       (r_vi_2.pk = r_li2v_2.fk_indication)
                 )
           )
                  
       WHERE (r_li2v_2.fk_vaccine = r_v.pk)
     ) indication_row
) AS all_indications
,
    r_v.fk_drug_product AS pk_drug_product
,
    r_dp.fk_data_source AS pk_data_source
,
    r_v.xmin AS xmin_vaccine
   
FROM (
     (
           (ref.vaccine r_v
     
              JOIN ref.lnk_indic2vaccine r_li2v 
                ON (
                       (r_li2v.fk_vaccine = r_v.pk)
                 )
           )
     
        JOIN ref.vacc_indication r_vi 
          ON (
                 (r_vi.pk = r_li2v.fk_indication)
           )
     )
     
LEFT JOIN ref.drug_product r_dp 
    ON (
           (r_dp.pk = r_v.fk_drug_product)
     )
);

Index - Schema ref

View: v_keyword_expansions

Just a slightly more convenient view over expansions.

v_keyword_expansions Structure
F-Key Name Type Description
pk_expansion integer
pk_staff integer
keyword text
expansion text
is_encrypted boolean
is_textual boolean
data_size integer
public_expansion boolean
private_expansion boolean
owner text
xmin_expansion xid 
SELECT pk AS pk_expansion
,
    fk_staff AS pk_staff
,
    keyword
,
    textual_data AS expansion
,
    encrypted AS is_encrypted
,
    
(binary_data IS NULL) AS is_textual
,
    octet_length
(binary_data) AS data_size
,
    
(fk_staff IS NULL) AS public_expansion
,
    
(fk_staff IS NOT NULL) AS private_expansion
,
    owner
,
    xmin AS xmin_expansion
   
FROM ref.keyword_expansion r_ke;

Index - Schema ref

View: v_lnk_loincs2substances

v_lnk_loincs2substances Structure
F-Key Name Type Description
pk_lnk_loinc2substance integer
substance text
loinc text
max_age interval
comment text
atc text
pk_substance integer
xmin_lnk_loinc2substance xid 
SELECT r_ll2s.pk AS pk_lnk_loinc2substance
,
    r_s.description AS substance
,
    r_ll2s.loinc
,
    r_ll2s.max_age
,
    r_ll2s.comment
,
    r_s.atc
,
    r_s.pk AS pk_substance
,
    r_ll2s.xmin AS xmin_lnk_loinc2substance
   
FROM (ref.substance r_s
     
  JOIN ref.lnk_loinc2substance r_ll2s 
    ON (
           (r_ll2s.fk_substance = r_s.pk)
     )
);

Index - Schema ref

View: v_paperwork_templates

v_paperwork_templates Structure
F-Key Name Type Description
pk_paperwork_template integer
name_short text
name_long text
external_version text
gnumed_revision double precision
template_type text
l10n_template_type text
instance_type text
l10n_instance_type text
engine text
in_use boolean
edit_after_substitution boolean
filename text
has_template_data boolean
last_modified timestamp with time zone
modified_by text
pk_template_type integer
xmin_paperwork_template xid 
SELECT pk AS pk_paperwork_template
,
    name_short
,
    name_long
,
    external_version
,
    gnumed_revision
,
    
(
SELECT r_ft.name
           
  FROM ref.form_types r_ft
          
 WHERE (r_ft.pk = r_pt.fk_template_type)
) AS template_type
,
    
(
SELECT _
     (r_ft.name) AS _
           
  FROM ref.form_types r_ft
          
 WHERE (r_ft.pk = r_pt.fk_template_type)
) AS l10n_template_type
,
    COALESCE
(instance_type
     , (
      SELECT r_ft.name
           
        FROM ref.form_types r_ft
          
       WHERE (r_ft.pk = r_pt.fk_template_type)
     )
) AS instance_type
,
    COALESCE
(_
     (instance_type)
     , (
      SELECT _
           (r_ft.name) AS _
           
        FROM ref.form_types r_ft
          
       WHERE (r_ft.pk = r_pt.fk_template_type)
     )
) AS l10n_instance_type
,
    engine
,
    in_use
,
    edit_after_substitution
,
    filename
,
        CASE
            WHEN 
(data IS NOT NULL) THEN true
            ELSE false
        END AS has_template_data
,
    modified_when AS last_modified
,
    COALESCE
(
     (
      SELECT d_s.short_alias
           
        FROM dem.staff d_s
          
       WHERE (d_s.db_user = r_pt.modified_by)
     )
     , (
           ('<'::text || 
                 (modified_by)::text
           ) || '>'::text
     )
) AS modified_by
,
    fk_template_type AS pk_template_type
,
    xmin AS xmin_paperwork_template
   
FROM ref.paperwork_templates r_pt;

Index - Schema ref

View: v_substance_doses

v_substance_doses Structure
F-Key Name Type Description
pk_dose integer
substance text
amount numeric
unit text
dose_unit text
intake_instructions text
atc_substance text
loincs json[]
pk_substance integer
xmin_dose xid 
SELECT r_d.pk AS pk_dose
,
    r_vs.substance
,
    r_d.amount
,
    r_d.unit
,
    r_d.dose_unit
,
    r_vs.intake_instructions
,
    r_vs.atc AS atc_substance
,
    r_vs.loincs
,
    r_vs.pk_substance
,
    r_d.xmin AS xmin_dose
   
FROM (ref.dose r_d
     
  JOIN ref.v_substances r_vs 
    ON (
           (r_d.fk_substance = r_vs.pk_substance)
     )
);

Index - Schema ref

View: v_substances

v_substances Structure
F-Key Name Type Description
pk_substance integer
substance text
intake_instructions text
atc text
loincs json[]
xmin_substance xid 
SELECT pk AS pk_substance
,
    description AS substance
,
    intake_instructions
,
    atc
,
    ARRAY
(
SELECT row_to_json
     (loinc_row.*) AS row_to_json
           
  FROM (
      SELECT r_ll2s.loinc
           ,
                    r_ll2s.comment
           ,
                    date_part
           ('epoch'::text
                 , r_ll2s.max_age
           ) AS max_age_in_secs
           ,
                    
           (r_ll2s.max_age)::text AS max_age_str
                   
        FROM ref.lnk_loinc2substance r_ll2s
                  
       WHERE (r_ll2s.fk_substance = r_s.pk)
     ) loinc_row
) AS loincs
,
    xmin AS xmin_substance
   
FROM ref.substance r_s;

Index - Schema ref

View: v_tag_images_no_data

v_tag_images_no_data Structure
F-Key Name Type Description
pk_tag_image integer
description text
l10n_description text
filename text
size integer
is_in_use boolean
xmin_tag_image xid 
SELECT pk AS pk_tag_image
,
    description
,
    _
(description) AS l10n_description
,
    filename
,
    octet_length
(COALESCE
     (image
           ,'\x'::bytea
     )
) AS size
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.identity_tag dit
          
       WHERE (dit.fk_tag = rti.pk)
         LIMIT 1
     )
) AS is_in_use
,
    xmin AS xmin_tag_image
   
FROM ref.tag_image rti;

Index - Schema ref

View: v_vaccines

A list of vaccines.

v_vaccines Structure
F-Key Name Type Description
pk_vaccine integer
vaccine text
preparation text
l10n_preparation text
is_live boolean
min_age interval
max_age interval
comment text
atc_vaccine gm.nonempty_text
atc_product text
external_code text
external_code_type text
indications json[]
pk_drug_product integer
pk_data_source integer
xmin_vaccine xid 
SELECT r_v.pk AS pk_vaccine
,
    r_dp.description AS vaccine
,
    r_dp.preparation
,
    _
(r_dp.preparation) AS l10n_preparation
,
    r_v.is_live
,
    r_v.min_age
,
    r_v.max_age
,
    r_v.comment
,
    r_v.atc AS atc_vaccine
,
    r_dp.atc_code AS atc_product
,
    r_dp.external_code
,
    r_dp.external_code_type
,
    ARRAY
(
SELECT row_to_json
     (indication_row.*) AS row_to_json
           
  FROM (
      SELECT r_vi.target AS indication
           ,
                    _
           (
                 (r_vi.target)::text
           ) AS l10n_indication
           ,
                    r_vi.atc AS atc_indication
           ,
                    r_vi.pk AS pk_indication
                   
        FROM (ref.lnk_indic2vaccine r_li2v
                     
              JOIN ref.vacc_indication r_vi 
                ON (
                       (r_li2v.fk_indication = r_vi.pk)
                 )
           )
                  
       WHERE (r_li2v.fk_vaccine = r_v.pk)
     ) indication_row
) AS indications
,
    r_dp.pk AS pk_drug_product
,
    r_dp.fk_data_source AS pk_data_source
,
    r_v.xmin AS xmin_vaccine
   
FROM (ref.vaccine r_v
     
LEFT JOIN ref.drug_product r_dp 
    ON (
           (r_v.fk_drug_product = r_dp.pk)
     )
);

Index - Schema ref

View: v_your_keyword_expansions

View over the text expansions relevant to the current user: a private expansion set up for the current user overrides a public expansion of the same keyword.

v_your_keyword_expansions Structure
F-Key Name Type Description
pk_expansion integer
pk_staff integer
keyword text
expansion text
is_encrypted boolean
is_textual boolean
data_size integer
public_expansion boolean
private_expansion boolean
owner text
xmin_expansion xid 
SELECT DISTINCT 
ON (keyword) pk_expansion
,
    pk_staff
,
    keyword
,
    expansion
,
    is_encrypted
,
    is_textual
,
    data_size
,
    public_expansion
,
    private_expansion
,
    owner
,
    xmin_expansion
   
FROM (
SELECT r_ke.pk AS pk_expansion
     ,
            r_ke.fk_staff AS pk_staff
     ,
            r_ke.keyword
     ,
            r_ke.textual_data AS expansion
     ,
            r_ke.encrypted AS is_encrypted
     ,
            
     (r_ke.binary_data IS NULL) AS is_textual
     ,
            octet_length
     (r_ke.binary_data) AS data_size
     ,
            false AS public_expansion
     ,
            true AS private_expansion
     ,
            r_ke.owner
     ,
            r_ke.xmin AS xmin_expansion
           
  FROM ref.keyword_expansion r_ke
          
 WHERE (r_ke.fk_staff = 
           (
            SELECT staff.pk
                   
              FROM dem.staff
                  
             WHERE (staff.db_user = "current_user"
                       ()
                 )
           )
     )
        
UNION ALL
         
SELECT r_ke.pk AS pk_expansion
     ,
            r_ke.fk_staff AS pk_staff
     ,
            r_ke.keyword
     ,
            r_ke.textual_data AS expansion
     ,
            r_ke.encrypted AS is_encrypted
     ,
            
     (r_ke.binary_data IS NULL) AS is_textual
     ,
            octet_length
     (r_ke.binary_data) AS data_size
     ,
            true AS public_expansion
     ,
            false AS private_expansion
     ,
            r_ke.owner
     ,
            r_ke.xmin AS xmin_expansion
           
  FROM ref.keyword_expansion r_ke
          
 WHERE (r_ke.fk_staff IS NULL)
  
ORDER BY 9 DESC
) union_result;

Index - Schema ref

Table: vacc_indication

List of target diseases which can be vaccinated against.

vacc_indication Structure
F-Key Name Type Description
pk serial PRIMARY KEY
target gm.nonempty_text NOT NULL

Name of the target disease/pathogen.
atc gm.nonempty_text

ATC for the target vaccine, if any. Single-target ATCs only.

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Table: vaccine

definition of a vaccine as available on the market

vaccine Structure
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
is_live boolean NOT NULL

whether this is a live vaccine
min_age interval

minimum age this vaccine is licensed for according to the information by the manufacturer
max_age interval

maximum age this vaccine is licensed for according to the information by the manufacturer, use "5555 years" to indicate "no maximum age"
comment text
drug_product.pk fk_drug_product integer

Link to a vaccine brand. If NULL this is a generic vaccine entry.
atc gm.nonempty_text

ATC for the vaccine, if any.

 

vaccine Constraints
Name Constraint
audit_audit_fields_sane_modified_when CHECK (((modified_when <= clock_timestamp()) IS TRUE))
vaccine_sane_max_age CHECK (((max_age IS NULL) OR (max_age < '150 years'::interval)))
vaccine_sane_min_age CHECK (((min_age IS NULL) OR (((max_age IS NULL) AND (min_age < '150 years'::interval)) OR ((max_age IS NOT NULL) AND (min_age <= max_age)))))

Tables referencing this one via Foreign Key Constraints:

Index - Schema ref

Function: trf_announce_substance_in_brand_mod_no_pk()

Returns: trigger

Language: PLPGSQL

begin
	execute 'notify "substance_in_brand_mod_db:"';
	return NULL;
end;

Function: trf_assert_product_has_components()

Returns: trigger

Language: PLPGSQL

DECLARE
	_msg text;
BEGIN
	PERFORM 1 FROM ref.lnk_dose2drug WHERE fk_drug_product = NEW.pk LIMIT 1;
	IF FOUND THEN
		RETURN NEW;
	END IF;
	_msg := '[ref.trf_assert_product_has_components()]: '
		|| TG_OP
		|| ' failed: no components (doses) linked to drug product ['
		|| NEW.pk
		|| '].'
	;
	RAISE EXCEPTION integrity_constraint_violation using message = _msg;
	RETURN NEW;
END;

Function: trf_assert_product_keeps_components()

Returns: trigger

Language: PLPGSQL

DECLARE
	_msg text;
BEGIN
	-- find components for given drug product
	PERFORM 1 FROM ref.lnk_dose2drug WHERE fk_drug_product = OLD.fk_drug_product LIMIT 1;
	IF FOUND THEN
		RETURN OLD;
	END IF;
	-- perhaps the drug product has been deleted, too ?
	PERFORM 1 FROM ref.drug_product WHERE pk = OLD.fk_drug_product LIMIT 1;
	IF NOT FOUND THEN
		RETURN OLD;
	END IF;
	_msg := '[ref.trf_assert_product_keeps_components()]: '
		|| TG_OP
		|| ' failed: no components (doses) linked to drug product ['
		|| OLD.fk_drug_product
		|| '] anymore.'
	;
	RAISE EXCEPTION integrity_constraint_violation using message = _msg;
	RETURN OLD;
END;

Function: trf_consumable_subst_normalize_amount()

Returns: trigger

Language: PLPGSQL

On INSERT/UPDATE drop .000 all-zero fractions from amounts.

BEGIN
	NEW.amount := gm.strip_allzeros_fraction(NEW.amount);
	return NEW;
END;

Function: trf_del_ref_code_tbl_check_backlink()

Returns: trigger

Language: PLPGSQL

When deleting from any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.

DECLARE
	_msg text;
BEGIN
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return OLD;
	end if;
	_msg := 'ref.trf_del_ref_code_tbl_check_backlink(): DELETE from '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;

Function: trf_do_not_update_component_if_taken_by_patient()

Returns: trigger

Language: PLPGSQL

If this drug component is taken by any patient do not modify it ( amount, unit, substance, brand).

BEGIN
	if OLD.fk_brand = NEW.fk_brand then
		if OLD.fk_substance = NEW.fk_substance then
			return NEW;
		end if;
	end if;
	perform 1 from clin.substance_intake c_si
	where c_si.fk_drug_component = OLD.pk
	limit 1;
	if NOT FOUND then
		return NEW;
	end if;
	raise exception '[ref.trf_do_not_update_component_if_taken_by_patient]: as long as drug component <%> is taken by a patient you cannot modify it', OLD.pk;
	return NEW;
END;

Function: trf_do_not_update_substance_if_taken_by_patient()

Returns: trigger

Language: PLPGSQL

If this substance is taken by any patient do not modify description, amount, or unit (case changes allowed).

DECLARE
	_msg text;
BEGIN
	-- allow for case insensitive non-changes
	if upper(OLD.description) = upper(NEW.description) then
		if OLD.amount = NEW.amount then
			if upper(OLD.unit) = upper(NEW.unit) then
				return NEW;
			end if;
		end if;
	end if;
	_msg := '[ref.trf_do_not_update_substance_if_taken_by_patient]: as long as substance <' || OLD.description || '> is taken by a patient you cannot modify it';
	perform 1 from clin.substance_intake c_si
	where c_si.fk_substance = OLD.pk
	limit 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	PERFORM 1
	FROM clin.substance_intake c_si
	WHERE c_si.fk_drug_component IN (
		-- get all PKs in component link table which
		-- represent the substance we want to modify
		SELECT
			r_ls2b.pk
		FROM
			ref.lnk_substance2brand r_ls2b
		WHERE
			r_ls2b.fk_substance = OLD.pk
	)
	LIMIT 1;
	if FOUND then
		raise exception '%', _msg;
	end if;
	return NEW;
END;

Function: trf_ins_upd_assert_dose_unit_across_drug_components()

Returns: trigger

Language: PLPGSQL

Assert that all substance doses linked into a multi-component drug carry the same <dose_unit>

DECLARE
	_dose_unit_count integer;
	_msg text;
BEGIN
	SELECT count(1) into strict _dose_unit_count
	FROM (
		SELECT dose_unit
		FROM ref.v_drug_components
		WHERE pk_drug_product = NEW.fk_drug_product
		GROUP BY dose_unit
	) AS dose_unit_count;
	if _dose_unit_count = 1 then
		return NEW;
	end if;
	_msg := '[ref.trf_ins_upd_assert_dose_unit_across_drug_components()]: cannot link substance dose [' || NEW.fk_dose || '] '
		|| 'to drug product [' || NEW.fk_drug_product || '] '
		|| 'because all components must have the same <dose_unit>';
	raise exception check_violation using message = _msg;
	return NEW;
END;

Function: trf_protect_template_data()

Returns: trigger

Language: PLPGSQL

Do not allow updates to the template data if any forms already use this template.

BEGIN
	if NEW.data != OLD.data then
		-- look for references in clin.form_instances
		-- if there are any we fail this update no matter what
		perform 1 from clin.form_instances where fk_form_def = NEW.pk;
		if FOUND then
			raise exception 'Updating ref.paperwork_templates.data not allowed because it is referenced from existing forms.';
		end if;
	end if;
	-- otherwise let it happen
	return NEW;
END;

Function: trf_true_brands_must_have_components()

Returns: trigger

Language: PLPGSQL

There must always be at least one component for any existing non-fake branded drug.

DECLARE
	_brand_is_deleted boolean;
	_is_fake_brand boolean;
	_has_other_components boolean;
BEGIN
	-- if an UPDATE does NOT move the component to another drug
	-- there WILL be at least one component left
	if TG_OP = 'UPDATE' then
		if NEW.fk_brand = OLD.fk_brand then
			return NEW;
		end if;
	end if;
	-- fake drugs may become devoid of components
	select
		is_fake into _is_fake_brand
	from
		ref.branded_drug
	where
		pk = OLD.fk_brand
	;
	if _is_fake_brand is TRUE then
		return OLD;
	end if;
	-- DELETEs may proceed if the drug has been deleted, too
	if TG_OP = 'DELETE' then
		select not exists (
			select 1 from ref.branded_drug
			where pk = OLD.fk_brand
		) into _brand_is_deleted;
		if _brand_is_deleted is TRUE then
			return OLD;
		end if;
	end if;
	-- if there are other components left after the
	-- UPDATE or DELETE everything is fine
	select exists (
		select 1 from ref.lnk_substance2brand
		where
			fk_brand = OLD.fk_brand
				and
			fk_substance != OLD.fk_substance
		limit 1
	) into _has_other_components;
	if _has_other_components is TRUE then
		return OLD;
	end if;
	raise exception '[ref.trf_true_brands_must_have_components::%] brand must have components (brand <%> component <%>)', TG_OP, OLD.fk_brand, OLD.fk_substance;
	return OLD;
END;

Function: trf_upd_assert_dose_unit_in_multi_component_drugs()

Returns: trigger

Language: PLPGSQL

Assert that after updates to ref.dose.dose_unit all substance doses linked into a multi-component drug still carry the same <dose_unit>.

DECLARE
	_pk_drug_product integer;
	_component_count integer;
	_dose_unit_count integer;
	_msg text;
BEGIN
	SELECT fk_drug_product into strict _pk_drug_product
	FROM ref.lnk_dose2drug
	WHERE fk_dose = NEW.pk;
	if FOUND IS FALSE then
		return NEW;
	end if;
	SELECT count(1) into strict _component_count
	FROM ref.lnk_dose2drug
	WHERE fk_drug_product = _pk_drug_product;
	if _component_count = 1 then
		return NEW;
	end if;
	SELECT count(1) into strict _dose_unit_count
	FROM (
		SELECT dose_unit
		FROM ref.v_drug_components
		WHERE pk_drug_product = _pk_drug_product
		GROUP BY dose_unit
	) AS dose_unit_count;
	if _dose_unit_count = 1 then
		return NEW;
	end if;
	_msg := '[ref.trf_upd_assert_dose_unit_in_multi_component_drugs()]: cannot change <dose_unit> on dose [' || NEW.pk || '] '
		|| 'from [' || coalesce(OLD.dose_unit, '<NULL>') || '] '
		|| 'to [' || coalesce(NEW.dose_unit, '<NULL>') || '] '
		|| 'because all doses linked to drug product [' || _pk_drug_product || '] '
		|| 'must have the same <dose_unit>';
	raise exception check_violation using message = _msg;
	return NEW;
END;

Function: trf_upd_ref_code_tbl_check_backlink()

Returns: trigger

Language: PLPGSQL

When updating any child of ref.coding_system_root check whether its row is being used in any clin.lnk_code2item_root child.

DECLARE
	_msg text;
BEGIN
	if NEW.pk_coding_system = OLD.pk_coding_system then
		return NEW;
	end if;
	perform 1 from clin.lnk_code2item_root where fk_generic_code = NEW.pk_coding_system;
	if not FOUND then
		return NEW;
	end if;
	_msg := 'ref.trf_upd_ref_code_tbl_check_backlink(): UPDATE of '
		|| TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || ': '
		|| 'pk_coding_system=(' || NEW.pk_coding_system || ') '
		|| 'in use in clin.lnk_code2item_root.fk_generic_code, '
		|| 'old pk_coding_system=(' || OLD.pk_coding_system || ')';
	raise foreign_key_violation using message = _msg;
	return OLD;
END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict