sql - 函数调用的属性符号给出错误

Question

当当前模式与函数之一不同时，属性符号函数调用会出错。

我创建了一个函数

CREATE FUNCTION pub.FullName(pub.reps)
  RETURNS text AS
$func$
       select ($1.fname || ' ' || $1.lname)
$func$ LANGUAGE SQL;

我正在尝试使用 docs 中描述的属性符号调用函数):

select r.fullname from pub.reps r;

但是得到一个错误信息:

ERROR:  column "fullname" does not exist

使用函数符号的查询工作正常:

select pub.fullname(r.*) from pub.reps r;

数据库已通过备份/恢复从 PostgreSQL 10 迁移。

选择版本() 给出:PostgreSQL 11.3，由 Visual C++ build 1914 编译，64 位

更新。发现如果我将 pub 模式设置为默认模式，则 select r.fullname from pub.reps r 可以正常工作。

Answer 1

您自己找到了问题的根源。准确地说:pub 函数的架构必须列在当前 search_path 中的任何地方，而不是必须是“默认”或“当前”架构(列表中的第一个)。相关:

• How does the search_path influence identifier resolution and the "current schema"

所以Postgres没有找到这个函数。在这方面，Postgres 11 与 Postgres 10 没有什么不同。不过，有一些值得注意的相关发展。你提到:

Database has been migrated from PostgreSQL 10 with backup/restore.

考虑 release notes for Postgres 11 中指出的这个细微变化:

• Consider syntactic form when disambiguating function versus column references (Tom Lane)

  When x is a table name or composite column, PostgreSQL has traditionally considered the syntactic forms f(x) and x.f to be equivalent, allowing tricks such as writing a function and then using it as though it were a computed-on-demand column. However, if both interpretations are feasible, the column interpretation was always chosen, leading to surprising results if the user intended the function interpretation. Now, if there is ambiguity, the interpretation that matches the syntactic form is chosen.

所以，如果表 reps 中有一个列 fullname 还有函数 pub .fullname(pub.reps) 你显示，Postgres 10，即使有功能符号，仍然会选择列:

SELECT fullname(r) FROM reps r;  -- resolves to column if it exists, ignoring function

db<> fiddle here 用于 Postgres 10

Postgres 11(更合理)选择函数:

db<> fiddle here 用于 Postgres 11

Postgres 12(目前为 beta 版)最终实​​现了真正的生成列。 The release notes:

• Add support for generated columns (Peter Eisentraut)

The content of generated columns are computed from expressions (including references to other columns in the same table) rather than being specified by INSERT or UPDATE commands.

不过，只有 STORED 生成的列进入了这个版本。 (更有趣的海事组织)VIRTUAL variant was postponed for a later release . (还没有在 Postgres 13 中。)

您的表格可能如下所示:

CREATE TABLE pub.reps (
  reps_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY 
, fname    text NOT NULL
, lname    text NOT NULL
, fullname text GENERATED ALWAYS AS (fname || ' ' || lname) STORED
);

db<> fiddle here

我声明了 fname 和 lname 列 NOT NULL。否则，您的简单连接 (fname || ' ' || lname) 是一个陷阱。见:

• How to concatenate columns in a Postgres SELECT?
• Combine two columns and add into one new column