postgresqlpostgresql-17

Create an immutable version of CONCAT()


I tried to create an immutable CONCAT(). For this, I tried to use the following SQL queries.

Option 1

CREATE FUNCTION immutable_concat(VARIADIC text[])
  RETURNS text
  LANGUAGE internal IMMUTABLE PARALLEL SAFE AS 'text_concat';

Option 2

CREATE FUNCTION immutable_concat(VARIADIC text[])
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
  RETURN array_to_string($1, '');

As a result, I get a function that works well with text. But how can I create a similar function that will take an array containing not only text elements?

CONCAT() allows us to execute the following SQL query:

SELECT concat(CURRENT_DATE, true, false, 1, 'text');

As a result, we will get the following string:

2025-03-07tf1text

Ideally, I would like to create a function that can take different elements. I mean the following:

SELECT immutable_concat(CURRENT_DATE, true, false, 1, 'text');

Solution

  • The way concat() can do that is by accepting variadic any which neither sql nor plpgsql language functions are allowed to. As long as element types match and you quote the function body, you can use a polymorphic anyarray:
    demo at db<>fiddle

    CREATE FUNCTION immutable_concat(VARIADIC anyarray)
      RETURNS text
      LANGUAGE sql IMMUTABLE PARALLEL SAFE
      as $$ select array_to_string($1, '') $$;
    
    select immutable_concat(1,2,3)
          ,immutable_concat(true,false,false)
          ,immutable_concat(1e2,.1,9.)
          ,immutable_concat(now(),'infinity','today allballs');
    
    immutable_concat immutable_concat immutable_concat immutable_concat
    123 tff 1000.19 2025-03-07 15:20:26.448193+00infinity2025-03-07 00:00:00+00

    You can also reclassify the internal concat():

    CREATE FUNCTION immutable_concat(VARIADIC "any")
      RETURNS text
      LANGUAGE internal IMMUTABLE PARALLEL SAFE
      as 'text_concat';
    select immutable_concat(1,'x'::text,false);
    
    immutable_concat
    1xf

    My guess is you're trying to use that for a generated expression or a functional index and if so, I'm assuming you know the risk.