Dumped on 2015-04-16

Index of database - gnumed_v19

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

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