Dumped on 2015-04-16

Index of database - gnumed_v19

• ref
• atc
• atc_staging
• auto_hint
• billable
• branded_drug
• coda
• code_thesaurus_root
• coding_system_root
• consumable_substance
• data_source
• document_type
• form_types
• icd10
• icd9
• icpc
• icpc_chapter
• icpc_component
• icpc_thesaurus
• keyword_expansion
• lnk_substance2brand
• loinc
• loinc_staging
• ops
• other_code
• papersizes
• paperwork_templates
• tag_image
• v_atc
• v_billables
• v_branded_drugs
• v_coded_terms
• v_drug_components
• v_generic_codes
• v_icpc
• v_icpc_thesaurus
• v_keyword_expansions
• v_paperwork_templates
• v_tag_images_no_data
• v_your_keyword_expansions
• trf_announce_substance_in_brand_mod_no_pk()
• trf_consumable_subst_normalize_amount()
• trf_del_ref_code_tbl_check_backlink()
• trf_do_not_update_component_if_taken_by_patient()
• trf_do_not_update_substance_if_taken_by_patient()
• trf_protect_template_data()
• trf_true_brands_must_have_components()
• trf_upd_ref_code_tbl_check_backlink()

Table: atc

holds ATC data

Table atc Inherits coding_system_root,

Index - Schema ref

Table: atc_staging

used for importing ATC data

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.

Index - Schema ref

Table: billable

items that *can* be billed to patients

Table billable Inherits coding_system_root,

Index - Schema ref

Table: branded_drug

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).

Tables referencing this one via Foreign Key Constraints:

• lnk_substance2brand

Index - Schema ref

Table: coda

Holds CodA/CodZ codes.

Table coda Inherits coding_system_root,

Index - Schema ref

Table: code_thesaurus_root

Synonyms for coded terms.

Index - Schema ref

Table: coding_system_root

Base table for coding system tables providing common fields.

Index - Schema ref

Table: consumable_substance

lists substances that are consumable by patients, whether or not linked to a branded drug

Tables referencing this one via Foreign Key Constraints:

• lnk_substance2brand

Index - Schema ref

Table: data_source

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

Tables referencing this one via Foreign Key Constraints:

• atc

• billable

• branded_drug

• coda

• coding_system_root

• icd10

• icd9

• icpc

• loinc

• ops

• other_code

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

Index - Schema ref

Table: form_types

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

Tables referencing this one via Foreign Key Constraints:

• paperwork_templates

Index - Schema ref

Table: icd10

Holds ICD-10 codes.

Table icd10 Inherits coding_system_root,

Index - Schema ref

Table: icd9

Holds ICD-9 codes.

Table icd9 Inherits coding_system_root,

Index - Schema ref

Table: icpc

This table holds ICPC2 codes along with local extensions.

Table icpc Inherits coding_system_root,

Tables referencing this one via Foreign Key Constraints:

• icpc_thesaurus

Index - Schema ref

Table: icpc_chapter

The chapters of the ICPC.

Tables referencing this one via Foreign Key Constraints:

• icpc

Index - Schema ref

Table: icpc_component

The Components of the ICPC chapters.

Tables referencing this one via Foreign Key Constraints:

• icpc

Index - Schema ref

Table: icpc_thesaurus

Synonyms for ICPC terms.

Table icpc_thesaurus Inherits code_thesaurus_root,

Index - Schema ref

Table: keyword_expansion

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

Index - Schema ref

Table: lnk_substance2brand

This table links substances (INNs, mostly) as components into drugs.

Index - Schema ref

Table: loinc

holds LOINC codes

Table loinc Inherits coding_system_root,

Index - Schema ref

Table: loinc_staging

used for importing LOINC data, columns correspond 1:1 with the LOINC CSV file fields

Index - Schema ref

Table: ops

Holds OPS (German ICPM-CM) codes.

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.

Table other_code Inherits coding_system_root,

Index - Schema ref

Table: papersizes

Index - Schema ref

Table: paperwork_templates

form and letter template definitions

Index - Schema ref

Table: tag_image

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

Index - Schema ref

View: v_atc

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_billables

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_branded_drugs

