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');

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');

• postgresql
• postgresql-17

• As long as element types match and you quote the function body, you can use a polymorphic anyarray. – Zegarek Commented Mar 7 at 15:16

1 Answer 1

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');

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);

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.