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?
First, the function requires two parameters in the definition, like Richard already suggested, and you updated your question accordingly.
Second, 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 AS 'text_concat_ws' LANGUAGE internal IMMUTABLE PARALLEL SAFE;
Mark it as PARALLEL SAFE
to not spoil 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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With