Dumped on 2018-02-22

Index of database - gnumed_v20

• dem
• address
• address_type
• country
• enum_comm_types
• enum_ext_id_types
• gender_label
• identity
• identity_tag
• inbox_item_category
• inbox_item_type
• lnk_identity2comm
• lnk_identity2ext_id
• lnk_job2person
• lnk_org_unit2comm
• lnk_org_unit2ext_id
• lnk_person2address
• lnk_person2relative
• lnk_person_org_address
• marital_status
• message_inbox
• name_gender_map
• names
• occupation
• org
• org_category
• org_unit
• praxis_branch
• relation_types
• staff
• state
• street
• urb
• v_address
• v_basic_address
• v_basic_person
• v_external_ids4identity
• v_external_ids4org_unit
• v_gender_labels
• v_identity_tags
• v_inbox_item_type
• v_message_inbox
• v_org_unit_comms
• v_org_units
• v_org_units_no_praxis_check
• v_orgs
• v_pat_addresses
• v_person_comms
• v_person_jobs
• v_person_names
• v_persons
• v_praxis_branches
• v_staff
• v_state
• v_street
• v_uniq_zipped_urbs
• v_urb
• v_zip2data
• v_zip2street
• v_zip2urb
• add_external_id_type(text, text)
• add_name(integer, text, text, boolean)
• address_exists(text, text, text, text, text, text, text)
• create_address(text, text, text, text, text, text, text)
• create_comm_type(text)
• create_occupation(text)
• create_street(text, text, text, text, text)
• create_urb(text, text, text, text)
• date_trunc_utc(text, timestamp with time zone)
• dob_is_in_range(timestamp with time zone, interval, interval)
• f_always_active_name()
• f_delete_names()
• f_uniq_active_name()
• gm_upd_default_states()
• new_pupic()
• remove_person(integer)
• set_nickname(integer, text)
• trf_normalize_time_in_dob()
• trf_null_empty_title()
• trf_prevent_multiple_praxi()
• trf_protect_active_name_of_person()

Table: address

an address aka a location, void of attached meaning such as type of address

Tables referencing this one via Foreign Key Constraints:

• lnk_identity2comm

• lnk_person_org_address

• org_unit

Index - Schema dem

Table: address_type

Tables referencing this one via Foreign Key Constraints:

• lnk_person_org_address

Index - Schema dem

Table: country

countries coded per ISO 3166-1

Tables referencing this one via Foreign Key Constraints:

• state

Index - Schema dem

Table: enum_comm_types

Tables referencing this one via Foreign Key Constraints:

• lnk_identity2comm

• lnk_org_unit2comm

Index - Schema dem

Table: enum_ext_id_types

a list of all bureaucratic IDs/serial numbers/3rd party primary keys, etc.

Tables referencing this one via Foreign Key Constraints:

• lnk_identity2ext_id

• lnk_org_unit2ext_id

Index - Schema dem

Table: gender_label

This table stores the genders known to GNUmed. FIXME: cross-check with CDA:administrative-gender-code

Tables referencing this one via Foreign Key Constraints:

• identity

Index - Schema dem

Table: identity

represents the unique identity of a person

Tables referencing this one via Foreign Key Constraints:

• identity

• identity_tag

• lnk_identity2comm

• lnk_identity2ext_id

• lnk_job2person

• lnk_person2relative

• lnk_person_org_address

• message_inbox

• names

• staff

Index - Schema dem

Table: identity_tag

tags attached to this identity

Index - Schema dem

Table: inbox_item_category

Holds the various categories of messages that can show up in the provider inbox.

Tables referencing this one via Foreign Key Constraints:

• inbox_item_type

Index - Schema dem

Table: inbox_item_type

Holds the various types of messages that can show up in the provider inbox.

Tables referencing this one via Foreign Key Constraints:

• message_inbox

Index - Schema dem

Table: lnk_identity2comm

Index - Schema dem

Table: lnk_identity2ext_id

link external IDs to GnuMed identities

Index - Schema dem

Table: lnk_job2person

linking (possibly several) jobs to a person

Index - Schema dem

Table: lnk_org_unit2comm

Comm channels per org unit.

Index - Schema dem

Table: lnk_org_unit2ext_id

External IDs per org unit.

Index - Schema dem

View: lnk_person2address

SELECT lnk_person_org_address.id_identity
,
    lnk_person_org_address.id_address
,
    lnk_person_org_address.id_type
   
FROM dem.lnk_person_org_address;

Index - Schema dem

Table: lnk_person2relative

biological and social relationships between an identity and other identities

Index - Schema dem

Table: lnk_person_org_address

a many-to-many pivot table describing the relationship between an organisation, a person, their work address and their occupation at that location. For patients id_org is NULL

Index - Schema dem

Table: marital_status

Tables referencing this one via Foreign Key Constraints:

• identity

Index - Schema dem

Table: message_inbox

messages in GNUmed relating to a patient, a provider, and a context

Index - Schema dem

Table: name_gender_map

maps (first) names to their most frequently locally assigned gender, this table is updated nightly by a cron script, names whose gender distribution is between 70/30 and 30/70 are ignored for ambiguity reasons, names with "ambigous" gender are also ignored

Index - Schema dem

Table: names

all the names an identity is known under; As opposed to the versioning of all other tables, changed names should not be moved into the audit trail tables. Search functionality must be available at any time for all names a person ever had.

Index - Schema dem

Table: occupation

collects occupation names

Tables referencing this one via Foreign Key Constraints:

• lnk_job2person

Index - Schema dem

Table: org

Organizations at a conceptual level.

Tables referencing this one via Foreign Key Constraints:

• org_unit

Index - Schema dem

Table: org_category

Tables referencing this one via Foreign Key Constraints:

• org

• org_unit

Index - Schema dem

Table: org_unit

Actual branches/departments/offices/... of organizations.

Tables referencing this one via Foreign Key Constraints:

• lnk_org_unit2comm

• lnk_org_unit2ext_id

Index - Schema dem

Table: praxis_branch

Defines one branch of a praxis (which itself is a dem.org)

Index - Schema dem

Table: relation_types

types of biological/social relationships between identities

Tables referencing this one via Foreign Key Constraints:

• lnk_person2relative

• relation_types

Index - Schema dem

Table: staff

one-to-one mapping of database user accounts (db_user) to staff identities (fk_identity)

Tables referencing this one via Foreign Key Constraints:

• identity

• message_inbox

Index - Schema dem

Table: state

state codes (country specific); Richard agreed we should require pre-existence, allow user to mail details for adding a state to developers

Tables referencing this one via Foreign Key Constraints:

• urb

Index - Schema dem

Table: street

street names, specific for distinct "urbs"

Tables referencing this one via Foreign Key Constraints:

• address

Index - Schema dem

Table: urb

cities, towns, dwellings ..., eg. "official" places of residence

Tables referencing this one via Foreign Key Constraints:

• street

Index - Schema dem

View: v_address

fully denormalizes data about addresses as entities in themselves

SELECT adr.id AS pk_address
,
    str.name AS street
,
    COALESCE
