Postgres将“文本”替换为“文本”

Question

我插入了一堆带有文本字段(如content='...\n...\n...' )的行。

我没有在前面使用e，比如conent=e'...\n...\n...，所以现在\n实际上不是以换行符的形式显示的，而是以文本的形式打印出来的。

如何解决这个问题，即如何将每行的内容字段从'...'更改为e'...'

Answer 1

语法变量E'string'使Postgres将给定的字符串解释为Posix转义字符串。\n编码换行符只是许多解释转义序列中的一个(即使是最常见的)。请参见：

• 在PostgreSQL中插入单引号文本

要“重新评估”Posix转义字符串，可以使用一个带有动态SQL的简单函数，如下所示：

CREATE OR REPLACE FUNCTION f_eval_posix_escapes(INOUT _string text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT E''' || _string || ''''  INTO _string;
END
$func$;

警告1:，这本质上是不安全的！我们必须动态地计算输入字符串，而不需要引用和转义，这允许SQL注入。只在安全的环境中使用。

警告2:不重复应用。否则，它将错误地使用真正的\字符来解释实际字符串，等等。

警告3:这个简单的函数是不完美的，因为它不能正确地处理嵌套单引号。如果你有其中的一些，考虑一下：

• 取消带有转义换行符和回车的字符串

适用范围：

UPDATE tbl
SET    content = f_eval_posix_escapes(content)
WHERE  content IS DISTINCT FROM f_eval_posix_escapes(content);

db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6477ccaf1fddacd6d12b68f90db23edf

注意添加的WHERE子句可以跳过不会更改任何内容的更新。请参见：

• 如何(或可以)在多列上选择DISTINCT？

Answer 2

在更新查询中使用替换。类似这样的东西：(我在移动，所以请忽略任何错误或语法错误)

UPDATE table
SET 
column = REPLACE(column, '\n', e'\n')