Re: immutable functions and enumerate type casts in indexes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Edoardo Panfili <edoardo(at)aspix(dot)it>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: immutable functions and enumerate type casts in indexes
Date: 2008-09-01 21:06:07
Message-ID: 18869.1220303167@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Edoardo Panfili <edoardo(at)aspix(dot)it> writes:
> my enumerated type is (this is a subset)
> CREATE TYPE hibridation AS ENUM('none','genus','specie');

> function declaration
> CREATE FUNCTION ename(text,boolean,text,text RETURNS text AS
> 'funzioniGDB.so' LANGUAGE C IMMUTABLE;

> index creation (the type of ibrido is hibridation)
> CREATE INDEX i_specie_nome_specie ON specie
> (esterna_nome(ibrido::text,proParte,genere,specie));

> the result is
> ERROR: functions in index expression must be marked IMMUTABLE

Yeah, enum_out is considered STABLE not IMMUTABLE. I think this is
correct for the long term, even though right now you could make some
argument for an IMMUTABLE marking. For instance, we might in future
allow renaming of an enum member. (Actually, you can do that today
if you don't mind poking pg_enum by hand ...)

Now, maybe for your purposes here it's okay to consider it immutable.
In that case what I'd suggest doing is redefining ename() to take the
enum directly. You could invoke enum_out within the function if you
really need a text equivalent.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lennin Caro 2008-09-01 22:24:05 Re: MySQL LAST_INSERT_ID() to Postgres
Previous Message Lew 2008-09-01 17:50:22 Re: Oracle and Postgresql