Exclusion constraint issue

From: Eric McKeeth <eldin00(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Exclusion constraint issue
Date: 2010-09-24 21:09:29
Message-ID: AANLkTim7nhtRi_muWrVwxMwHey2E4uZpLXm_WEM9HQxD@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm getting an error message that doesn't make sense to me. Using PostgreSQL
9.0.0 on CentOS 5.5.

Given the following table and function definitions

CREATE TABLE test3
(
test3_id serial NOT NULL,
fk_id integer,
data_3 text,
effect_date date NOT NULL,
expire_date date NOT NULL,
CONSTRAINT test3_pkey PRIMARY KEY (test3_id)
)

CREATE OR REPLACE FUNCTION period(timestamp with time zone, timestamp with
time zone)
RETURNS period AS
$BODY$
SELECT CASE WHEN $1 <= $2
THEN ($1, $2)::period
ELSE ($2, $1)::period END;
$BODY$
LANGUAGE sql IMMUTABLE STRICT

and the period datatype with it's associated functions and operators
installed from http://pgfoundry.org/projects/timespan/

why would I get the following error, since the period() function is in fact
declared as immutable?

test=# ALTER TABLE test3 ADD exclude using
gist(period(effect_date::timestamptz, expire_date::timestamptz) with && );
ERROR: functions in index expression must be marked IMMUTABLE

Thanks in advance for any assistance.
-Eric

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-09-24 21:22:15 Re: Exclusion constraint issue
Previous Message Joshua D. Drake 2010-09-24 21:03:21 Re: Trade Study on Oracle vs. PostgreSQL