(str.postcode
     , u.postcode
) AS postcode
,
    adr.aux_street AS notes_street
,
    adr.number
,
    adr.subunit
,
    adr.addendum AS notes_subunit
,
    adr.lat_lon AS lat_lon_address
,
    str.postcode AS postcode_street
,
    str.lat_lon AS lat_lon_street
,
    str.suburb
,
    u.name AS urb
,
    u.postcode AS postcode_urb
,
    u.lat_lon AS lat_lon_urb
,
    dst.code AS code_state
,
    dst.name AS state
,
    _
(dst.name) AS l10n_state
,
    dst.country AS code_country
,
    c.name AS country
,
    _
(c.name) AS l10n_country
,
    c.deprecated AS country_deprecated
,
    adr.id_street AS pk_street
,
    u.id AS pk_urb
,
    dst.id AS pk_state
,
    adr.xmin AS xmin_address
   
FROM (
     (
           (
                 (dem.address adr
     
               LEFT JOIN dem.street str 
                      ON (
                             (adr.id_street = str.id)
                       )
                 )
     
         LEFT JOIN dem.urb u 
                ON (
                       (str.id_urb = u.id)
                 )
           )
     
   LEFT JOIN dem.state dst 
          ON (
                 (u.id_state = dst.id)
           )
     )
     
LEFT JOIN dem.country c 
    ON (
           (c.code = dst.country)
     )
);

Index - Schema dem

View: v_basic_address

SELECT adr.id
,
    s.country AS country_code
,
    s.code AS state_code
,
    s.name AS state
,
    c.name AS country
,
    COALESCE
(str.postcode
     , urb.postcode
) AS postcode
,
    urb.name AS urb
,
    adr.number
,
    str.name AS street
,
    adr.addendum
,
    COALESCE
(adr.lat_lon
     , str.lat_lon
     , urb.lat_lon
) AS lat_lon
   
FROM dem.address adr
,
    dem.state s
,
    dem.country c
,
    dem.urb
,
    dem.street str
  
WHERE (
     (
           (
                 (s.country = c.code)
               AND (adr.id_street = str.id)
           )
         AND (str.id_urb = urb.id)
     )
   AND (urb.id_state = s.id)
);

Index - Schema dem

View: v_basic_person

This view denormalizes non-deleted persons with their active name.

SELECT i.pk AS pk_identity
,
    i.title
,
    n.firstnames
,
    n.preferred
,
    n.lastnames
,
    i.gender
,
    _
(i.gender) AS l10n_gender
,
    i.dob AS dob_only
,
    
(date_trunc
     ('day'::text
           , i.dob
     ) + 
     (COALESCE
           (i.tob
                 , (i.dob)::time without time zone
           )
     )::interval
) AS dob
,
    i.tob
,
    i.deceased
,
    i.cob
,
    i.pupic
,
        CASE
            WHEN 
(i.fk_marital_status IS NULL) THEN 'unknown'::text
            ELSE 
(
SELECT ms.name
               
  FROM dem.marital_status ms
     ,
                dem.identity i1
              
 WHERE (
           (ms.pk = i.fk_marital_status)
         AND (i1.pk = i.pk)
     )
)
        END AS marital_status
,
        CASE
            WHEN 
(i.fk_marital_status IS NULL) THEN _
('unknown'::text)
            ELSE 
(
SELECT _
     (ms1.name) AS _
               
  FROM dem.marital_status ms1
     ,
                dem.identity i1
              
 WHERE (
           (ms1.pk = i.fk_marital_status)
         AND (i1.pk = i.pk)
     )
)
        END AS l10n_marital_status
,
    i.emergency_contact
,
    i.comment
,
    i.fk_marital_status AS pk_marital_status
,
    n.id AS pk_active_name
,
    i.fk_emergency_contact AS pk_emergency_contact
,
    i.fk_primary_provider AS pk_primary_provider
,
    i.xmin AS xmin_identity
,
    i.dob_is_estimated
   
FROM dem.identity i
,
    dem.names n
  
WHERE (
     (
           (i.deleted IS FALSE)
         AND (n.active IS TRUE)
     )
   AND (n.id_identity = i.pk)
);

Index - Schema dem

View: v_external_ids4identity

SELECT li2ei.id_identity AS pk_identity
,
    li2ei.id AS pk_id
,
    eit.name
,
    li2ei.external_id AS value
,
    eit.issuer
,
    li2ei.comment
,
    li2ei.fk_origin AS pk_type
   
FROM dem.lnk_identity2ext_id li2ei
,
    dem.enum_ext_id_types eit
  
WHERE (li2ei.fk_origin = eit.pk);

Index - Schema dem

View: v_external_ids4org_unit

SELECT d_lou2ei.fk_org_unit AS pk_org_unit
,
    d_lou2ei.pk AS pk_id
,
    d_eit.name
,
    d_lou2ei.external_id AS value
,
    d_eit.issuer
,
    d_lou2ei.comment
,
    d_lou2ei.fk_type AS pk_type
   
FROM (dem.lnk_org_unit2ext_id d_lou2ei
     
  JOIN dem.enum_ext_id_types d_eit 
    ON (
           (d_lou2ei.fk_type = d_eit.pk)
     )
);

Index - Schema dem

View: v_gender_labels

SELECT gl.tag
,
    _
(gl.tag) AS l10n_tag
,
    gl.label
,
    _
(gl.label) AS l10n_label
,
    gl.comment
,
    gl.sort_weight
,
    gl.pk AS pk_gender_label
   
FROM dem.gender_label gl;

Index - Schema dem

View: v_identity_tags

SELECT dit.fk_identity AS pk_identity
,
    rti.description
,
    _
(rti.description) AS l10n_description
,
    dit.comment
,
    rti.filename
,
    octet_length
(COALESCE
     (rti.image
           ,'\x'::bytea
     )
) AS image_size
,
    dit.pk AS pk_identity_tag
,
    rti.pk AS pk_tag_image
,
    dit.xmin AS xmin_identity_tag
   
FROM (dem.identity_tag dit
     
LEFT JOIN ref.tag_image rti 
    ON (
           (dit.fk_tag = rti.pk)
     )
);

Index - Schema dem

View: v_inbox_item_type

SELECT it.description AS type
,
    _
(it.description) AS l10n_type
,
    ic.description AS category
,
    _
(ic.description) AS l10n_category
,
    it.is_user AS is_user_type
,
    ic.is_user AS is_user_category
,
    it.pk AS pk_type
,
    it.fk_inbox_item_category AS pk_category
   
FROM dem.inbox_item_type it
,
    dem.inbox_item_category ic
  
WHERE (it.fk_inbox_item_category = ic.pk);

Index - Schema dem

View: v_message_inbox

Denormalized messages for the providers and/or patients. Using UNION makes sure we get the right level of uniqueness.

