Output format is wrapped. Expanded display is on. Table "dem.message_inbox" Column | Type | Modifiers --------------------+--------------------------+----------------------------------------------------------------------- pk_audit | integer | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass) row_version | integer | not null default 0 modified_when | timestamp with time zone | not null default now() modified_by | name | not null default "current_user"() pk | integer | not null default nextval('dem.provider_inbox_pk_seq'::regclass) fk_staff | integer | fk_inbox_item_type | integer | not null comment | text | data | text | importance | smallint | default 0 fk_patient | integer | ufk_context | integer[] | Indexes: "provider_inbox_pkey" PRIMARY KEY, btree (pk) Check constraints: "message_must_have_recipient" CHECK ((fk_staff IS NULL AND fk_patient IS NULL) IS FALSE) "provider_inbox_comment_check" CHECK (btrim(COALESCE(comment, 'xxxDEFAULTxxx'::text)) <> ''::text) "provider_inbox_importance_check" CHECK (importance = (-1) OR importance = 0 OR importance = 1) Foreign-key constraints: "message_inbox_fk_patient_fkey" FOREIGN KEY (fk_patient) REFERENCES dem.identity(pk) ON UPDATE CASCADE ON DELETE RESTRICT "provider_inbox_fk_inbox_item_type_fkey" FOREIGN KEY (fk_inbox_item_type) REFERENCES dem.inbox_item_type(pk) "provider_inbox_fk_staff_fkey" FOREIGN KEY (fk_staff) REFERENCES dem.staff(pk) Triggers: tr_message_inbox_generic_mod AFTER INSERT OR DELETE OR UPDATE ON dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE dem.trf_announce_message_inbox_generic_mod_no_pk() tr_message_inbox_mod AFTER INSERT OR DELETE OR UPDATE ON dem.message_inbox DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE dem.trf_announce_message_inbox_mod() zt_del_message_inbox BEFORE DELETE ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_del_message_inbox() zt_ins_message_inbox BEFORE INSERT ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_ins_message_inbox() zt_upd_message_inbox BEFORE UPDATE ON dem.message_inbox FOR EACH ROW EXECUTE PROCEDURE audit.ft_upd_message_inbox() Inherits: audit.audit_fields View "dem.v_message_inbox" Column | Type | Modifiers --------------------+--------------------------+----------- received_when | timestamp with time zone | provider | text | importance | integer | category | text | l10n_category | text | type | text | l10n_type | text | comment | text | pk_context | integer[] | data | text | pk_inbox_message | integer | pk_staff | integer | pk_category | integer | pk_type | integer | pk_patient | integer | is_virtual | boolean | xmin_message_inbox | xid | View definition: ( ( SELECT mi.modified_when AS received_when, ( 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.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 SELECT now() AS received_when, ( SELECT staff.short_alias FROM dem.staff WHERE staff.pk = vo4dnd.pk_intended_reviewer) AS provider, 0 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review docs' AS type, _('review docs'::text) AS l10n_type, ( SELECT ((((_('unreviewed documents for patient'::text) || ' ['::text) || dn.lastnames) || ', '::text) || dn.firstnames) || ']'::text FROM dem.names dn WHERE dn.id_identity = vo4dnd.pk_patient AND dn.active IS TRUE) AS comment, NULL::integer[] AS pk_context, NULL::text AS data, NULL::integer AS pk_inbox_message, vo4dnd.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 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, vo4dnd.pk_patient, true AS is_virtual, NULL::xid AS xmin_message_inbox FROM blobs.v_obj4doc_no_data vo4dnd WHERE vo4dnd.reviewed IS FALSE) UNION SELECT now() AS received_when, ( SELECT staff.short_alias FROM dem.staff WHERE staff.pk = vtr.pk_intended_reviewer) AS provider, 0 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review results' 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, NULL::xid 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, ( SELECT staff.short_alias FROM dem.staff WHERE staff.pk = vtr.pk_intended_reviewer) AS provider, 1 AS importance, 'clinical' AS category, _('clinical'::text) AS l10n_category, 'review results' 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, NULL::xid 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); View "clin.v_test_results" Column | Type | Modifiers --------------------------------+--------------------------+----------- pk_patient | integer | pk_test_result | integer | clin_when | timestamp with time zone | unified_abbrev | text | unified_name | text | unified_loinc | text | unified_val | text | unified_target_min | numeric | unified_target_max | numeric | unified_target_range | text | soap_cat | text | comment | text | val_num | numeric | val_alpha | text | val_unit | text | conversion_unit | text | val_normal_min | numeric | val_normal_max | numeric | val_normal_range | text | val_target_min | numeric | val_target_max | numeric | val_target_range | text | abnormality_indicator | text | norm_ref_group | text | note_test_org | text | material | text | material_detail | text | abbrev_tt | text | name_tt | text | loinc_tt | text | code_tt | text | coding_system_tt | text | comment_tt | text | name_test_org | text | contact_test_org | text | abbrev_meta | text | name_meta | text | loinc_meta | text | comment_meta | text | episode | text | health_issue | text | reviewed | boolean | is_technically_abnormal | boolean | is_clinically_relevant | boolean | review_comment | text | last_reviewer | text | last_reviewed | timestamp with time zone | review_by_you | boolean | review_by_responsible_reviewer | boolean | responsible_reviewer | text | you_are_responsible | boolean | modified_by | text | modified_when | timestamp with time zone | row_version | integer | pk_item | integer | pk_encounter | integer | pk_episode | integer | pk_test_type | integer | pk_intended_reviewer | integer | pk_request | integer | xmin_test_result | xid | pk_test_org | integer | pk_meta_test_type | integer | pk_health_issue | integer | pk_last_reviewer | integer | View definition: SELECT cenc.fk_patient AS pk_patient, tr.pk AS pk_test_result, tr.clin_when, vutt.unified_abbrev, vutt.unified_name, vutt.unified_loinc, CASE WHEN COALESCE(btrim(tr.val_alpha), ''::text) = ''::text THEN tr.val_num::text ELSE CASE WHEN tr.val_num IS NULL THEN tr.val_alpha ELSE ((tr.val_num::text || ' ('::text) || tr.val_alpha) || ')'::text END END AS unified_val, COALESCE(tr.val_target_min, tr.val_normal_min) AS unified_target_min, COALESCE(tr.val_target_max, tr.val_normal_max) AS unified_target_max, COALESCE(tr.val_target_range, tr.val_normal_range) AS unified_target_range, tr.soap_cat, tr.narrative AS comment, tr.val_num, tr.val_alpha, tr.val_unit, vutt.conversion_unit, tr.val_normal_min, tr.val_normal_max, tr.val_normal_range, tr.val_target_min, tr.val_target_max, tr.val_target_range, tr.abnormality_indicator, tr.norm_ref_group, tr.note_test_org, tr.material, tr.material_detail, vutt.abbrev_tt, vutt.name_tt, vutt.loinc_tt, vutt.code_tt, vutt.coding_system_tt, vutt.comment_tt, cto.internal_name AS name_test_org, cto.contact AS contact_test_org, vutt.abbrev_meta, vutt.name_meta, vutt.loinc_meta, vutt.comment_meta, epi.description AS episode, chi.description AS health_issue, COALESCE(rtr.fk_reviewed_row, 0)::boolean AS reviewed, rtr.is_technically_abnormal, rtr.clinically_relevant AS is_clinically_relevant, rtr.comment AS review_comment, ( SELECT staff.short_alias FROM dem.staff WHERE staff.pk = rtr.fk_reviewer) AS last_reviewer, rtr.modified_when AS last_reviewed, COALESCE(rtr.fk_reviewer = (( SELECT staff.pk FROM dem.staff WHERE staff.db_user = "current_user"())), false) AS review_by_you, COALESCE(tr.fk_intended_reviewer = rtr.fk_reviewer, false) AS review_by_responsible_reviewer, ( SELECT staff.short_alias FROM dem.staff WHERE staff.pk = tr.fk_intended_reviewer) AS responsible_reviewer, COALESCE(tr.fk_intended_reviewer = (( SELECT staff.pk FROM dem.staff WHERE staff.db_user = "current_user"())), false) AS you_are_responsible, CASE WHEN (( SELECT 1 FROM dem.staff WHERE staff.db_user = tr.modified_by)) IS NULL THEN ('<'::text || tr.modified_by::text) || '>'::text ELSE ( SELECT staff.short_alias FROM dem.staff WHERE staff.db_user = tr.modified_by) END AS modified_by, tr.modified_when, tr.row_version, tr.pk_item, tr.fk_encounter AS pk_encounter, tr.fk_episode AS pk_episode, tr.fk_type AS pk_test_type, tr.fk_intended_reviewer AS pk_intended_reviewer, tr.fk_request AS pk_request, tr.xmin AS xmin_test_result, vutt.pk_test_org, vutt.pk_meta_test_type, epi.fk_health_issue AS pk_health_issue, rtr.fk_reviewer AS pk_last_reviewer FROM clin.test_result tr LEFT JOIN clin.encounter cenc ON tr.fk_encounter = cenc.pk LEFT JOIN clin.episode epi ON tr.fk_episode = epi.pk LEFT JOIN clin.reviewed_test_results rtr ON tr.pk = rtr.fk_reviewed_row LEFT JOIN clin.health_issue chi ON epi.fk_health_issue = chi.pk, clin.v_unified_test_types vutt LEFT JOIN clin.test_org cto ON vutt.pk_test_org = cto.pk WHERE tr.fk_type = vutt.pk_test_type; View "blobs.v_obj4doc_no_data" Column | Type | Modifiers -------------------------------+--------------------------+----------- pk_patient | integer | pk_obj | integer | seq_idx | integer | size | integer | date_generated | timestamp with time zone | type | text | l10n_type | text | ext_ref | text | episode | text | doc_comment | text | obj_comment | text | filename | text | pk_intended_reviewer | integer | reviewed | boolean | reviewed_by_you | boolean | reviewed_by_intended_reviewer | boolean | pk_doc | integer | pk_type | integer | pk_encounter | integer | pk_episode | integer | pk_health_issue | integer | xmin_doc_obj | xid | View definition: SELECT vdm.pk_patient, dobj.pk AS pk_obj, dobj.seq_idx, octet_length(COALESCE(dobj.data, ''::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; View "dem.v_inbox_item_type" Column | Type | Modifiers ------------------+---------+----------- type | text | l10n_type | text | category | text | l10n_category | text | is_user_type | boolean | is_user_category | boolean | pk_type | integer | pk_category | integer | View definition: 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; select * from dem.v_message_inbox; psql:xx.sql:14: ERROR: could not implement UNION DETAIL: Some of the datatypes only support hashing, while others only support sorting.