Postgres Pro
Downloads
Home   >   mailing lists

Thread: immutable functions and enumerate type casts in indexes

immutable functions and enumerate type casts in indexes

From
Edoardo Panfili
Date:
Hello,

I have a problem with enumerated types in functions parameters.

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

Searching on google I found some explanation: the problem arises with
"non immutable" typea as "data" but I can't figure the problem (or
better, the solution) with enumerate types.

What can I do?

thank you
Edoardo

Re: immutable functions and enumerate type casts in indexes

From
Tom Lane
Date:
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

Re: immutable functions and enumerate type casts in indexes

From
Edoardo Panfili
Date:
Tom Lane ha scritto:
> 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
>
> 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.
thank you! this is the right way for me.
Now it works.

But i have a little question about parameters of enum_out.
Datum enum_out(PG_FUNCTION_ARGS);
this is a part of my function
-----------------------------------------------------------
Datum esterna_nome2(PG_FUNCTION_ARGS){
    int label;
    label = enum_out(fcinfo);
    sprintf(debug,"false enum_out: \"%s\" ",unrolled);
    elog(LOG, debug);
-----------------------------------------------------------
but it works only because my enum parameter is the first (and using
fcinfo is a little obscure).

I must build a FunctionCallInfo structure (I think) but how?

Edoardo

Re: immutable functions and enumerate type casts in indexes

From
Martijn van Oosterhout
Date:
On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
> But i have a little question about parameters of enum_out.
> Datum enum_out(PG_FUNCTION_ARGS);
> this is a part of my function
> -----------------------------------------------------------
> Datum esterna_nome2(PG_FUNCTION_ARGS){
>     int label;
>     label = enum_out(fcinfo);
>     sprintf(debug,"false enum_out: \"%s\" ",unrolled);
>     elog(LOG, debug);
> -----------------------------------------------------------
> but it works only because my enum parameter is the first (and using
> fcinfo is a little obscure).


Look in the fmgr.h header for functions like DirectFunctionCall1 and
various other ways of calling functions.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachment

Re: immutable functions and enumerate type casts in indexes

From
Edoardo Panfili
Date:
Martijn van Oosterhout ha scritto:
> On Tue, Sep 02, 2008 at 10:53:03AM +0200, Edoardo Panfili wrote:
>> But i have a little question about parameters of enum_out.
>> Datum enum_out(PG_FUNCTION_ARGS);
>> this is a part of my function
>> -----------------------------------------------------------
>> Datum esterna_nome2(PG_FUNCTION_ARGS){
>>     int label;
>>     label = enum_out(fcinfo);
>>     sprintf(debug,"false enum_out: \"%s\" ",unrolled);
>>     elog(LOG, debug);
>> -----------------------------------------------------------
>> but it works only because my enum parameter is the first (and using
>> fcinfo is a little obscure).
>
>
> Look in the fmgr.h header for functions like DirectFunctionCall1 and
> various other ways of calling functions.
>
Now it works! thank you to Martin and Tom.

this is a code fragment:
--------------------------------------------------------------
#include "utils/builtins.h"

PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
    Datum datumIbrido = PG_GETARG_DATUM(0);
    Datum labelDatum;
    char *label;

    labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
    label = (char *) DatumGetPointer(labelDatum);
    if(strcmp(label,("label_constant"))==0){
        ...
    }
    ...
}
--------------------------------------------------------------
I don't know why but I need <<#include "utils/builtins.h">>

The line "label = (char *) DatumGetPointer(labelDatum);" is essential to
use the information in strcmp() if I use directly labelDatum it does not
works (but it works inside a sprintf(buffer,"%s",labelDatum)).

thank you again
Edoardo

Re: immutable functions and enumerate type casts in indexes

From
Tom Lane
Date:
Edoardo Panfili <edoardo@aspix.it> writes:
>     labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
>     label = (char *) DatumGetPointer(labelDatum);

Just FYI, preferred style for the second line would be

    label = DatumGetCString(labelDatum);

Nearly all standard data types have DatumGetFoo and FooGetDatum
macros to hide the conversion details (even if it's only a cast).

            regards, tom lane

Re: immutable functions and enumerate type casts in indexes

From
Edoardo Panfili
Date:
Tom Lane ha scritto:
> Edoardo Panfili <edoardo@aspix.it> writes:
>>     labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
>>     label = (char *) DatumGetPointer(labelDatum);
>
> Just FYI, preferred style for the second line would be
>
>     label = DatumGetCString(labelDatum);
>
> Nearly all standard data types have DatumGetFoo and FooGetDatum
> macros to hide the conversion details (even if it's only a cast).

the clean version:
--------------------------------------------------------------
#include "utils/builtins.h"

PG_FUNCTION_INFO_V1(esterna_nome);
Datum esterna_nome(PG_FUNCTION_ARGS){
     Datum datumIbrido = PG_GETARG_DATUM(0);
     Datum labelDatum;
     char *label;

     labelDatum = DirectFunctionCall1(enum_out,datumIbrido);
     label = DatumGetCString(labelDatum);
     if(strcmp(label,("label_constant"))==0){
         ...
     }
     ...
}
--------------------------------------------------------------


thank you again!
Edoardo