(
         
SELECT mi.modified_when AS received_when
     ,
            COALESCE
     (
           (
            SELECT staff.short_alias
                   
              FROM dem.staff
                  
             WHERE (staff.db_user = mi.modified_by)
           )
           , (
                 ('<'::text || 
                       (mi.modified_by)::text
                 ) || '>'::text
           )
     ) AS modified_by
     ,
            
     (
      SELECT staff.short_alias
                   
        FROM dem.staff
                  
       WHERE (staff.pk = mi.fk_staff)
     ) AS provider
     ,
            mi.importance
     ,
            vit.category
     ,
            vit.l10n_category
     ,
            vit.type
     ,
            vit.l10n_type
     ,
            mi.comment
     ,
            mi.ufk_context AS pk_context
     ,
            mi.data
     ,
            mi.pk AS pk_inbox_message
     ,
            mi.fk_staff AS pk_staff
     ,
            vit.pk_category
     ,
            mi.fk_inbox_item_type AS pk_type
     ,
            mi.fk_patient AS pk_patient
     ,
            false AS is_virtual
     ,
            mi.due_date
     ,
            mi.expiry_date
     ,
                CASE
                    WHEN 
     (mi.due_date IS NULL) THEN false
                    WHEN 
     (mi.due_date > now
           ()
     ) THEN false
                    WHEN 
     (mi.expiry_date IS NULL) THEN true
                    WHEN 
     (mi.expiry_date < now
           ()
     ) THEN false
                    ELSE true
                END AS is_overdue
     ,
                CASE
                    WHEN 
     (mi.expiry_date IS NULL) THEN false
                    WHEN 
     (mi.expiry_date > now
           ()
     ) THEN false
                    ELSE true
                END AS is_expired
     ,
                CASE
                    WHEN 
     (mi.due_date IS NULL) THEN NULL::interval
                    WHEN 
     (mi.due_date > now
           ()
     ) THEN 
     (
           (mi.due_date)::timestamp with time zone - now
           ()
     )
                    ELSE 
     (now
           () - 
           (mi.due_date)::timestamp with time zone
     )
                END AS interval_due
     ,
            gm.xid2int
     (mi.xmin) AS xmin_message_inbox
           
  FROM dem.message_inbox mi
     ,
            dem.v_inbox_item_type vit
          
 WHERE (mi.fk_inbox_item_type = vit.pk_type)
        
UNION ALL
         
SELECT v_unreviewed_docs_inbox.received_when
     ,
            v_unreviewed_docs_inbox.modified_by
     ,
            v_unreviewed_docs_inbox.provider
     ,
            v_unreviewed_docs_inbox.importance
     ,
            v_unreviewed_docs_inbox.category
     ,
            v_unreviewed_docs_inbox.l10n_category
     ,
            v_unreviewed_docs_inbox.type
     ,
            v_unreviewed_docs_inbox.l10n_type
     ,
            v_unreviewed_docs_inbox.comment
     ,
            v_unreviewed_docs_inbox.pk_context
     ,
            v_unreviewed_docs_inbox.data
     ,
            v_unreviewed_docs_inbox.pk_inbox_message
     ,
            v_unreviewed_docs_inbox.pk_staff
     ,
            v_unreviewed_docs_inbox.pk_category
     ,
            v_unreviewed_docs_inbox.pk_type
     ,
            v_unreviewed_docs_inbox.pk_patient
     ,
            v_unreviewed_docs_inbox.is_virtual
     ,
            v_unreviewed_docs_inbox.due_date
     ,
            v_unreviewed_docs_inbox.expiry_date
     ,
            v_unreviewed_docs_inbox.is_overdue
     ,
            v_unreviewed_docs_inbox.is_expired
     ,
            v_unreviewed_docs_inbox.interval_due
     ,
            v_unreviewed_docs_inbox.xmin_message_inbox
           
  FROM blobs.v_unreviewed_docs_inbox

)
UNION
 
SELECT now
() AS received_when
,
    vtr.modified_by
,
    
(
SELECT staff.short_alias
           
  FROM dem.staff
          
 WHERE (staff.pk = vtr.pk_intended_reviewer)
) AS provider
,
    0 AS importance
,
    'clinical'::text AS category
,
    _
('clinical'::text) AS l10n_category
,
    'review results'::text AS type
,
    _
('review results'::text) AS l10n_type
,
    
(
SELECT (
           (
                 (
                       (
                             (_
                                   ('unreviewed (normal) results for patient'::text
                             ) || ' ['::text
                       ) || dn.lastnames
                 ) || 
              ', '::text
           ) || dn.firstnames
     ) || ']'::text
)
           
FROM dem.names dn
          
WHERE (
     (dn.id_identity = vtr.pk_patient)
   AND (dn.active IS TRUE)
)
)      AS comment
,     
    NULL::integer[] AS pk_context
,     
    NULL::text AS data
,     
    NULL::integer AS pk_inbox_message
,     
    vtr.pk_intended_reviewer AS pk_staff
,     
    
(     
SELECT v_inbox_item_type.pk_category
           
FROM dem.v_inbox_item_type
          
WHERE (v_inbox_item_type.type = 'review results'::text)
)      AS pk_category
,     
    
(     
SELECT v_inbox_item_type.pk_type
           
FROM dem.v_inbox_item_type
          
WHERE (v_inbox_item_type.type = 'review results'::text)
)      AS pk_type
,     
    vtr.pk_patient
,     
    true AS is_virtual
,     
    
(     now
() - '01:00:00'::interval
)      AS due_date
,     
    NULL::timestamp with time zone AS expiry_date
,     
    true AS is_overdue
,     
    false AS is_expired
,     
    '01:00:00'::interval AS interval_due
,     
    NULL::integer AS xmin_message_inbox
   
FROM   clin.v_test_results vtr
  
WHERE  (
(vtr.reviewed IS FALSE)
AND (
     (vtr.is_technically_abnormal IS FALSE)
    OR (
           (vtr.is_technically_abnormal IS NULL)
         AND (vtr.abnormality_indicator IS NULL)
     )
)
)     
UNION 
 
SELECT now
(     ) AS received_when
,     
    vtr.modified_by
,     
    
(     
SELECT staff.short_alias
           
FROM dem.staff
          
WHERE (staff.pk = vtr.pk_intended_reviewer)
)      AS provider
,     
    1 AS importance
,     
    'clinical'::text AS category
,     
    _
(     'clinical'::text) AS l10n_category
,     
    'review results'::text AS type
,     
    _
(     'review results'::text) AS l10n_type
,     
    
(     
SELECT (
     (
           (
                 (
                       (_
                             ('unreviewed (abnormal) results for patient'::text
                       ) || ' ['::text
                 ) || dn.lastnames
           ) || 
        ', '::text
     ) || dn.firstnames
) || ']'::text
)     
           
FROM   dem.names dn
          
WHERE  (
(dn.id_identity = vtr.pk_patient)
AND (dn.active IS TRUE)
)     
)            AS comment
,           
    NULL::integer[] AS pk_context
,           
    NULL::text AS data
,           
    NULL::integer AS pk_inbox_message
,           
    vtr.pk_intended_reviewer AS pk_staff
,           
    
(           
SELECT v_inbox_item_type.pk_category
           
FROM   dem.v_inbox_item_type
          
WHERE  (v_inbox_item_type.type = 'review results'::text)
)            AS pk_category
,           
    
