PostgreSQL表返回型函数异常处理失效问题咨询

一、正确的函数实现示例 #

假设我们有一张smartphone_owners表，结构为id INT, name TEXT, phone_model TEXT，下面是符合需求的函数：

CREATE OR REPLACE FUNCTION get_smartphone_owner(owner_id INT)
RETURNS TABLE(owner_id INT, owner_name TEXT, phone_model TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 执行查询并返回结果集
    RETURN QUERY
    SELECT id, name, phone_model
    FROM smartphone_owners
    WHERE id = owner_id;

    -- 检查是否有数据返回
    IF NOT FOUND THEN
        -- 抛出NO_DATA_FOUND对应的自定义异常（状态码P0002）
        RAISE EXCEPTION '未找到ID为%的智能手机所有者', owner_id
        USING ERRCODE = 'P0002';
    END IF;

EXCEPTION
    -- 捕获无效文本表示异常（对应状态码22P02），返回自定义提示
    WHEN invalid_text_representation THEN
        RAISE EXCEPTION '输入的ID必须是整数类型'
        USING ERRCODE = '22P02';
END;
$$;

二、常见问题解析 #

1. NO_DATA_FOUND异常失效的原因
   PostgreSQL没有内置NO_DATA_FOUND异常（这是Oracle的语法），直接捕获会报错。正确做法是用PostgreSQL内置的FOUND变量判断RETURN QUERY是否返回了数据，再主动抛出指定状态码P0002的异常。

2. record返回类型的坑
   用RETURNS record时，调用函数必须显式指定返回结构（比如SELECT * FROM get_smartphone_owner(1) AS (id INT, name TEXT, model TEXT)），否则PostgreSQL无法推断返回类型，必然报错。除非你能确保调用方每次都能指定结构，否则优先用RETURNS TABLE或RETURNS SETOF <自定义类型>，结构更清晰，调用更简单。

三、测试验证 #

• 查询存在的ID：

  SELECT * FROM get_smartphone_owner(1);
  -- 正常返回该用户的手机信息
  

• 查询不存在的ID：

  SELECT * FROM get_smartphone_owner(999);
  -- 抛出错误：未找到ID为999的智能手机所有者（状态码P0002）
  

• 传入非整数参数：

  SELECT * FROM get_smartphone_owner('abc');
  -- 抛出错误：输入的ID必须是整数类型（状态码22P02）
  

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