使用plpgsql将不同版本的Postgres中的列字节改为文本

Question

我在如何使用plpgsql（bytea->text）转换列时遇到问题。我写了一个函数，它适用于某些数据库，而不适用于其他数据库。我不知道该怎么修。
使用8.0-9.3版本的数据库；此错误适用于8.1.19。

我收到：

ERROR:  column "the_column" cannot be cast to type "text"
CONTEXT:  SQL statement "ALTER TABLE the_table ALTER COLUMN the_column TYPE text"
PL/pgSQL function "byteatotext" line 11 at execute statement

我的功能：

CREATE OR REPLACE FUNCTION byteaToText()
  RETURNS text AS
  $BODY$
    DECLARE
      ver int;
    BEGIN
      SELECT into ver (select setting from pg_settings where name='server_version_num') as test;
      IF ver < 80200 THEN
        EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text USING ENCODE(properties, 'escape'))';
        RETURN ver;
      ELSE
        EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text';
        RETURN ver;
      END IF;
    RETURN 'error';
    END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
select byteaToText();

Answer 1

配置参数server_version_num是在Postgres8.2中引入的。根据文档：

添加新的配置参数server_version_num（Greg Sabino Mullane）

这类似于server_version，但它是一个整数，例如80200。这允许应用程序更容易地进行版本检查。

您的代码在第8.1页中找不到不存在的参数，也没有分配给ver，因此它保持为NULL，控制权最终在ELSE分支中——这也在错误消息中指示：

ERROR:  column "the_column" cannot be cast to type "text"
CONTEXT:  SQL statement "ALTER TABLE the_table ALTER COLUMN the_column TYPE text"
PL/pgSQL function "byteatotext" line 11 at execute statement

将函数重写为：

CREATE OR REPLACE FUNCTION bytea_to_text()
  RETURNS text AS
$func$
BEGIN
   -- the config param was introduced with version 8.2
   PERFORM 1 FROM pg_settings WHERE name = 'server_version_num';
   IF FOUND THEN  -- version >= 8.2
      EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text';
   ELSE
      EXECUTE 'ALTER TABLE the_table ALTER COLUMN the_column TYPE text USING encode(the_column, ''escape''))';
   END IF;
   RETURN (SELECT setting FROM pg_settings WHERE name = 'server_version');
END
$func$ LANGUAGE plpgsql;

备注

• 在Postgres 8.1上未测试，没有人再使用那么旧的Postgres了
• 返回配置参数server_version，该参数也存在于第8.1页中
• 我用the_column替换了properties，假设这是另一个错误
• 不要使用大小写混合的名称
• 不要引用语言名称。使用LANGUAGE plpgsql，无引号

旁白：
显而易见的解决方案是将Postgres服务器升级到用电而不是蒸汽运行的版本。Postgres 8.1是10年前写的，于2010年11月达到EOL。除此之外，至少要更新到最后一个发布点，即8.1.23。