SELECT r_bd.pk AS pk_brand
, r_bd.description AS brand
, r_bd.preparation
, r_bd.atc_code AS atc
, r_bd.external_code
, r_bd.external_code_type
, r_bd.is_fake AS is_fake_brand
, (
SELECT array_agg
     (
           (
                 (
                       (
                             (
                                   (
                                         (r_cs.description || '::'::text) || r_cs.amount
                                   ) || '::'::text
                             ) || r_cs.unit
                       ) || '::'::text
                 ) || COALESCE
                 (r_cs.atc_code
                       ,''::text
                 )
           )
     ) AS array_agg 
  FROM (ref.lnk_substance2brand r_ls2b 
        JOIN ref.consumable_substance r_cs 
          ON (
                 (r_ls2b.fk_substance = r_cs.pk)
           )
     )
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS components
, (
SELECT array_agg
     (r_ls2b.pk) AS array_agg 
  FROM ref.lnk_substance2brand r_ls2b 
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS pk_components
, (
SELECT array_agg
     (r_ls2b.fk_substance) AS array_agg 
  FROM ref.lnk_substance2brand r_ls2b 
 WHERE (r_ls2b.fk_brand = r_bd.pk)
) AS pk_substances
, r_bd.fk_data_source AS pk_data_source
, r_bd.xmin AS xmin_branded_drug 
FROM ref.branded_drug r_bd;

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.

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)
     )
)
UNIONSELECT 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

SELECT r_ls2b.pk AS pk_component
, r_bd.description AS brand
, r_cs.description AS substance
, r_cs.amount
, r_cs.unit
, r_bd.preparation
, r_cs.atc_code AS atc_substance
, r_bd.atc_code AS atc_brand
, r_bd.external_code AS external_code_brand
, r_bd.external_code_type AS external_code_type_brand
, r_bd.is_fake AS is_fake_brand
, (EXISTS 
     (
      SELECT 1 
        FROM clin.substance_intake c_si 
       WHERE (c_si.fk_drug_component = r_ls2b.pk) LIMIT 1
     )
) AS is_in_use
, r_ls2b.fk_brand AS pk_brand
, r_cs.pk AS pk_consumable_substance
, r_bd.fk_data_source AS pk_data_source
, r_ls2b.xmin AS xmin_lnk_substance2brand 
FROM (
     (ref.consumable_substance r_cs 
        JOIN ref.lnk_substance2brand r_ls2b 
          ON (
                 (r_cs.pk = r_ls2b.fk_substance)
           )
     )
LEFT JOIN ref.branded_drug r_bd 
    ON (
           (r_ls2b.fk_brand = r_bd.pk)
     )
);

Index - Schema ref

View: v_generic_codes

Denormalized generic codes.

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.

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.

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_keyword_expansions

Just a slightly more convenient view over expansions.

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
, (r_ke.fk_staff IS NULL) AS public_expansion
, (r_ke.fk_staff IS NOT NULL) AS private_expansion
, r_ke.owner
, r_ke.xmin AS xmin_expansion 
FROM ref.keyword_expansion r_ke;

Index - Schema ref

View: v_paperwork_templates

SELECT r_pt.pk AS pk_paperwork_template
, r_pt.name_short
, r_pt.name_long
, r_pt.external_version
, (
SELECT form_types.name 
  FROM ref.form_types 
 WHERE (form_types.pk = r_pt.fk_template_type)
) AS template_type
, (
SELECT _
     (form_types.name) AS _ 
  FROM ref.form_types 
 WHERE (form_types.pk = r_pt.fk_template_type)
) AS l10n_template_type
, COALESCE
(r_pt.instance_type
     , (
      SELECT form_types.name 
        FROM ref.form_types 
       WHERE (form_types.pk = r_pt.fk_template_type)
     )
) AS instance_type
, COALESCE
(_
     (r_pt.instance_type)
     , (
      SELECT _
           (form_types.name) AS _ 
        FROM ref.form_types 
       WHERE (form_types.pk = r_pt.fk_template_type)
     )
) AS l10n_instance_type
, r_pt.engine
, r_pt.in_use
, r_pt.edit_after_substitution
, r_pt.filename
, CASE WHEN 
(r_pt.data IS NOT NULL) THEN true ELSE false END AS has_template_data
, r_pt.modified_when AS last_modified
, COALESCE
(
     (
      SELECT staff.short_alias 
        FROM dem.staff 
       WHERE (staff.db_user = r_pt.modified_by)
     )
     , (
           ('<'::text || 
                 (r_pt.modified_by)::text
           ) || '>'::text
     )
) AS modified_by
, r_pt.fk_template_type AS pk_template_type
, r_pt.xmin AS xmin_paperwork_template 
FROM ref.paperwork_templates r_pt;

Index - Schema ref

View: v_tag_images_no_data

SELECT rti.pk AS pk_tag_image
, rti.description
, _
(rti.description) AS l10n_description
, rti.filename
, octet_length
(COALESCE
     (rti.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
, rti.xmin AS xmin_tag_image 
FROM ref.tag_image rti;

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.

SELECT DISTINCT 
ON (union_result.keyword) union_result.pk_expansion
, union_result.pk_staff
, union_result.keyword
, union_result.expansion
, union_result.is_encrypted
, union_result.is_textual
, union_result.data_size
, union_result.public_expansion
, union_result.private_expansion
, union_result.owner
, union_result.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 ALLSELECT 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

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_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_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_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