(           
SELECT v_inbox_item_type.pk_type
           
FROM   dem.v_inbox_item_type
          
WHERE  (v_inbox_item_type.type = 'review results'::text)
)            AS pk_type
,           
    vtr.pk_patient
,           
    true AS is_virtual
,           
    
(           now
(     ) - '01:00:00'::interval
)            AS due_date
,           
    NULL::timestamp with time zone AS expiry_date
,           
    true AS is_overdue
,           
    false AS is_expired
,           
    '01:00:00'::interval AS interval_due
,           
    NULL::integer AS xmin_message_inbox
   
FROM         clin.v_test_results vtr
  
WHERE        (
(     vtr.reviewed IS FALSE)
AND    (
(vtr.is_technically_abnormal IS TRUE)
OR (
     (vtr.is_technically_abnormal IS NULL)
   AND (vtr.abnormality_indicator IS NOT NULL)
)
)     
)           ;

Index - Schema dem

View: v_org_unit_comms

denormalizes org units to communication channels

SELECT d_lo2c.fk_org_unit AS pk_org_unit
,
    ect.description AS comm_type
,
    _
(ect.description) AS l10n_comm_type
,
    d_lo2c.url
,
    d_lo2c.comment
,
    d_lo2c.is_confidential
,
    d_lo2c.pk AS pk_lnk_org_unit2comm
,
    d_lo2c.fk_type AS pk_type
,
    d_lo2c.xmin AS xmin_lnk_org_unit2comm
   
FROM (dem.lnk_org_unit2comm d_lo2c
     
  JOIN dem.enum_comm_types ect 
    ON (
           (d_lo2c.fk_type = ect.pk)
     )
);

Index - Schema dem

View: v_org_units

SELECT d_ou.pk AS pk_org_unit
,
    d_o.description AS organization
,
    d_ou.description AS unit
,
    d_oc_o.description AS organization_category
,
    _
(d_oc_o.description) AS l10n_organization_category
,
    d_oc_u.description AS unit_category
,
    _
(d_oc_u.description) AS l10n_unit_category
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.praxis_branch d_pb
          
       WHERE (d_pb.fk_org_unit = d_ou.pk)
     )
) AS is_praxis_branch
,
    d_o.pk AS pk_org
,
    d_o.fk_category AS pk_category_org
,
    d_ou.fk_category AS pk_category_unit
,
    d_ou.fk_address AS pk_address
,
    d_ou.xmin AS xmin_org_unit
   
FROM (
     (
           (dem.org_unit d_ou
     
              JOIN dem.org d_o 
                ON (
                       (d_o.pk = d_ou.fk_org)
                 )
           )
     
   LEFT JOIN dem.org_category d_oc_u 
          ON (
                 (d_ou.fk_category = d_oc_u.pk)
           )
     )
     
LEFT JOIN dem.org_category d_oc_o 
    ON (
           (d_o.fk_category = d_oc_o.pk)
     )
);

Index - Schema dem

View: v_org_units_no_praxis_check

SELECT d_ou.pk AS pk_org_unit
,
    d_o.description AS organization
,
    d_ou.description AS unit
,
    d_oc_o.description AS organization_category
,
    _
(d_oc_o.description) AS l10n_organization_category
,
    d_oc_u.description AS unit_category
,
    _
(d_oc_u.description) AS l10n_unit_category
,
    d_o.pk AS pk_org
,
    d_o.fk_category AS pk_category_org
,
    d_ou.fk_category AS pk_category_unit
,
    d_ou.fk_address AS pk_address
,
    d_ou.xmin AS xmin_org_unit
   
FROM (
     (
           (dem.org_unit d_ou
     
              JOIN dem.org d_o 
                ON (
                       (d_o.pk = d_ou.fk_org)
                 )
           )
     
   LEFT JOIN dem.org_category d_oc_u 
          ON (
                 (d_ou.fk_category = d_oc_u.pk)
           )
     )
     
LEFT JOIN dem.org_category d_oc_o 
    ON (
           (d_o.fk_category = d_oc_o.pk)
     )
);

Index - Schema dem

View: v_orgs

SELECT d_o.pk AS pk_org
,
    d_o.description AS organization
,
    d_oc.description AS category
,
    _
(d_oc.description) AS l10n_category
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.praxis_branch d_pb
          
       WHERE (d_pb.fk_org_unit IN 
                 (
                  SELECT d_ou.pk
                   
                    FROM dem.org_unit d_ou
                  
                   WHERE (d_ou.fk_org = d_o.pk)
                 )
           )
     )
) AS is_praxis
,
    d_o.fk_category AS pk_category_org
,
    d_o.xmin AS xmin_org
   
FROM (dem.org d_o
     
LEFT JOIN dem.org_category d_oc 
    ON (
           (d_o.fk_category = d_oc.pk)
     )
);

Index - Schema dem

View: v_pat_addresses

denormalized addresses per patient

SELECT vbp.pk_identity
,
    va.pk_address
,
    at.name AS address_type
,
    _
(at.name) AS l10n_address_type
,
    vbp.title
,
    vbp.firstnames
,
    vbp.lastnames
,
    vbp.dob
,
    vbp.cob
,
    vbp.gender
,
    vbp.l10n_gender
,
    vbp.preferred
,
    va.street
,
    va.postcode
,
    va.notes_street
,
    va.number
,
    va.subunit
,
    va.notes_subunit
,
    va.lat_lon_address
,
    va.postcode_street
,
    va.lat_lon_street
,
    va.suburb
,
    va.urb
,
    va.postcode_urb
,
    va.lat_lon_urb
,
    va.code_state
,
    va.state
,
    va.l10n_state
,
    va.code_country
,
    va.country
,
    va.l10n_country
,
    va.country_deprecated
,
    va.pk_street
,
    va.pk_urb
,
    va.pk_state
,
    lpoa.id AS pk_lnk_person_org_address
,
    lpoa.id_type AS pk_address_type
,
    lpoa.xmin AS xmin_lnk_person_org_address
   
FROM dem.v_address va
,
    dem.lnk_person_org_address lpoa
,
    dem.v_basic_person vbp
,
    dem.address_type at
  
WHERE (
     (
           (lpoa.id_identity = vbp.pk_identity)
         AND (lpoa.id_address = va.pk_address)
     )
   AND (lpoa.id_type = at.id)
);

Index - Schema dem

View: v_person_comms

denormalizes persons to communications channels

SELECT li2c.fk_identity AS pk_identity
,
    ect.description AS comm_type
,
    _
(ect.description) AS l10n_comm_type
,
    li2c.url
,
    li2c.is_confidential
,
    li2c.comment
,
    li2c.pk AS pk_lnk_identity2comm
,
    li2c.fk_address AS pk_address
,
    li2c.fk_type AS pk_type
,
    li2c.xmin AS xmin_lnk_identity2comm
   
FROM dem.lnk_identity2comm li2c
,
    dem.enum_comm_types ect
  
WHERE (li2c.fk_type = ect.pk);

Index - Schema dem

View: v_person_jobs

denormalizes the jobs a person has

SELECT lj2p.fk_identity AS pk_identity
,
    o.name AS occupation
