创建concat_ws的不可变克隆 [英] Create an immutable clone of concat_ws

问题描述

此博客文章显示了如何在Pg中创建 immutable_concat 函数：

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

我想对 concat_ws 做同样的事情，并且对应的 text_concat_ws 确实存在，但是，下面的内容导致进程崩溃：

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

更新： immutable_concat_ws 应该为（胶水，*部分），一种胶水（文本或varchar）和一个或多个部分（文本，varchar或null）。

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

我在这里想念什么？

推荐答案

首先，该函数在定义中需要两个参数，就像Richard已经建议的那样，并且您更新了

First, the function requires two parameters in the definition, like Richard already suggested, and you updated your question accordingly.

第二，您可以使用 any 输入创建该函数使用 LANGUAGE内部。

Second, you can create that function with "any" input using LANGUAGE internal. Does not mean that you should, though.

concat_ws（）只是 STABLE 是有原因的。其中， date 或 timestamp 的文本表示取决于语言环境/日期样式设置，因此结果不是不可变。基于此的索引可能会无提示地中断。限于文本输入，可以安全地声明为 IMMUTABLE 。
因为您只需要文本输入（或 varchar ，它隐式转换为 text ），将其限制在您的用例中并确保安全：

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.

抵制执行此类 immutable_concat_ws（'|'，now（）:: text，'foo'）的诱惑。

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

这篇关于创建concat_ws的不可变克隆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！