Hi all !
Attached find some table and view definitions from the
GNUmed (www.gnumed.de) database.
Unfortunately I do not understand why PostgreSQL says
psql:xx.sql:14: ERROR: could not implement UNION
DETAIL: Some of the datatypes only support hashing, while others only support sorting.
when I say
select * from dem.v_message_inbox;
I mean, I (hope I) do understand what PostgreSQL tries to
tell me but I don't know how to find out which columns are
affected ...
Thanks !
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
