Re: immutable functions and enumerate type casts in indexes - Mailing list pgsql-general

From Tom Lane
Subject Re: immutable functions and enumerate type casts in indexes
Date
Msg-id 18869.1220303167@sss.pgh.pa.us
Whole thread Raw
In response to immutable functions and enumerate type casts in indexes  (Edoardo Panfili <edoardo@aspix.it>)
Responses Re: immutable functions and enumerate type casts in indexes  (Edoardo Panfili <edoardo@aspix.it>)
List pgsql-general
Edoardo Panfili <edoardo@aspix.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

pgsql-general by date:

Previous
From: Howard Cole
Date:
Subject: Can I truncate statements in the log?
Next
From: Lennin Caro
Date:
Subject: Re: MySQL LAST_INSERT_ID() to Postgres