For the record:
On Wed, Feb 09, 2011 at 11:12:01PM +0100, Karsten Hilbert wrote:
> 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.
The solution lies in these bits:
> 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[] |
This data type can only be hashed.
> 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 |
This data type can only be sorted.
By defining an explicit caster:
create or replace function gm.xid2int(xid)
returns integer
language 'sql'
as 'select $1::text::integer;';
and applying that to the XMIN column inside the view
definition nicely solves the "could not implement UNION".
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
