Dumped on 2024-11-22

Index of database - gnumed_v21

• 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
• ops
• other_code
• papersizes
• paperwork_templates
• tag_image
• v_atc
• v_auto_hints
• 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,

• idx_ref_atc_fk_data_src fk_data_source

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,

• idx_ref_billable_fk_data_src fk_data_source

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

• idx_drug_data_source fk_data_source
• idx_drug_description description
• idx_drug_ext_code external_code

Index - Schema ref

Table: coda

Holds CodA/CodZ codes.

Table coda Inherits coding_system_root,

• idx_ref_coda_fk_data_src fk_data_source

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

Index - Schema ref

Table: data_source

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

• idx_ref_data_source_lang lang

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

Index - Schema ref

Table: icd10

Holds ICD-10 codes.

Table icd10 Inherits coding_system_root,

• idx_ref_icd10_fk_data_src fk_data_source

Index - Schema ref

Table: icd9

Holds ICD-9 codes.

Table icd9 Inherits coding_system_root,

• idx_ref_icd9_fk_data_src fk_data_source

Index - Schema ref

Table: icpc

This table holds ICPC2 codes along with local extensions.

Table icpc Inherits coding_system_root,

• idx_ref_icpc_chapter fk_chapter
• idx_ref_icpc_code code
• idx_ref_icpc_component fk_component
• idx_ref_icpc_fk_data_src fk_data_source
• idx_ref_icpc_term term

Index - Schema ref

Table: icpc_chapter

The chapters of the ICPC.

Index - Schema ref

Table: icpc_component

The Components of the ICPC chapters.

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.

• idx_lnk_s2b_fk_brand fk_brand
• idx_lnk_s2b_fk_substance fk_substance

Index - Schema ref

Table: loinc

holds LOINC codes

Table loinc Inherits coding_system_root,

• idx_ref_loinc_fk_data_src fk_data_source

Index - Schema ref

Table: ops

Holds OPS (German ICPM-CM) codes.

Table ops Inherits coding_system_root,

• idx_ref_ops_fk_data_src fk_data_source

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,

• idx_ref_other_code_fk_data_src fk_data_source

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_auto_hints

SELECT auto_hint.pk AS pk_auto_hint
,
    auto_hint.query
,
    auto_hint.recommendation_query
,
    auto_hint.title
,
    auto_hint.hint
,
    auto_hint.url
,
    auto_hint.is_active
,
    auto_hint.source
,
    auto_hint.lang
,
    NULL::text AS rationale4suppression
,
    NULL::text AS recommendation
,
    md5
(
     (
           (
                 (
                       (COALESCE
                             (auto_hint.query
                                   ,''::text
                             ) || COALESCE
                             (auto_hint.recommendation_query
                                   ,''::text
                             )
                       ) || COALESCE
                       (auto_hint.title
                             ,''::text
                       )
                 ) || COALESCE
                 (auto_hint.hint
                       ,''::text
                 )
           ) || COALESCE
           (auto_hint.url
                 ,''::text
           )
     )
) AS md5_sum
,
    auto_hint.xmin AS xmin_auto_hint
   
FROM ref.auto_hint;

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

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
,
    r_pt.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
(r_pt.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
(_
     (r_pt.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
,
    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 d_s.short_alias
           
        FROM dem.staff d_s
          
       WHERE (d_s.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 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

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