为何Psycopg调用PostgreSQL函数无匹配行时fetchone返回全None？

问题场景 #

我编写了用于通过邮箱查询账户的PostgreSQL函数：

CREATE FUNCTION get_account_by_email(account_email varchar(64)) RETURNS account AS $$
SELECT id,
    name,
    email
FROM account
WHERE account.email = account_email;
$$ LANGUAGE SQL;

同时使用psycopg[binary]==3.1.9编写了调用该函数的Python异步代码：

async def get_account_by_email(self, email: str):
    async with self.pool.connection() as conn:
        resp = await conn.execute(f"SELECT * FROM get_account_by_email(%s);", (email,))
        print(resp.rowcount)
        print(resp)
        return await resp.fetchone()

当查询一个不存在的邮箱时，得到不符合预期的结果：rowcount为1，fetchone()返回(None, None, None, None, None, None)。

原因分析 #

问题出在PostgreSQL函数的返回类型定义上：

• 该函数指定RETURNS account，表示返回单个account类型的行。
• 当函数体内的SELECT语句没有匹配到任何记录时，PostgreSQL不会返回0行结果，而是会生成一个所有字段为NULL的account类型实例作为返回值，也就是一行全NULL的记录。
• 因此psycopg接收到的结果集包含1行数据，所以rowcount为1，fetchone()返回全None的元组。

解决方案 #

方案1：修改函数返回多行结果集 #

将函数返回类型改为SETOF account，这样当没有匹配记录时，函数会返回0行结果，符合预期逻辑：

CREATE FUNCTION get_account_by_email(account_email varchar(64)) RETURNS SETOF account AS $$
SELECT id,
    name,
    email
FROM account
WHERE account.email = account_email;
$$ LANGUAGE SQL;

此时查询不存在的邮箱时，rowcount会是0，fetchone()返回None。

方案2：在函数内处理无结果场景 #

如果需要保持函数返回单行的定义，可以在函数体内判断是否有匹配结果，无结果时返回NULL，示例如下：

CREATE FUNCTION get_account_by_email(account_email varchar(64)) RETURNS account AS $$
SELECT (account).* 
FROM (
    SELECT id, name, email FROM account WHERE account.email = account_email LIMIT 1
) AS account
UNION ALL
SELECT NULL::account WHERE NOT EXISTS (
    SELECT 1 FROM account WHERE account.email = account_email
);
$$ LANGUAGE SQL;

不过这种方式相对复杂，推荐优先使用方案1。

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