为何PostgreSQL含WHERE子句的用户表更新函数仍报不安全查询错误？

问题背景 #

我在PostgreSQL中定义了如下user表：

create table "user"
(
    userid         uuid not null
        constraint pk__user__1788ccac19748a10
            primary key
        constraint uq__user__1788ccad4f14aa65
            unique,
    username       varchar(100)
        constraint uq__user__c9f2845657920267
            unique,
    passwordhash   bytea,
    firstname      varchar(40),
    lastname       varchar(40),
    salt           uuid,
    organisationid uuid
        constraint fk_user_organisation_organisationid
            references organisation,
    sessiontoken   uuid,
    sessionage     timestamp with time zone
);

alter table "user"
    owner to postgres;

随后编写了spUpdateUser函数用于更新用户表：

create or replace function spUpdateUser(userid uuid, firstname text, lastname text, passwordhash  bytea, salt  uuid , organisationid uuid) returns void
    language sql
as
$$

UPDATE dbo.User
SET FirstName      = ISNULL($2, FirstName),
    LastName       = ISNULL($3, LastName),
    PasswordHash   = ISNULL($4, PasswordHash),
    Salt           = ISNULL($5, Salt),
    OrganisationID = ISNULL($6, OrganisationID)
WHERE UserID = $1;

$$;

alter function spUpdateUser(uuid, text, text, text) owner to postgres;

意图是更新UserID匹配$1的单条记录，且已添加WHERE UserID = $1条件，但收到错误提示：

Unsafe query: 'Update' statement without 'where' updates all table rows at once

错误原因分析 #

这个错误提示并非PostgreSQL原生报错，而是来自客户端工具（如pgAdmin、代码检查插件）的静态分析误判，根源是函数中存在多个语法/定义问题，导致工具无法正确解析WHERE子句：

• 函数签名不匹配：你定义的spUpdateUser包含6个参数（uuid, text, text, bytea, uuid, uuid），但后续的ALTER FUNCTION语句中参数列表写的是spUpdateUser(uuid, text, text, text)，参数数量和类型都与实际定义不符。这会导致数据库中可能存在函数签名冲突，工具无法正确识别你实际要执行的函数逻辑，进而误判没有WHERE条件。

• 错误使用ISNULL函数：PostgreSQL中没有ISNULL(expr1, expr2)这种双参数函数，ISNULL是用于判断值是否为NULL的运算符（格式为expr IS NULL）。你想要实现“如果参数为NULL则保留原字段值”的逻辑，应该使用PostgreSQL标准的COALESCE函数。语法错误会导致工具的静态解析失败，无法识别到有效的WHERE子句，从而触发误报。

• 表名引用错误：你创建的表是"user"（因user是PostgreSQL关键字，必须用双引号包裹），但UPDATE语句中写的是dbo.User。如果没有手动创建dbo schema，或者表实际存在于默认的public schema下，这条语句会找不到目标表。工具可能因无法解析表名，进而忽略WHERE子句的存在，触发错误提示。

修复建议 #

针对上述问题，修改后的函数代码如下：

create or replace function spUpdateUser(userid uuid, firstname text, lastname text, passwordhash bytea, salt uuid, organisationid uuid) returns void
    language sql
as
$$
-- 假设表在public schema下，若实际在dbo则改为"dbo"."user"
UPDATE "user"
SET FirstName      = COALESCE($2, FirstName),
    LastName       = COALESCE($3, LastName),
    PasswordHash   = COALESCE($4, PasswordHash),
    Salt           = COALESCE($5, Salt),
    OrganisationID = COALESCE($6, OrganisationID)
WHERE UserID = $1;
$$;

-- 保持ALTER语句的参数签名与函数定义一致
alter function spUpdateUser(uuid, text, text, bytea, uuid, uuid) owner to postgres;

内容的提问来源于stack exchange，提问作者ChrisAsi71