Dumped on 2018-02-22

Index of database - gnumed_v20

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

Index - Schema blobs

Table: doc_med

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

Tables referencing this one via Foreign Key Constraints:

• doc_desc

• doc_obj

• lnk_doc2hospital_stay

• lnk_doc_med2episode

Index - Schema blobs

Table: doc_obj

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

Tables referencing this one via Foreign Key Constraints:

• reviewed_doc_objs

Index - Schema blobs

Table: doc_type

this table enumerates the document types known to the system

Tables referencing this one via Foreign Key Constraints:

• doc_med

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 (
SELECT encounter.fk_patient
           
  FROM clin.encounter
          
 WHERE (encounter.pk = dm.fk_encounter)
) AS pk_patient
,
    dm.pk AS pk_doc
,
    dm.clin_when
,
    dt.name AS type
,
    _
(dt.name) AS l10n_type
,
    dm.ext_ref
,
    cle.description AS episode
,
    dm.comment
,
    cle.is_open AS episode_open
,
    dm.fk_type AS pk_type
,
    dm.fk_encounter AS pk_encounter
,
    dm.fk_episode AS pk_episode
,
    cle.fk_health_issue AS pk_health_issue
,
    dm.modified_when
,
    dm.modified_by
,
    dm.xmin AS xmin_doc_med
,
    chi.description AS health_issue
   
FROM blobs.doc_med dm
,
    blobs.doc_type dt
,
    
(clin.episode cle
     
LEFT JOIN clin.health_issue chi 
    ON (
           (chi.pk = cle.fk_health_issue)
     )
)
  
WHERE (
     (dt.pk = dm.fk_type)
   AND (cle.pk = dm.fk_episode)
);

Index - Schema blobs

View: v_doc_med_journal

SELECT (
SELECT encounter.fk_patient
           
  FROM clin.encounter
          
 WHERE (encounter.pk = dm.fk_encounter)
) AS pk_patient
,
    dm.modified_when
,
    dm.clin_when
,
    COALESCE
(
     (
      SELECT staff.short_alias
           
        FROM dem.staff
          
       WHERE (staff.db_user = dm.modified_by)
     )
     , (
           ('<'::text || 
                 (dm.modified_by)::text
           ) || '>'::text
     )
) AS modified_by
,
    NULL::text AS soap_cat
,
    
(
     (
           (
                 (
                       (
                             (_
                                   ('Document'::text) || ': '::text
                             ) || _
                             (dt.name)
                       ) || COALESCE
                       (
                             (
                                   (' "'::text || dm.ext_ref) || 
                               '" ('::text)
                                   , ' ('::text)
                             ) || to_char
                             (dm.clin_when
                                   ,'YYYY-MM-DD HH24:MI'::text
                             )
                       ) || 
               ')'::text
           ) || COALESCE
           (
                 ('
 '::text || dm.comment)
                 ,''::text
           )
     ) AS narrative
     ,
    dm.fk_encounter AS pk_encounter
     ,
    dm.fk_episode AS pk_episode
     ,
    
     (
      SELECT episode.fk_health_issue
           
        FROM clin.episode
          
       WHERE (episode.pk = dm.fk_episode)
     ) AS pk_health_issue
     ,
    dm.pk AS src_pk
     ,
    'blobs.doc_med'::text AS src_table
   
  FROM blobs.doc_med dm
     ,
    blobs.doc_type dt
  
 WHERE (dt.pk = dm.fk_type);

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 vdm.pk_patient
,
    dobj.pk AS pk_obj
,
    dobj.seq_idx
,
    octet_length
(COALESCE
     (dobj.data
           ,'\x'::bytea
     )
) AS size
,
    vdm.clin_when AS date_generated
,
    vdm.type
,
    vdm.l10n_type
,
    vdm.ext_ref
,
    vdm.episode
,
    vdm.comment AS doc_comment
,
    dobj.comment AS obj_comment
,
    dobj.filename
,
    dobj.fk_intended_reviewer AS pk_intended_reviewer
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.reviewed_doc_objs
          
       WHERE (reviewed_doc_objs.fk_reviewed_row = dobj.pk)
     )
) AS reviewed
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM blobs.reviewed_doc_objs
          
       WHERE (
                 (reviewed_doc_objs.fk_reviewed_row = dobj.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 = dobj.pk)
               AND (reviewed_doc_objs.fk_reviewer = dobj.fk_intended_reviewer)
           )
     )
) AS reviewed_by_intended_reviewer
,
    vdm.pk_doc
,
    vdm.pk_type
,
    vdm.pk_encounter
,
    vdm.pk_episode
,
    vdm.pk_health_issue
,
    dobj.xmin AS xmin_doc_obj
   
FROM blobs.v_doc_med vdm
,
    blobs.doc_obj dobj
  
WHERE (vdm.pk_doc = dobj.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