Postgres Immutable Concat in Indexes

The volitility category of Postgres concat function is STABLE which means it’s guaranteed to return the same results given the same arguments for all rows within a single statement. Basically, you’re out of luck if you want to use this function in an index. Tom Lane explains in this post:

> The pg_catalog.concat() is defined as STABLE function.
> why was STABLE preferred for concat() over IMMUTABLE?
concat() invokes datatype output functions, which are not necessarily
immutable. An easy example is that timestamptz_out's results depend
on the TimeZone setting.

In my case I’m only dealing with integer and text columns so I modified the original function (STABLE -> IMMUTABLE), creating immutable_concat.

CREATE OR REPLACE FUNCTION immutable_concat(VARIADIC "any")
RETURNS text AS
'text_concat'
LANGUAGE internal IMMUTABLE
COST 1;
Like what you read? Give Tyler Cubell a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.