Create an immutable version of CONCAT()

Question

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

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

Zegarek · Accepted Answer · 2025-03-07 16:03:04Z

2

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.

edited Mar 7 at 16:03

answered Mar 7 at 15:25

Zegarek

30.1k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ivan Yuzafatau Mar 7 at 15:36

This works if all elements have the same type. For example, this will not work with select immutable_concat('text', 1) or select immutable_concat(CURRENT_DATE, true, false)

Zegarek Mar 7 at 15:45

Correct, that's what I opened with.

Ivan Yuzafatau Mar 7 at 16:05

This is exactly what I need. Thank you very much! Yes, I realize all the risks when I use this in indexes.

Zegarek Mar 7 at 16:06

Other than that, you'd probably have to do some heavy overloading to achieve the same effect in plain sql or plpgsql simply because they don't support heteromorphic arrays.

Collectives™ on Stack Overflow

Create an immutable version of CONCAT()

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related