,
    _
(o.name) AS l10n_occupation
,
    lj2p.activities
,
    lj2p.modified_when
,
    lj2p.fk_occupation AS pk_occupation
,
    lj2p.pk AS pk_lnk_job2person
,
    lj2p.xmin AS xmin_lnk_job2person
   
FROM (dem.lnk_job2person lj2p
     
  JOIN dem.occupation o 
    ON (
           (lj2p.fk_occupation = o.id)
     )
);

Index - Schema dem

View: v_person_names

SELECT dn.id_identity AS pk_identity
,
    dn.active AS active_name
,
    di.title
,
    dn.firstnames
,
    dn.lastnames
,
    dn.preferred
,
    dn.comment
,
    di.gender
,
    di.dob
,
    di.tob
,
    di.deceased AS dod
,
    di.deleted AS identity_deleted
,
    
(di.deceased IS NOT NULL) AS deceased
,
    dn.id AS pk_name
,
    dn.xmin AS xmin_name
   
FROM dem.names dn
,
    dem.identity di
  
WHERE (di.pk = dn.id_identity);

Index - Schema dem

View: v_persons

This view denormalizes persons with their active name.

SELECT i.pk AS pk_identity
,
    i.title
,
    n.firstnames
,
    n.preferred
,
    n.lastnames
,
    i.gender
,
    _
(i.gender) AS l10n_gender
,
    i.dob AS dob_only
,
    
(date_trunc
     ('day'::text
           , i.dob
     ) + 
     (COALESCE
           (i.tob
                 , (i.dob)::time without time zone
           )
     )::interval
) AS dob
,
    i.tob
,
    i.deceased
,
        CASE
            WHEN 
(i.fk_marital_status IS NULL) THEN 'unknown'::text
            ELSE 
(
SELECT ms.name
               
  FROM dem.marital_status ms
     ,
                dem.identity i1
              
 WHERE (
           (ms.pk = i.fk_marital_status)
         AND (i1.pk = i.pk)
     )
)
        END AS marital_status
,
        CASE
            WHEN 
(i.fk_marital_status IS NULL) THEN _
('unknown'::text)
            ELSE 
(
SELECT _
     (ms1.name) AS _
               
  FROM dem.marital_status ms1
     ,
                dem.identity i1
              
 WHERE (
           (ms1.pk = i.fk_marital_status)
         AND (i1.pk = i.pk)
     )
)
        END AS l10n_marital_status
,
    i.emergency_contact
,
    i.comment
,
    i.deleted AS is_deleted
,
    i.fk_marital_status AS pk_marital_status
,
    n.id AS pk_active_name
,
    i.fk_emergency_contact AS pk_emergency_contact
,
    i.fk_primary_provider AS pk_primary_provider
,
    i.xmin AS xmin_identity
,
    i.dob_is_estimated
   
FROM dem.identity i
,
    dem.names n
  
WHERE (
     (n.active IS TRUE)
   AND (n.id_identity = i.pk)
);

Index - Schema dem

View: v_praxis_branches

Denormalized praxis branches with their praxis.

SELECT branches_w_orgs.pk_praxis_branch
,
    branches_w_orgs.branch
,
    branches_w_orgs.pk_org_unit
,
    branches_w_orgs.pk_category_unit
,
    branches_w_orgs.pk_address
,
    branches_w_orgs.pk_org
,
    branches_w_orgs.xmin_praxis_branch
,
    branches_w_orgs.xmin_org_unit
,
    branches_w_orgs.praxis
,
    branches_w_orgs.pk_category_org
,
    d_ocat.description AS organization_category
,
    _
(d_ocat.description) AS l10n_organization_category
,
    d_ucat.description AS unit_category
,
    _
(d_ucat.description) AS l10n_unit_category
   
FROM (
     (
           (
            SELECT branches_w_units.pk_praxis_branch
                 ,
            branches_w_units.branch
                 ,
            branches_w_units.pk_org_unit
                 ,
            branches_w_units.pk_category_unit
                 ,
            branches_w_units.pk_address
                 ,
            branches_w_units.pk_org
                 ,
            branches_w_units.xmin_praxis_branch
                 ,
            branches_w_units.xmin_org_unit
                 ,
            d_o.description AS praxis
                 ,
            d_o.fk_category AS pk_category_org
           
              FROM (
                       (
                        SELECT d_pb.pk AS pk_praxis_branch
                             ,
                    d_ou.description AS branch
                             ,
                    d_pb.fk_org_unit AS pk_org_unit
                             ,
                    d_ou.fk_category AS pk_category_unit
                             ,
                    d_ou.fk_address AS pk_address
                             ,
                    d_ou.fk_org AS pk_org
                             ,
                    d_pb.xmin AS xmin_praxis_branch
                             ,
                    d_ou.xmin AS xmin_org_unit
                   
                          FROM (dem.praxis_branch d_pb
                     
                                JOIN dem.org_unit d_ou 
                                  ON (
                                         (d_pb.fk_org_unit = d_ou.pk)
                                   )
                             )
                       ) branches_w_units
             
                    JOIN dem.org d_o 
                      ON (
                             (d_o.pk = branches_w_units.pk_org)
                       )
                 )
           ) branches_w_orgs
     
   LEFT JOIN dem.org_category d_ucat 
          ON (
                 (branches_w_orgs.pk_category_unit = d_ucat.pk)
           )
     )
     
LEFT JOIN dem.org_category d_ocat 
    ON (
           (branches_w_orgs.pk_category_org = d_ocat.pk)
     )
);

Index - Schema dem

View: v_staff

Denormalized staff data.

SELECT vbp.pk_identity
,
    s.pk AS pk_staff
,
    vbp.title
,
    vbp.firstnames
,
    vbp.lastnames
,
    s.short_alias
,
        CASE
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-doctors'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'full clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-nurses'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'limited clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-staff'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'non-clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-public'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'public access'::text
            ELSE NULL::text
        END AS role
,
    vbp.dob
,
    vbp.gender
,
    s.db_user
,
    s.comment
,
    s.is_active
,
    
(
SELECT (
           (
            SELECT (EXISTS 
                       (
                        SELECT 1
                           
                          FROM pg_group
                          
                         WHERE (
                                   (
                                         (
                                          SELECT pg_user.usesysid
                                   
                                            FROM pg_user
                                  
                                           WHERE (pg_user.usename = s.db_user)
                                         ) = ANY 
                                         (pg_group.grolist)
                                   )
                                 AND (pg_group.groname = current_database
                                         ()
                                   )
                             )
                       )
                 ) AS "exists"
           )
         AND (
            SELECT (EXISTS 
                       (
                        SELECT 1
                           
                          FROM pg_group
                          
                         WHERE (
                                   (
                                         (
                                          SELECT pg_user.usesysid
                                   
                                            FROM pg_user
                                  
                                           WHERE (pg_user.usename = s.db_user)
                                         ) = ANY 
                                         (pg_group.grolist)
                                   )
                                 AND (pg_group.groname = 'gm-logins'::name)
                             )
                       )
                 ) AS "exists"
           )
     )
) AS can_login
,
    s.xmin AS xmin_staff
   
FROM (dem.staff s
     
  JOIN dem.v_basic_person vbp 
    ON (
           (s.fk_identity = vbp.pk_identity)
     )
);

Index - Schema dem

View: v_state

denormalizes state information

SELECT s.id AS pk_state
,
    s.code AS code_state
,
    s.name AS state
,
    _
(s.name) AS l10n_state
,
    s.country AS code_country
,
    c.name AS country
,
    _
(c.name) AS l10n_country
,
    c.deprecated AS country_deprecated
,
    s.xmin AS xmin_state
   
FROM (dem.state s
     
LEFT JOIN dem.country c 
    ON (
           (s.country = c.code)
     )
);

Index - Schema dem

View: v_street

denormalizes street data

SELECT st.id AS pk_street
,
    st.name AS street
,
    COALESCE
(st.postcode
     , vu.postcode_urb
) AS postcode
,
    st.postcode AS postcode_street
,
    st.lat_lon AS lat_lon_street
,
    st.suburb
,
    vu.urb
,
    vu.postcode_urb
,
    vu.lat_lon_urb
,
    vu.code_state
,
    vu.state
,
    vu.l10n_state
,
    vu.code_country
,
    vu.country
,
    vu.l10n_country
,
    vu.country_deprecated
,
    st.id_urb AS pk_urb
,
    vu.pk_state
,
    st.xmin AS xmin_street
   
FROM (dem.street st
     
LEFT JOIN dem.v_urb vu 
    ON (
           (st.id_urb = vu.pk_urb)
     )
);

Index - Schema dem

View: v_uniq_zipped_urbs

convenience view that selects urbs which: - have a zip code - are not referenced in table "street" with that zip code

SELECT urb.postcode
,
    urb.name
,
    stt.name AS state
,
    stt.code AS code_state
,
    c.name AS country
,
    _
(c.name) AS l10n_country
,
    stt.country AS code_country
   
FROM dem.urb
,
    dem.state stt
,
    dem.country c
  
WHERE (
     (
           (
                 (urb.postcode IS NOT NULL)
               AND (NOT 
                       (EXISTS 
                             (
                              SELECT 1
           
                                FROM dem.v_zip2street vz2str
                                   ,
            dem.urb urb_1
          
                               WHERE (
                                         (vz2str.postcode = urb_1.postcode)
                                       AND (vz2str.urb = urb_1.name)
                                   )
                             )
                       )
                 )
           )
         AND (urb.id_state = stt.id)
     )
   AND (stt.country = c.code)
);

Index - Schema dem

View: v_urb

denormalizes urb data

SELECT u.id AS pk_urb
,
    u.name AS urb
,
    u.postcode AS postcode_urb
,
    u.lat_lon AS lat_lon_urb
,
    vs.code_state
,
    vs.state
,
    vs.l10n_state
,
    vs.code_country
,
    vs.country
,
    vs.l10n_country
,
    vs.country_deprecated
,
    u.id_state AS pk_state
,
    u.xmin AS xmin_urb
   
FROM (dem.urb u
     
LEFT JOIN dem.v_state vs 
    ON (
           (vs.pk_state = u.id_state)
     )
);

Index - Schema dem

View: v_zip2data

aggregates nearly all known data per zip code

SELECT vz2s.postcode AS zip
,
    vz2s.street
,
    vz2s.suburb
,
    vz2s.urb
,
    vz2s.state
,
    vz2s.code_state
,
    vz2s.country
,
    vz2s.l10n_country
,
    vz2s.code_country
   
FROM dem.v_zip2street vz2s

UNION
 
SELECT vuzu.postcode AS zip
,
    NULL::text AS street
,
    NULL::text AS suburb
,
    vuzu.name AS urb
,
    vuzu.state
,
    vuzu.code_state
,
    vuzu.country
,
    vuzu.l10n_country
,
    vuzu.code_country
   
FROM dem.v_uniq_zipped_urbs vuzu;

Index - Schema dem

View: v_zip2street

list known data for streets that have a zip code

SELECT COALESCE
(str.postcode
     , urb.postcode
) AS postcode
,
    str.name AS street
,
    str.suburb
,
    stt.name AS state
,
    stt.code AS code_state
,
    urb.name AS urb
,
    c.name AS country
,
    _
(c.name) AS l10n_country
,
    stt.country AS code_country
   
FROM dem.street str
,
    dem.urb
,
    dem.state stt
,
    dem.country c
  
WHERE (
     (
           (
                 (str.postcode IS NOT NULL)
               AND (str.id_urb = urb.id)
           )
         AND (urb.id_state = stt.id)
     )
   AND (stt.country = c.code)
);

Index - Schema dem

View: v_zip2urb

list known data for urbs that have a zip code

SELECT urb.postcode
,
    urb.name AS urb
,
    stt.name AS state
,
    stt.code AS code_state
,
    _
(c.name) AS country
,
    stt.country AS code_country
   
FROM dem.urb
,
    dem.state stt
,
    dem.country c
  
WHERE (
     (
           (urb.postcode IS NOT NULL)
         AND (urb.id_state = stt.id)
     )
   AND (stt.country = c.code)
);

Index - Schema dem

Function: add_external_id_type(text, text)

Returns: integer

Language: PLPGSQL

Add an external ID type if it does not exist yet. This implementation is prone to concurrency issues.

declare
	_name alias for $1;
	_issuer alias for $2;
	_pk int;
begin
	select pk into _pk from dem.enum_ext_id_types where name = _name and issuer = _issuer;
	if FOUND then
		return _pk;
	end if;
	insert into dem.enum_ext_id_types(name, issuer) values (_name, _issuer);
	select currval(pg_get_serial_sequence('dem.enum_ext_id_types', 'pk')) into _pk;
	return _pk;
end;

Function: add_name(integer, text, text, boolean)

Returns: integer

Language: PLPGSQL

DECLARE
	_id_identity alias for $1;
	_first alias for $2;
	_last alias for $3;
	_active alias for $4;
	_id integer;
BEGIN
    -- deactivate all the existing names if this name is to become active
	if _active then
		update dem.names set active = false where id_identity = _id_identity;
	end if;
	-- name already there for this identity ?
	select into _id id from dem.names where id_identity = _id_identity and firstnames = _first and lastnames = _last;
	if FOUND then
		update dem.names set active = _active where id = _id;
		return _id;
	end if;
	-- no, insert new name
	insert into dem.names (id_identity, firstnames, lastnames, active) values (_id_identity, _first, _last, _active);
	if FOUND then
		return currval('dem.names_id_seq');
	end if;
	return NULL;
END;

