Search code examples
postgresqlfunctionconcatenationconcat-ws

Create an immutable clone of concat_ws

This blog post shows an example of how to create a immutable_concat function in Pg:

CREATE OR REPLACE FUNCTION immutable_concat(VARIADIC "any")
  RETURNS text AS 'text_concat'
  LANGUAGE internal IMMUTABLE

I'd like to do the same with concat_ws and the corresponding text_concat_ws does exist, however, the following just crashes the process:

CREATE OR REPLACE FUNCTION immutable_concat_ws(VARIADIC "any")
  RETURNS text AS 'text_concat_ws'
  LANGUAGE internal IMMUTABLE

Update: The siguature of immutable_concat_ws should be (glue, *parts), one glue (text or varchar) and one or more parts (text, varchar or null).

What am I missing here?

Solution

  • Firstly, the function requires two parameters in the definition, like Richard already suggested, and you updated your question accordingly.

    Secondly, you can create that function with "any" input using LANGUAGE internal. Does not mean that you should, though.

    concat_ws() is only STABLE for a reason. Among others, the text representation of date or timestamp depends on locale / datestyle settings, so the result is not immutable. Indexes building on this could silently break. Restricted to text input, it's safe to declare it IMMUTABLE. Since you only need text input (or varchar, which has an implicit cast to text), limit it to your use case and be safe:

    CREATE OR REPLACE FUNCTION immutable_concat_ws(text, VARIADIC text[])
  RETURNS text
  LANGUAGE internal IMMUTABLE PARALLEL SAFE AS
'text_concat_ws';

    Crating a LANGUAGE internal function requires superuser privileges. If that's not an option, the next best thing would be an SQL function like:

    Mark it as PARALLEL SAFE in Postgres 9.6 or later (it qualifies!) to enable parallelism when involving this function. The manual:

    all user-defined functions are assumed to be parallel unsafe unless otherwise marked.

    Resist the temptation to do things like this immutable_concat_ws('|', now()::text, 'foo'). This would reintroduce said dependencies in the call.

    Related: