PG ::错误:错误:左功能(字符变化，整数)不存在

Question

我正在将PostgreSQL与Rails 3.2一起使用。我的DB迁移之一具有以下内容：

execute <<-SQL
  CREATE INDEX users_multi_idx
  ON users (lower(left(fname, 1)), fname)
  WHERE deleted_at IS NULL;
SQL

在某些DBS上迁移时，我们会收到以下错误：

==  AddFnameIndexToUsers: migrating ===========================================
-- execute("      CREATE INDEX users_multi_idxn      ON users (lower(left(fname, 1)), fname)n      WHERE deleted_at IS NULL;n")
rake aborted!
An error has occurred, this and all later migrations canceled:
PG::Error: ERROR:  function left(character varying, integer) does not exist
LINE 2:       ON users (lower(left(fname, 1)), fname)
                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
:       CREATE INDEX users_multi_idx
      ON users (lower(left(fname, 1)), fname)
      WHERE deleted_at IS NULL;

奇怪的是，这并不是所有DB上都发生的，只是一些（分期）。关于此索引执行的问题有什么建议？

Answer 1

您将其标记为postgresql-9.1，但我强烈怀疑您在此处处理较旧的版本。您可以通过询问：

检查您使用的版本

SELECT version();

left()使用版本9.1引入。如果您使用的是旧版本，请用：

替换left(fname, 1)

substr(fname, 1, 1)

如果由于某种原因无法修改查询（例如@Wize），则可以在9.1之前为较旧版本创建一个：

之前的较旧版本

CREATE OR REPLACE FUNCTION public.left(text, int)
 RETURNS text LANGUAGE sql STABLE COST 30 AS
'SELECT substr($1, 1, $2)';

这通常不会在版本升级后引起冲突，因为默认模式搜索路径在public之前具有pg_catalog（隐式），因此用户定义的功能在系统功能退出 - 除非明确明确。但是您应该在版本升级后将其删除。

我添加了此内容，以提出@Wize提供的许多改进：

• 出于多种原因使用LANGUAGE sql（不是plpgsql）：

  • 较短，更简单。
  • PLPGSQL默认情况下未安装在9.0之前的版本中，因此您可能会遇到另一个问题，就像您要解决的问题一样。
  • 我提出的简单功能可以被内衬，从而在更大的查询中提高了性能。

• 使用功能波动率STABLE，这是合适的，有助于性能。

• 使用$n表示参考函数参数，因为在较旧版本中不支持参数名称。

• 明确在public模式中创建功能。否则，它可能是在当前用户中创建的。模式，不适合其他用户。根据您的模式搜索路径，这应该为您服务。

• 使用数据类型text，它是默认字符类型，与left()或substr()返回相同。也适用于varchar。

Answer 2

只需在9.1

之前为Posgres的版本创建以下功能

CREATE OR REPLACE FUNCTION left(s character varying,i int)
RETURNS character varying AS
$BODY$
BEGIN
  return substr(s, 1, i);
 END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;