PostgreSQL函数赋值报错：将数据库值赋值给变量时语法错误排查

问题背景 #

作为PostgreSQL开发新手，需要编写名为spUserTest的函数，实现以下功能：

• 传入参数username2，从user表中查询对应的UserID；
• 使用该UserID从userdevice表提取相关数据。

表定义 #

-- auto-generated definition
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;

-- auto-generated definition
create table userdevice
(
    userdeviceid uuid not null
        constraint pk__userdevi__fd151c39ccac5a1d
            primary key,
    userid       uuid
        constraint fkuser_userdevice_userid
            references "user",
    deviceid     uuid
        constraint fkuser_userdevice_deviceid
            references device,
    constraint unique_userid_deviceid
        unique (userid, deviceid)
);

alter table userdevice
    owner to postgres;

原函数代码 #

create function spuserTest(username2 text)
    returns TABLE(UserDeviceID uuid, UserID uuid, DeviceID uuid)
    language sql
as
 do $$

  declare
       UserID2     uuid;

begin

    UserID2 := (SELECT t.userid from dbo.user t where t.username = username2);

RETURN QUERY
        SELECT
            UserDeviceID ::text,
            UserID  ::text,
            DeviceID ::text
        FROM
            dbo.userdevice
        where
            UserID = UserID2;

END $$;

运行错误 #

[2023-10-31 12:18:38] [42601] ERROR: syntax error at end of input
[2023-10-31 12:18:38] Position: 131

错误原因 #

• 语言类型不匹配：函数声明为language sql，但使用了PL/pgSQL特有的do $$块、declare变量定义、begin/end结构，SQL语言函数不支持这些语法，直接触发语法错误。
• 冗余架构前缀：PostgreSQL默认无dbo架构（为SQL Server默认架构），未手动创建的情况下，dbo.user和dbo.userdevice会找不到对应表。
• 类型转换矛盾：函数返回值定义为uuid类型，但查询中强制将字段转为text，与返回类型不兼容。
• 变量与字段名冲突：where UserID = UserID2中，UserID作为表字段，易与变量名混淆，可能导致逻辑解析错误。

修复后的函数代码 #

PL/pgSQL版本（保留变量逻辑） #

create function spuserTest(username2 text)
    returns TABLE(UserDeviceID uuid, UserID uuid, DeviceID uuid)
    language plpgsql -- 修正为PL/pgSQL语言
as $$ -- 移除多余的do，直接用$$包裹函数体
declare
    v_userid uuid; -- 变量名改为v_userid，避免与字段名冲突
begin
    -- 移除dbo前缀，查询user表获取userid并赋值给变量
    select t.userid into v_userid 
    from "user" t 
    where t.username = username2;

    RETURN QUERY
        SELECT
            ud.userdeviceid,
            ud.userid,
            ud.deviceid
        FROM
            userdevice ud -- 给表加别名，明确字段归属
        where
            ud.userid = v_userid;
END $$;

简洁SQL语言版本（直接关联查询） #

如果不需要中间变量，可直接通过表关联实现，效率更高：

create function spuserTest(username2 text)
    returns TABLE(UserDeviceID uuid, UserID uuid, DeviceID uuid)
    language sql
as $$
    select
        ud.userdeviceid,
        ud.userid,
        ud.deviceid
    from "user" u
    join userdevice ud on u.userid = ud.userid
    where u.username = username2;
$$;

验证方法 #

调用函数测试：

select * from spuserTest('测试用户名');

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