Alter system function to immutable in Azure PostgreSQL - Flexible Server (PostgreSQL 14)

Question

I am trying to create below index in Azure PostgreSQL Flexible Server (PostgreSQL version 14) with admin user

CREATE INDEX index_name ON table_name USING btree (to_char(columnname, 'YYYYMM'::text));
NOTE - Column data type in table is timestamptz.

It is showing me error as : SQL Error [42P17]: ERROR: functions in index expression must be marked IMMUTABLE
When I tried to alter the function to immutable with command - alter function to_char(timestamp with time zone, text) IMMUTABLE;
it is showing error as "SQL Error [42501]: ERROR: must be owner of function to_char",

The same above scenario I am able to execute without any issues in Azure PostgreSQL Single Server (PostgreSQL version 11).

Can anyone guide me on how to achieve this in Azure PostgreSQL Flexible Server (PostgreSQL version 14) ?

Answer 1

Hi, @sappu solanki The best way to achieve this is to create a custom function and make it immutable to use it in that index in our flexible offering .. for example:

CREATE FUNCTION custom_to_char(timestamptz) RETURNS text AS
$$ select to_char($1, 'YYYYMM'); $$
LANGUAGE sql immutable;

Please let me know if this helps.

Regards
Geetha

Answer 2

Hi, @sappu solanki Yes this is the expected behavior time/date functions are not immutable by definition: "IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. check here

if you really want this behavior can create a wrapper function which I mentioned above.

I hope this information helps, please let me know for any additional questions.

Regards
Geetha