Function: address_exists(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function checks whether a given address exists in the database and returns the primary key if found. It takes the following parameters: country code, state code, urb (location), postcode, street, number, subunit (can be NULL)

DECLARE
	_code_country alias for $1;
	_code_state alias for $2;
	_urb alias for $3;
	_postcode alias for $4;
	_street alias for $5;
	_number alias for $6;
	_subunit alias for $7;
	__subunit text;
	_pk_address integer;
	msg text;
BEGIN
	if (_code_country || _code_state || _urb || _postcode || _street || _number) is NULL then
		msg := '[dem.address_exists]: insufficient or invalid address definition: '
			|| 'country code <' || coalesce(_code_country, 'NULL') || '>, '
			|| 'state code <' || coalesce(_code_state, 'NULL') || '>, '
			|| 'urb <' || coalesce(_urb, 'NULL') || '>, '
			|| 'zip <' || coalesce(_postcode, 'NULL') || '>, '
			|| 'street <' || coalesce(_street, 'NULL') || '>, '
			|| 'number <' || coalesce(_number, 'NULL') || '>'
		;
		raise exception '%', msg;
	end if;
	__subunit := nullif(trim(_subunit), '');
	if __subunit is null then
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_state = trim(_code_state)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit is null;
	else
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_state = trim(_code_state)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit = __subunit;
	end if;
	return _pk_address;
END;

Function: create_address(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function creates an address. It first checks whether the address already exists. It takes the following parameters: number, street, postcode, urb (location), state code, country code, subunit (can be NULL) If the country or the state do not exist in the database, the function fails.

DECLARE
	_number ALIAS FOR $1;
	_street ALIAS FOR $2;
	_postcode ALIAS FOR $3;
	_urb ALIAS FOR $4;
	_state_code ALIAS FOR $5;
	_country_code ALIAS FOR $6;
	_subunit alias for $7;
	_street_id integer;
	_pk_address integer;
	__subunit text;
	msg text;
BEGIN
	select into _pk_address dem.address_exists (
		_country_code,
		_state_code,
		_urb,
		_postcode,
		_street,
		_number,
		_subunit
	);
	if _pk_address is not null then
		return _pk_address;
	end if;
	-- this either creates dem.street and possible dem.urb rows or
	-- or else it fails (because state and/or country do not exist)
	select into _street_id dem.create_street(_street, _postcode, _urb, _state_code, _country_code);
	-- create address
	__subunit := nullif(trim(_subunit), '');
	insert into dem.address (
		number,
		id_street,
		subunit
	) values (
		_number,
		_street_id,
		__subunit
	)
	returning id
	into _pk_address;
	return _pk_address;
END;

Function: create_comm_type(text)

Returns: integer

Language: PLPGSQL

DECLARE
	_description alias for $1;
	_pk_type integer;
BEGIN
	select pk into _pk_type from dem.enum_comm_types where _(description) = _description;
	if FOUND then
		return _pk_type;
	end if;
	select pk into _pk_type from dem.enum_comm_types where description = _description;
	if FOUND then
		return _pk_type;
	end if;
	insert into dem.enum_comm_types(description) values (_description);
	select currval(pg_get_serial_sequence('dem.enum_comm_types', 'pk')) into _pk_type;
	return _pk_type;
END;

Function: create_occupation(text)

Returns: integer

Language: PLPGSQL

DECLARE
	_job alias for $1;
	_id integer;
BEGIN
	select into _id id from dem.occupation where name = _job;
	if FOUND then
		return _id;
	end if;
	insert into dem.occupation (name) values (_job);
	return currval('dem.occupation_id_seq');
END;

Function: create_street(text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function takes a parameters the name of the street, the postal code, the name of the urb, the postcode of the urb, the code of the state and the code of the country. If the country or the state does not exists in the tables, the function fails. At first, both the urb and street are tried to be retrieved according to the supplied information. If the fields do not match exactly an existing row, a new urb is created or a new street is created and returned.

DECLARE
	_street ALIAS FOR $1;
	_postcode ALIAS FOR $2;
	_urb ALIAS FOR $3;
	_state_code ALIAS FOR $4;
	_country_code ALIAS FOR $5;
	_urb_id integer;
	_street_id integer;
	msg text;
BEGIN
	-- create/get urb
	SELECT INTO _urb_id dem.create_urb(_urb, _postcode, _state_code, _country_code);
	-- create/get and return street
	SELECT INTO _street_id s.id from dem.street s WHERE s.name ILIKE _street AND s.id_urb = _urb_id AND postcode ILIKE _postcode;
	IF FOUND THEN
		RETURN _street_id;
	END IF;
	INSERT INTO dem.street (name, postcode, id_urb) VALUES (_street, _postcode, _urb_id);
	RETURN currval('dem.street_id_seq');
END;

Function: create_urb(text, text, text, text)

Returns: integer

Language: PLPGSQL

This function takes a parameters the name of the urb, the postcode of the urb, the name of the state and the name of the country. If the country or the state does not exists in the tables, the function fails. At first, the urb is tried to be retrieved according to the supplied information. If the fields do not match exactly an existing row, a new urb is created and returned.

DECLARE
	_urb ALIAS FOR $1;
	_urb_postcode ALIAS FOR $2;	
	_state_code ALIAS FOR $3;
	_country_code ALIAS FOR $4;
 	_state_id integer;
	_urb_id integer;
	msg text;
BEGIN
 	-- get state
 	SELECT INTO _state_id s.id from dem.state s WHERE s.code = _state_code and s.country = _country_code;
 	IF NOT FOUND THEN
		msg := 'combination of state + country not registered ['
			||   'country:' || coalesce(_country_code, 'NULL')
			||   ', state:' || coalesce(_state_code, 'NULL')
			||     ', urb:' || coalesce(_urb, 'NULL')
			|| ', urb_zip:' || coalesce(_urb_postcode, 'NULL')
			|| ']';
		RAISE EXCEPTION '=> %', msg;
 	END IF;
	-- get/create and return urb
	SELECT INTO _urb_id u.id from dem.urb u WHERE u.name ILIKE _urb AND u.id_state = _state_id;
	IF FOUND THEN
		RETURN _urb_id;
	END IF;
	INSERT INTO dem.urb (name, postcode, id_state) VALUES (_urb, _urb_postcode, _state_id);
	RETURN currval('dem.urb_id_seq');
END;

Function: date_trunc_utc(text, timestamp with time zone)

Returns: timestamp without time zone

Language: SQL

date_trunc() is not immutable because it depends on the timezone setting, hence need to use this in index creation, but also need to use it in queries which want to use that index, so make it generally available as a function

select date_trunc($1, $2 at time zone 'UTC');

Function: dob_is_in_range(timestamp with time zone, interval, interval)

Returns: boolean

Language: SQL

select
	($1 - (extract(year from $1) * '1 year'::interval)) -
	(now() - (extract(year from now()) * '1 year'::interval))
	between (-1 * $2) and $3

Function: f_always_active_name()

Returns: trigger

Language: PLPGSQL

BEGIN
	if NEW.active = false then
		raise exception 'Cannot delete/disable active name. Another name must be activated first.';
		return OLD;
	end if;
	return NEW;
END;

Function: f_delete_names()

Returns: trigger

Language: PLPGSQL

DECLARE
BEGIN
	DELETE from dem.names WHERE id_identity=OLD.id;
	RETURN OLD;
END;

Function: f_uniq_active_name()

Returns: trigger

Language: PLPGSQL

DECLARE
--	tmp text;
BEGIN
--	tmp := 'identity:' || NEW.id_identity || ',id:' || NEW.id || ',name:' || NEW.firstnames || ' ' || NEW.lastnames;
--	raise notice 'uniq_active_name: [%]', tmp;
	if NEW.active = true then
		update dem.names set active = false
		where
			id_identity = NEW.id_identity
				and
			active = true;
	end if;
	return NEW;
END;

Function: gm_upd_default_states()

Returns: boolean

Language: PLPGSQL

declare
	_state_code text;
	_state_name text;
	_country_row record;
begin
	_state_code := '??';
	_state_name := 'state/territory/province/region not available';
	-- add default state to countries needing one
	for _country_row in
		select distinct code from dem.country
		where code not in (
			select country from dem.state where code = _state_code
		)
	loop
		raise notice 'adding default state for [%]', _country_row.code;
		execute 'insert into dem.state (code, country, name) values ('
				|| quote_literal(_state_code) || ', '
				|| quote_literal(_country_row.code) || ', '
				|| quote_literal(_state_name) || ');';
	end loop;
	return true;
end;

Function: new_pupic()

Returns: bpchar

Language: PLPGSQL

DECLARE
BEGIN
   -- how does this work? How do we get new 'unique' numbers?
   RETURN '0000000000';
END;

Function: remove_person(integer)

Returns: boolean

Language: PLPGSQL

Fully remove a person from the system - except everything is still in the audit tables ;-)

DECLARE
	_pk_identity alias for $1;
BEGIN
	-- does person exist ?
	perform 1 from dem.identity where pk = _pk_identity;
	if not FOUND then
		raise notice 'dem.remove_person(): dem.identity.pk=(%) does not exist, not removing', _pk_identity;
		return false;
	end if;
	-- we cannot just get the child tables of clin.clin_root_item and
	-- delete from them since they are inter-dependent and may require
	-- a particular order of deletion, so let us do that explicitely:
	DELETE FROM clin.clin_hx_family WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.vaccination WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy_state WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_diag WHERE fk_narrative IN (
		SELECT pk FROM clin.clin_narrative WHERE fk_encounter IN (
			select pk from clin.encounter where fk_patient = _pk_identity
		)
	);
	DELETE FROM clin.test_result WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.lab_request WHERE fk_encounter in (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.substance_intake WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.procedure WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_narrative WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM blobs.doc_med WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	-- now that we have deleted all the clinical data let us
	-- delete the EMR structural items as well:
	DELETE FROM clin.episode WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.health_issue WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.encounter WHERE fk_patient = _pk_identity;
	-- delete demographics details:
	DELETE FROM dem.identity_tag where fk_identity = _pk_identity;
	DELETE FROM dem.names WHERE id_identity = _pk_identity;
	-- eventually delete the identity itself which does
	-- not go down without some twisting of arms:
	ALTER TABLE dem.identity disable rule r_del_identity;
	DELETE FROM dem.identity WHERE pk = _pk_identity;
	ALTER TABLE dem.identity enable rule r_del_identity;
	return true;
END;

Function: set_nickname(integer, text)

Returns: integer

Language: PLPGSQL

Setting the nickname only makes sense for the currently active name. However, we also want to keep track of previous nicknames. Hence we would set the nickname right in the active name if it is NULL. It it contains a previous nickname (eg IS NOT NULL) we will inactivate the currently active name and copy it into a new active name but with the nickname set to the new one. Unsetting works the same (IOW *setting* to NULL).

DECLARE
	_id_identity alias for $1;
	_nick alias for $2;
	_names_row record;
	msg text;
BEGIN
	-- 0.1: Just always set the nickname inside the active name
	-- post 0.1: openEHR-like (name: pk, fk_identity, name, fk_type, comment, is_legal, is_active ...)
	-- does name exist ?
	select into _names_row * from dem.names where id_identity = _id_identity and active = true;
	if not found then
		msg := 'Cannot set nickname [' || _nick || ']. No active <names> row with id_identity [' || _id_identity || '] found.';
		raise exception '%', msg;
	end if;
	update dem.names set preferred = _nick where id = _names_row.id;
	return _names_row.id;
END;

Function: trf_normalize_time_in_dob()

Returns: trigger

Language: PLPGSQL

BEGIN
	if NEW.dob is NULL then
		return NEW;
	end if;
	NEW.dob = date_trunc('day', NEW.dob) + '11 hours 11 minutes 11 seconds 111 milliseconds'::interval;
	return NEW;
END;

Function: trf_null_empty_title()

Returns: trigger

Language: PLPGSQL

begin
	if (NEW.title is null) then
		return NEW;
	end if;
	if trim(NEW.title) <> '' then
		return NEW;
	end if;
	NEW.title := NULL;
	return NEW;
end;

Function: trf_prevent_multiple_praxi()

Returns: trigger

Language: PLPGSQL

Prevent praxis branches to be defined for more than one dem.org.

DECLARE
	_branch_count integer;
	_pk_org_existing integer;
	_pk_org_prospective integer;
	_msg text;
BEGIN
	select count(1) into _branch_count from dem.praxis_branch;
	if TG_OP = 'INSERT' then
		-- first branch ever
		if _branch_count = 0 then
			return NEW;
		end if;
	end if;
	if TG_OP = 'UPDATE' then
		-- only one branch (which is being updated)
		if _branch_count = 1 then
			return NEW;
		end if;
	end if;
	-- now we have either got an INSERT with at least
	-- one praxis_branch already existing or we are
	-- UPDATEing one of several existing branches
	SELECT fk_org INTO _pk_org_existing FROM dem.org_unit
	WHERE pk = (SELECT fk_org_unit FROM dem.praxis_branch LIMIT 1);
	SELECT fk_org INTO _pk_org_prospective
	FROM dem.org_unit WHERE pk = NEW.fk_org_unit;
	if _pk_org_prospective = _pk_org_existing then
		return NEW;
	end if;
	_msg := '[dem.trf_prevent_multiple_praxi] '
			|| TG_OP || ': '
			|| 'Existing praxis branches (dem.praxis_branch.fk_org->dem.org_unit.pk->dem.org_unit.fk_org) belong to an org with dem.org.pk='
			|| _pk_org_existing
			|| '. Cannot link praxis branch (dem.praxis_branch.fk_org=dem.org_unit.pk='
			|| NEW.fk_org_unit
			||') to a different org (dem.org.pk='
			|| _pk_org_prospective
			||'). There can only be one praxis (=dem.org) per database.';
	raise exception unique_violation using message = _msg;
	return NULL;
END;

Function: trf_protect_active_name_of_person()

Returns: trigger

Language: PLPGSQL

A person must have at least one, active, name record.

DECLARE
	_name_count integer;
	_msg text;
BEGIN
	-- how many names are there for the identity at the end of the Tx ?
	select count(1) into _name_count
	from dem.names
	where
		id_identity = OLD.id_identity
			and
		active is true
	;
	-- less than one name ?
	if _name_count < 1 then
		_msg := 'person ' || OLD.id_identity || ' must have at least one, active, name entry';
		raise exception check_violation using message = _msg;
		return OLD;
	end if;
	return OLD;
END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict