Dumped on 2024-12-23

Index of database - gnumed_v21

• blobs
• doc_desc
• doc_med
• doc_obj
• doc_type
• lnk_doc2hospital_stay
• lnk_doc_med2episode
• reviewed_doc_objs
• v_doc_desc
• v_doc_med
• v_doc_med_journal
• v_doc_type
• v_latest_mugshot
• v_obj4doc_no_data
• v_reviewed_doc_objects
• v_unreviewed_docs
• v_unreviewed_docs_inbox
• v_unreviewed_docs_summary
• delete_document(integer, integer)
• delete_document_part(integer, integer)
• trf_do_not_duplicate_primary_episode_in_link_table()
• trf_mark_unreviewed_on_doc_obj_update()
• trf_remove_primary_episode_from_link_table()
• trf_set_intended_reviewer()
• trf_verify_page_number()

Table: doc_desc

A textual description of the content such as a result summary. Several of these may belong to one document object.

• idx_doc_desc_fk_doc fk_doc

Index - Schema blobs

Table: doc_med

a medical document object possibly containing several data objects such as several pages of a paper document

Index - Schema blobs

Table: doc_obj

possibly several of these form a medical document such as multiple scanned pages/images

Index - Schema blobs

Table: doc_type

this table enumerates the document types known to the system

Index - Schema blobs

Table: lnk_doc2hospital_stay

links documents to any hospital stay they might pertain to

Index - Schema blobs

Table: lnk_doc_med2episode

this allows linking documents to episodes, each document can apply to several episodes but only once each

Index - Schema blobs

Table: reviewed_doc_objs

review table for documents (per object such as a page)

Index - Schema blobs

View: v_doc_desc

aggregates some document data descriptions

SELECT vdm.pk_patient
,
    dd.fk_doc AS pk_doc
,
    dd.text AS description
,
    vdm.pk_encounter
,
    vdm.pk_episode
,
    vdm.pk_health_issue
,
    dd.pk AS pk_doc_desc
   
FROM blobs.doc_desc dd
,
    blobs.v_doc_med vdm
  
WHERE (dd.fk_doc = vdm.pk_doc);

Index - Schema blobs

View: v_doc_med

SELECT c_enc.fk_patient AS pk_patient
,
    b_dm.pk AS pk_doc
,
    b_dm.clin_when
,
    b_dt.name AS type
,
    _
(b_dt.name) AS l10n_type
,
    b_dm.ext_ref
,
    b_dm.comment
,
    c_epi.description AS episode
,
    c_hi.description AS health_issue
,
    c_epi.is_open AS episode_open
,
    d_ou.description AS unit
,
    d_o.description AS organization
,
    b_dm.fk_type AS pk_type
,
    b_dm.fk_encounter AS pk_encounter
,
    b_dm.fk_episode AS pk_episode
,
    c_epi.fk_health_issue AS pk_health_issue
,
    b_dm.fk_org_unit AS pk_org_unit
,
    d_ou.fk_org AS pk_org
,
    b_dm.modified_when
,
    b_dm.modified_by
,
    b_dm.xmin AS xmin_doc_med
   
FROM (
     (
           (
                 (
                       (
                             (blobs.doc_med b_dm
     
                                JOIN blobs.doc_type b_dt 
                                  ON (
                                         (b_dm.fk_type = b_dt.pk)
                                   )
                             )
     
                          JOIN clin.encounter c_enc 
                            ON (
                                   (b_dm.fk_encounter = c_enc.pk)
                             )
                       )
     
                    JOIN clin.episode c_epi 
                      ON (
                             (b_dm.fk_episode = c_epi.pk)
                       )
                 )
     
         LEFT JOIN clin.health_issue c_hi 
                ON (
                       (c_hi.pk = c_epi.fk_health_issue)
                 )
           )
     
   LEFT JOIN dem.org_unit d_ou 
          ON (
                 (b_dm.fk_org_unit = d_ou.pk)
           )
     )
     
LEFT JOIN dem.org d_o 
    ON (
           (d_ou.fk_org = d_o.pk)
     )
);

Index - Schema blobs

View: v_doc_med_journal

SELECT c_enc.fk_patient AS pk_patient
,
    b_dm.modified_when
,
    b_dm.clin_when
,
    COALESCE
(
     (
      SELECT staff.short_alias
           
        FROM dem.staff
          
       WHERE (staff.db_user = b_dm.modified_by)
     )
     , (
           ('<'::text || 
                 (b_dm.modified_by)::text
           ) || '>'::text
     )
) AS modified_by
,
    NULL::text AS soap_cat
,
    
('"'::text || 
     (
           (
                 (
                       (
                             (
                                   (
                                         (
                                               (
                                                     (
                                                           (
                                                                 (
                                                                       (
                                                                             (
                                                                                   (_
                                                                                         (b_dt.name) || '" '::text
                                                                                   ) || _
                                                                                   ('with'::text)
                                                                             ) || ' '::text
                                                                       ) || 
                                                                       (
                                                                        SELECT count
                                                                             (1) AS count
           
                                                                          FROM blobs.doc_obj b_do
          
                                                                         WHERE (b_do.fk_doc = b_dm.pk)
                                                                       )
                                                                 ) || ' '::text
                                                           ) || _
                                                           ('part(s)'::text
                                                     )
                                               ) || '
'::text
                                         ) || ' '::text
                                   ) || to_char
                                   (b_dm.clin_when
                                         ,'YYYY-MM-DD HH24:MI'::text
                                   )
                             ) || '
'::text
                       ) || COALESCE
                       (
                             (
                                   (' ['::text || b_dm.ext_ref) || ']'::text
                             )
                             ,''::text
                       )
                 ) || COALESCE
                 (
                       (
                             (
                                   (
                                         (
                                               (' @ '::text || d_ou.description) || ' '::text
                                         ) || _
                                         ('of'::text)
                                   ) || ' '::text
                             ) || d_o.description
                       )
                       ,''::text
                 )
           ) || '
'::text
     ) || COALESCE
     (
           (' '::text || b_dm.comment)
           ,''::text
     )
)
)      AS narrative
,     
    b_dm.fk_encounter AS pk_encounter
,     
    b_dm.fk_episode AS pk_episode
,     
    c_epi.fk_health_issue AS pk_health_issue
,     
    b_dm.pk AS src_pk
,     
    'blobs.doc_med'::text AS src_table
,     
    b_dm.row_version
,     
    c_hi.description AS health_issue
,     
    c_hi.laterality AS issue_laterality
,     
    c_hi.is_active AS issue_active
,     
    c_hi.clinically_relevant AS issue_clinically_relevant
,     
    c_hi.is_confidential AS issue_confidential
,     
    c_epi.description AS episode
,     
    c_epi.is_open AS episode_open
,     
    c_enc.started AS encounter_started
,     
    c_enc.last_affirmed AS encounter_last_affirmed
,     
    c_ety.description AS encounter_type
,     
    _
(     c_ety.description) AS encounter_l10n_type
   
FROM   (
(
     (
           (
                 (
                       (
                             (blobs.doc_med b_dm
     
                                JOIN clin.encounter c_enc 
                                  ON (
                                         (b_dm.fk_encounter = c_enc.pk)
                                   )
                             )
     
                          JOIN clin.encounter_type c_ety 
                            ON (
                                   (c_enc.fk_type = c_ety.pk)
                             )
                       )
     
                    JOIN blobs.doc_type b_dt 
                      ON (
                             (b_dm.fk_type = b_dt.pk)
                       )
                 )
     
              JOIN clin.episode c_epi 
                ON (
                       (b_dm.fk_episode = c_epi.pk)
                 )
           )
     
   LEFT JOIN clin.health_issue c_hi 
          ON (
                 (c_epi.fk_health_issue = c_hi.pk)
           )
     )
     
LEFT JOIN dem.org_unit d_ou 
    ON (
           (b_dm.fk_org_unit = d_ou.pk)
     )
)
     
LEFT JOIN dem.org d_o 
ON (
     (d_ou.fk_org = d_o.pk)
)
)     ;

Index - Schema blobs

View: v_doc_type

list active document types, those that are activated for use

SELECT dt.pk AS pk_doc_type
,
    dt.name AS type
,
    _
(dt.name) AS l10n_type
,
    
(NOT 
     (EXISTS 
           (
            SELECT 1
           
              FROM ref.document_type
          
             WHERE (document_type.description = dt.name)
           )
     )
) AS is_user_defined
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.doc_med
          
       WHERE (doc_med.fk_type = dt.pk)
     )
) AS is_in_use
,
    dt.xmin AS xmin_doc_type
   
FROM blobs.doc_type dt;

Index - Schema blobs

View: v_latest_mugshot

shows the latest picture of the patient, currently the highest seq_idx of the newest document of type "patient photograph"

SELECT vo4d.pk_patient
,
    vo4d.doc_comment
,
    vo4d.date_generated AS date_taken
,
    vo4d.ext_ref
,
    vo4d.seq_idx AS obj_seq_idx
,
    vo4d.obj_comment
,
    vo4d.pk_doc
,
    vo4d.pk_obj
,
    bdo.data AS image
   
FROM blobs.v_obj4doc_no_data vo4d
,
    blobs.doc_obj bdo
  
WHERE (
     (vo4d.type = 'patient photograph'::text)
   AND (vo4d.seq_idx = 
           (
            SELECT max
                 (vo4d1.seq_idx) AS max
           
              FROM blobs.v_obj4doc_no_data vo4d1
          
             WHERE (
                       (vo4d1.pk_patient = vo4d.pk_patient)
                     AND (vo4d1.type = 'patient photograph'::text)
                 )
          
          GROUP BY vo4d1.date_generated
          
          ORDER BY vo4d1.date_generated DESC
         LIMIT 1
           )
     )
   AND (bdo.pk = vo4d.pk_obj)
);

Index - Schema blobs

View: v_obj4doc_no_data

denormalized metadata for blobs.doc_obj but without the data itself

SELECT b_vdm.pk_patient
,
    b_do.pk AS pk_obj
,
    b_do.seq_idx
,
    octet_length
(COALESCE
     (b_do.data
           ,'\x'::bytea
     )
) AS size
,
    b_vdm.clin_when AS date_generated
,
    b_vdm.type
,
    b_vdm.l10n_type
,
    b_vdm.ext_ref
,
    b_vdm.episode
,
    b_vdm.comment AS doc_comment
,
    b_do.comment AS obj_comment
,
    b_do.filename
,
    b_do.fk_intended_reviewer AS pk_intended_reviewer
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.reviewed_doc_objs
          
       WHERE (reviewed_doc_objs.fk_reviewed_row = b_do.pk)
     )
) AS reviewed
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.reviewed_doc_objs
          
       WHERE (
                 (reviewed_doc_objs.fk_reviewed_row = b_do.pk)
               AND (reviewed_doc_objs.fk_reviewer = 
                       (
                        SELECT staff.pk
                   
                          FROM dem.staff
                  
                         WHERE (staff.db_user = "current_user"
                                   ()
                             )
                       )
                 )
           )
     )
) AS reviewed_by_you
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.reviewed_doc_objs
          
       WHERE (
                 (reviewed_doc_objs.fk_reviewed_row = b_do.pk)
               AND (reviewed_doc_objs.fk_reviewer = b_do.fk_intended_reviewer)
           )
     )
) AS reviewed_by_intended_reviewer
,
    b_vdm.pk_doc
,
    b_vdm.pk_type
,
    b_vdm.pk_encounter
,
    b_vdm.pk_episode
,
    b_vdm.pk_health_issue
,
    b_do.xmin AS xmin_doc_obj
   
FROM (blobs.v_doc_med b_vdm
     
  JOIN blobs.doc_obj b_do 
    ON (
           (b_do.fk_doc = b_vdm.pk_doc)
     )
)
  
WHERE (b_vdm.pk_doc = b_do.fk_doc);

Index - Schema blobs

View: v_reviewed_doc_objects

SELECT rdo.fk_reviewed_row AS pk_doc_obj
,
    COALESCE
(
     (
      SELECT staff.short_alias
           
        FROM dem.staff
          
       WHERE (staff.pk = rdo.fk_reviewer)
     )
     , (
           ('<#'::text || rdo.fk_reviewer) || '>'::text
     )
) AS reviewer
,
    rdo.is_technically_abnormal
,
    rdo.clinically_relevant
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.doc_obj
          
       WHERE (
                 (doc_obj.pk = rdo.fk_reviewed_row)
               AND (doc_obj.fk_intended_reviewer = rdo.fk_reviewer)
           )
     )
) AS is_review_by_responsible_reviewer
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.staff
          
       WHERE (
                 (staff.pk = rdo.fk_reviewer)
               AND (staff.db_user = "current_user"
                       ()
                 )
           )
     )
) AS is_your_review
,
    rdo.comment
,
    rdo.modified_when AS reviewed_when
,
    rdo.modified_by
,
    rdo.pk AS pk_review_root
,
    rdo.fk_reviewer AS pk_reviewer
,
    
(
SELECT v_obj4doc_no_data.pk_patient
           
  FROM blobs.v_obj4doc_no_data
          
 WHERE (v_obj4doc_no_data.pk_obj = rdo.fk_reviewed_row)
) AS pk_patient
,
    
(
SELECT v_obj4doc_no_data.pk_encounter
           
  FROM blobs.v_obj4doc_no_data
          
 WHERE (v_obj4doc_no_data.pk_obj = rdo.fk_reviewed_row)
) AS pk_encounter
,
    
(
SELECT v_obj4doc_no_data.pk_episode
           
  FROM blobs.v_obj4doc_no_data
          
 WHERE (v_obj4doc_no_data.pk_obj = rdo.fk_reviewed_row)
) AS pk_episode
,
    
(
SELECT v_obj4doc_no_data.pk_health_issue
           
  FROM blobs.v_obj4doc_no_data
          
 WHERE (v_obj4doc_no_data.pk_obj = rdo.fk_reviewed_row)
) AS pk_health_issue
   
FROM blobs.reviewed_doc_objs rdo;

Index - Schema blobs

View: v_unreviewed_docs

SELECT DISTINCT 
ON (b_do.fk_doc) b_do.fk_doc AS pk_doc
,
    min
(b_do.fk_intended_reviewer) AS pk_intended_reviewer
,
    min
(c_e.fk_patient) AS pk_patient
   
FROM (
     (blobs.doc_obj b_do
     
        JOIN blobs.doc_med b_dm 
          ON (
                 (b_dm.pk = b_do.fk_doc)
           )
     )
     
  JOIN clin.encounter c_e 
    ON (
           (b_dm.fk_encounter = c_e.pk)
     )
)
  
WHERE (NOT 
     (EXISTS 
           (
            SELECT 1
           
              FROM blobs.reviewed_doc_objs b_rdo
          
             WHERE (b_rdo.fk_reviewed_row = b_do.pk)
           )
     )
)
  
GROUP BY b_do.fk_doc;

Index - Schema blobs

View: v_unreviewed_docs_inbox

SELECT now
() AS received_when
,
    '<system>'::text AS modified_by
,
    NULL::text AS provider
,
    0 AS importance
,
    'clinical'::text AS category
,
    _
('clinical'::text) AS l10n_category
,
    'review docs'::text AS type
,
    _
('review docs'::text) AS l10n_type
,
    
(
SELECT (
           (
                 (
                       (
                             (
                                   (b_vuds.no_of_docs || ' '::text) || _
                                   ('unreviewed documents for patient'::text)
                             ) || ' '::text
                       ) || d_n.lastnames
                 ) || 
              ', '::text
           ) || d_n.firstnames
     )
) AS comment
,
    NULL::integer[] AS pk_context
,
    NULL::text AS data
,
    NULL::integer AS pk_inbox_message
,
    
(
SELECT staff.pk
           
  FROM dem.staff
          
 WHERE (staff.db_user = "current_user"
           ()
     )
) AS pk_staff
,
    
(
SELECT v_inbox_item_type.pk_category
           
  FROM dem.v_inbox_item_type
          
 WHERE (v_inbox_item_type.type = 'review docs'::text)
) AS pk_category
,
    
(
SELECT v_inbox_item_type.pk_type
           
  FROM dem.v_inbox_item_type
          
 WHERE (v_inbox_item_type.type = 'review docs'::text)
) AS pk_type
,
    b_vuds.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 (blobs.v_unreviewed_docs_summary b_vuds
     
  JOIN dem.names d_n 
    ON (
           (b_vuds.pk_patient = d_n.id_identity)
     )
)
  
WHERE (d_n.active IS TRUE);

Index - Schema blobs

View: v_unreviewed_docs_summary

SELECT b_vud.pk_patient
,
    count
(1) AS no_of_docs
   
FROM blobs.v_unreviewed_docs b_vud
  
GROUP BY b_vud.pk_patient;

Index - Schema blobs

Function: delete_document(integer, integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	_pk_doc alias for $1;
	_pk_encounter alias for $2;
	_del_note text;
	_doc_row record;
	_obj_row record;
	tmp text;
BEGIN
	select * into _doc_row from blobs.doc_med where pk = _pk_doc;
	_del_note := _('Deletion of document') || E':
'
		|| ' '
			|| to_char(_doc_row.clin_when, 'YYYY-MM-DD HH24:MI')
			|| ' "' || (select _(dt.name) from blobs.doc_type dt where pk = _doc_row.fk_type) || '"'
			|| coalesce(' (' || _doc_row.ext_ref || ')', '')
		|| coalesce(E'
 ' || _doc_row.comment, '')
	;
	FOR _obj_row IN select * from blobs.doc_obj where fk_doc = _pk_doc order by seq_idx LOOP
		_del_note := _del_note || E'
'
			|| ' #' || coalesce(_obj_row.seq_idx, '-1') || ': "' || coalesce(_obj_row.comment, '') || E'"
'
			|| ' ' || coalesce(_obj_row.filename, '') || E'
';
	end LOOP;
	insert into clin.clin_narrative
		(fk_encounter, fk_episode, narrative, soap_cat)
	values (
		_pk_encounter,
		_doc_row.fk_episode,
		_del_note,
		NULL
	);
	-- should auto-cascade:
	--delete from blobs.doc_obj where fk_doc = _pk_doc;
	-- should auto-NULL:
	--bill.bill.fk_doc
	delete from blobs.doc_med where pk = _pk_doc;
	return True;
END;

Function: delete_document_part(integer, integer)

Returns: boolean

Language: PLPGSQL

DECLARE
	_pk_doc_part alias for $1;
	_pk_encounter alias for $2;
	_del_note text;
	_doc_row record;
	_obj_row record;
	tmp text;
BEGIN
	select * into _obj_row from blobs.doc_obj where pk = _pk_doc_part;
	select * into _doc_row from blobs.doc_med where pk = _obj_row.fk_doc;
	_del_note := _('Deletion of part from document') || E':
'
		|| ' '
			|| to_char(_doc_row.clin_when, 'YYYY-MM-DD HH24:MI')
			|| ' "' || (select _(dt.name) from blobs.doc_type dt where pk = _doc_row.fk_type) || '"'
			|| coalesce(' (' || _doc_row.ext_ref || ')', '')
		|| coalesce(E'
 ' || _doc_row.comment, '')
		|| E'
'
		|| ' #' || coalesce(_obj_row.seq_idx, '-1') || ': "' || coalesce(_obj_row.comment, '') || E'"
'
		|| ' ' || coalesce(_obj_row.filename, '') || E'
'
	;
	insert into clin.clin_narrative
		(fk_encounter, fk_episode, narrative, soap_cat)
	values (
		_pk_encounter,
		_doc_row.fk_episode,
		_del_note,
		NULL
	);
	delete from blobs.doc_obj where pk = _pk_doc_part;
	return True;
END;

Function: trf_do_not_duplicate_primary_episode_in_link_table()

Returns: trigger

Language: PLPGSQL

This trigger function is called before a lnk_doc_med2episode row is inserted or updated. It makes sure the episode does not duplicate the primary episode for this document listed in doc_med. If it does the insert/update is skipped.

BEGIN
	-- if already in doc_med
	perform 1 from blobs.doc_med dm where dm.fk_episode = NEW.fk_episode and dm.pk = NEW.fk_doc_med;
	if FOUND then
		-- skip the insert/update
		return null;
	end if;
	return NEW;
END;

Function: trf_mark_unreviewed_on_doc_obj_update()

Returns: trigger

Language: PLPGSQL

BEGIN
	if (NEW.data != OLD.data) or ((NEW.data != OLD.data) is NULL) then
		delete from blobs.reviewed_doc_objs where fk_reviewed_row = OLD.pk;
	end if;
	return NEW;
END;

Function: trf_remove_primary_episode_from_link_table()

Returns: trigger

Language: PLPGSQL

This trigger function is called when a doc_med row is inserted or updated. It makes sure the primary episode listed in doc_med is not duplicated in lnk_doc_med2episode for the same document. If it exists in the latter it is removed from there.

BEGIN
	-- if update
	if TG_OP = 'UPDATE' then
		-- and no change
		if NEW.fk_episode = OLD.fk_episode then
			-- then do nothing
			return NEW;
		end if;
	end if;
	-- if already in link table
	perform 1 from blobs.lnk_doc_med2episode ldm2e where ldm2e.fk_episode = NEW.fk_episode and ldm2e.fk_doc_med = NEW.pk;
	if FOUND then
		-- delete from link table
		delete from blobs.lnk_doc_med2episode where fk_episode = NEW.fk_episode and fk_doc_med = NEW.pk;
	end if;
	return NEW;
END;

Function: trf_set_intended_reviewer()

Returns: trigger

Language: PLPGSQL

Set the default on blobs.doc_obj.fk_intended_reviewer.

DECLARE
	_pk_patient integer;
	_pk_provider integer;
BEGIN
	-- explicitely set ?
	if NEW.fk_intended_reviewer is not NULL then
		return NEW;
	end if;
	-- find patient via document
	select
		fk_patient into _pk_patient
	from
		clin.encounter
	where
		clin.encounter.pk = (
			select fk_encounter from blobs.doc_med where pk = NEW.fk_doc
		);
	-- does patient have primary provider ?
	select
		fk_primary_provider into _pk_provider
	from
		dem.identity
	where
		dem.identity.pk = _pk_patient;
	if _pk_provider is not NULL then
		NEW.fk_intended_reviewer := _pk_provider;
		return NEW;
	end if;
	-- else use CURRENT_USER
	select
		pk into _pk_provider
	from
		dem.staff
	where
		dem.staff.db_user = current_user;
	NEW.fk_intended_reviewer := _pk_provider;
	return NEW;
END;

Function: trf_verify_page_number()

Returns: trigger

Language: PLPGSQL

declare
	msg text;
begin
	if NEW.seq_idx is NULL then
		return NEW;
	end if;
	perform 1 from blobs.doc_obj where pk <> NEW.pk and fk_doc = NEW.fk_doc and seq_idx = NEW.seq_idx;
	if FOUND then
		msg := '[blobs.trf_verify_page_number]: uniqueness violation: seq_idx [' || NEW.seq_idx || '] already exists for fk_doc [' || NEW.fk_doc || ']';
		raise exception '%', msg;
	end if;
	return NEW;
end;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict