PostgreSQL传入自定义类型对象调用函数报42P01错误

42P01: relation "account" does not exist和调用写法无关，是函数内部SQL逻辑错误：
函数中声明了局部变量account core.account_type := input，但INSERT语句里写了from account as a，PostgreSQL解析器会将FROM后的标识符优先识别为表、视图等关系对象，不会识别为PL/pgSQL局部变量，因此会去查找名为account的表，而你的表实际是core.account，search_path未匹配到对应表就触发报错。

另外原函数的UPSERT更新逻辑也有错误：coalesce(a.user_name, excluded.user_name)中a和excluded实际是同一份传入数据，coalesce判断完全无效，无法实现“传入字段为NULL时保留表中原值”的预期。

1. 修正函数定义 #

直接替换原函数即可，去掉错误的FROM子句，改用VALUES传值，同时修正冲突更新时的字段取值逻辑：

create or replace function core.account_upsert(
    in input core.account_type)
    returns table
            (
                account_id         uuid,
                user_name          character varying,
                email              character varying,
                is_active          boolean,
                create_date        timestamp with time zone,
                last_modified_date timestamp with time zone
            )
    language plpgsql
    cost 100
    volatile
    rows 1000
as
$BODY$
begin
    return query
        insert into core.account (
            account_id,
            user_name,
            email,
            is_active,
            create_date,
            last_modified_date
        )
        values (
            input.account_id,
            input.user_name,
            input.email,
            input.is_active,
            current_timestamp(0),
            current_timestamp(0)
        )
        on conflict (account_id)
            do update set
                user_name = coalesce(excluded.user_name, core.account.user_name),
                email = coalesce(excluded.email, core.account.email),
                is_active = coalesce(excluded.is_active, core.account.is_active),
                last_modified_date = current_timestamp(0)
        returning
            account_id,
            user_name,
            email,
            is_active,
            create_date,
            last_modified_date;
end
$BODY$;

注：如果确实需要在PL/pgSQL中将复合类型变量作为行集查询，不能直接写from 变量名，要写成from (select (复合变量名).*) as 别名的格式。原函数中声明局部变量account属于冗余写法，直接使用入参input即可。

2. 正确调用方式 #

你之前尝试的第一种调用写法是合法的，第二种匿名行写法因为PostgreSQL无法自动推断类型会报错，推荐始终用显式类型转换的写法：

-- 新增账号：account_id传null会自动触发默认值生成UUID
select * from core.account_upsert(cast(row(null, 'test.user', 'test.user@yahoo.com', true) as core.account_type));

-- 更新账号：传入已存在的account_id，不需要修改的字段传null即可保留库中原值
select * from core.account_upsert(cast(row('f47ac10b-58cc-4372-a567-0e02b2c3d479'::uuid, 'new_username', null, null) as core.account_type));

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