Re: problematic view definition

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