Re: problematic view definition

I should have mentioned this is on

PostgreSQL 8.4.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-4) 4.4.5, 32-bit

Karsten

On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:
> Date: Wed, 9 Feb 2011 23:12:01 +0100
> From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
> To: pgsql-general <pgsql-general(at)postgresql(dot)org>
> Subject: [GENERAL] problematic view definition
> User-Agent: Mutt/1.5.20 (2009-06-14)
>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346