Create an immutable clone of concat_ws

Question

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?

Which kinds of arguments do you want to feed to your function - and what's the use case where you concatenate multiple expressions for an index. Might be an XY problem, and a more efficient solution around two corners. The function you show here is not only wrong (missing 1st parameter), it's also potentially hazardous. Related: https://stackoverflow.com/a/12320369/939860 — Erwin Brandstetter, Jan 25 '19 at 23:49

Erwin Brandstetter · Accepted Answer · 2023-01-18T10:32:41.667

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:

PostgreSQL full text search on many columns

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.

Combine two columns and add into one new column

I'm only dealing with text and NULL values here, so immutable should be fine. Thanks a bunch for the help! — svoop, Jan 28 '19 at 08:55

score 1 · Answer 2 · answered Jan 25 '19 at 21:19

OK, so you're mapping to internal "C" functions, which I must admit I've never done myself.

However, text_concat_ws is "with separator" so it doesn't just take a variadic list of text arguments - it takes a separator THEN the variadic list of text arguments. Adjust your function definition accordingly.

If you're going to be doing this, you probably want to hook a debugger up to the backend or run it single process if that's practical.

Also - I just found the doxygen interface to the PostgreSQL source-code replying to your question. Thanks :-)

Create an immutable clone of concat_ws

2 Answers2

Linked