Re: CAST Within EXCLUSION constraint
| От | Alexander Korotkov |
|---|---|
| Тема | Re: CAST Within EXCLUSION constraint |
| Дата | |
| Msg-id | CAPpHfdt6xnTEDShj1BvgzAT5avO00wrKaybCNVCkQmtKTxZkMA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: CAST Within EXCLUSION constraint ("David E. Wheeler" <david@justatheory.com>) |
| Ответы |
Re: CAST Within EXCLUSION constraint
|
| Список | pgsql-hackers |
On Tue, Aug 20, 2013 at 8:53 PM, David E. Wheeler <david@justatheory.com> wrote:
------
With best regards,
Alexander Korotkov.
On Aug 20, 2013, at 6:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Alas, no, same problem as for CAST():
> You need more parentheses -- (source::text) would've worked.Ah, right. Maybe if there was a way to get at some immutable numeric value…
ERROR: functions in index expression must be marked IMMUTABLE
>> No problem, I can use CAST(), right? So I try:
>> EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)
>> Not so much:
>> try.sql:13: ERROR: functions in index expression must be marked IMMUTABLE
>> I guess it's because locale settings might change, and therefore change the text representation? Seems unlikely, though.
>
> Not locale, just renaming one of the values would be enough to break that.
> Admittedly we don't provide an official way to do that ATM, but you can do
> an UPDATE on pg_enum.
It seems reasonable to me to cast enum to oid. However, creating casts without function isn't allowed for enums.
test=# create cast (source as oid) without function;
ERROR: enum data types are not binary-compatible
However, this restriction can be avoided either by writing dummy C-function or touching catalog directly:
test=# insert into pg_cast values ((select oid from pg_type where typname = 'source'), (select oid from pg_type where typname = 'oid'), 0, 'e', 'b');
INSERT 341001 1
Then you can define desired restriction.
CREATE TABLE things (
source source NOT NULL,
within tstzrange NOT NULL,
EXCLUDE USING gist ((source::oid) WITH =, within WITH &&)
);
Probably, I'm missing something and casting enum to oid is somehow unsafe?
With best regards,
Alexander Korotkov.
В списке pgsql-hackers по дате отправления: