Supabase触发器函数报错P0002：查询无返回行求助

我在Supabase平台编写了一个用于执行计算的PL/pgSQL函数，该函数会在目标表发生插入或更新操作时被触发器触发执行。这是我第一次使用SQL触发器，用测试数据测试函数时遇到了错误，怀疑是函数在测试数据插入表之前就执行查询导致的，但没法确定。

报错信息 #

ERROR: P0002: query returned no rows
CONTEXT: PL/pgSQL function calculate_wss(uuid) line 73 at SQL statement
SQL statement "SELECT calculate_wss(NEW.brand_id)"
PL/pgSQL function trigger_calculate_wss_brands() line 4 at PERFORM

触发错误的代码片段 #

FOR facility_nonweighted, facility_weighted, brand_fac_nonweighted 
    IN (
      SELECT
        (f.water_utility + f.rainwater_harvested + f.greywater_recycled + f.ground_bore_water) AS facility_nonweighted,
        (
          (f.water_utility        * 1.0 * f.freshwater_source_index)
          + (f.rainwater_harvested * 0.2 * f.freshwater_source_index)
          + (f.greywater_recycled  * 0.1 * f.freshwater_source_index)
          + (f.ground_bore_water   * 1.2 * f.freshwater_source_index)
        ) AS facility_weighted,
        fb.brand_water_consumption AS brand_fac_nonweighted
      FROM facilities f
      JOIN facility_brands fb ON f.id = fb.facility_id
      WHERE fb.brand_id = p_brand_id
    )
    LOOP
        IF facility_nonweighted > 0 THEN
            ratio_fac := brand_fac_nonweighted / facility_nonweighted;
        ELSE
            ratio_fac := 0;
        END IF;

        -- Add this facility’s portion to brand_weighted_twc
        brand_weighted_twc := brand_weighted_twc + (facility_weighted * ratio_fac);

        -- Also sum brand’s share of internal_recycle_volume from this facility
        -- (internal_recycle_volume * ratio_fac)
        SELECT f.internal_recycle_volume 
        INTO STRICT facility_weighted  -- reusing the variable just for a quick fetch, or define a new var
        FROM facilities f
        WHERE f.id IN (
          SELECT fb.facility_id
          FROM facility_brands fb
          WHERE fb.brand_id = p_brand_id
          AND (
            (f.water_utility + f.rainwater_harvested + f.greywater_recycled + f.ground_bore_water) = facility_nonweighted
            AND ( (f.water_utility*1.0 + ) = facility_weighted ) -- or better approach with PK
          )
        );

1. 核心错误原因 #

报错query returned no rows来自代码里的SELECT ... INTO STRICT语句——STRICT要求查询必须返回恰好一行，但当前查询没找到匹配数据，直接抛出异常。

你的问题不在触发器执行时机（默认触发器是AFTER INSERT/UPDATE，数据已经写入表中），而是这段查询逻辑有问题：

• 子查询里的条件(f.water_utility*1.0 + ) = facility_weighted是语法错误，少了后续计算项，导致查询无法正确匹配数据
• 用计算值（facility_nonweighted、facility_weighted）来匹配设施，本身就不可靠——不同设施可能算出相同的数值，或者浮点精度问题导致匹配失败
• 循环里已经从facilities和facility_brands关联查询拿到了设施数据，没必要再次查询，完全可以直接在初始查询里把internal_recycle_volume一并取出

2. 修复步骤 #

步骤1：修正初始查询，直接获取所需字段 #

把internal_recycle_volume加入第一个查询的SELECT列表，避免后续重复查询：

FOR facility_nonweighted, facility_weighted, brand_fac_nonweighted, internal_recycle_volume
    IN (
      SELECT
        (f.water_utility + f.rainwater_harvested + f.greywater_recycled + f.ground_bore_water) AS facility_nonweighted,
        (
          (f.water_utility        * 1.0 * f.freshwater_source_index)
          + (f.rainwater_harvested * 0.2 * f.freshwater_source_index)
          + (f.greywater_recycled  * 0.1 * f.freshwater_source_index)
          + (f.ground_bore_water   * 1.2 * f.freshwater_source_index)
        ) AS facility_weighted,
        fb.brand_water_consumption AS brand_fac_nonweighted,
        f.internal_recycle_volume  -- 直接取出需要的字段
      FROM facilities f
      JOIN facility_brands fb ON f.id = fb.facility_id
      WHERE fb.brand_id = p_brand_id
    )
    LOOP

步骤2：移除错误的二次查询，直接使用已获取的字段 #

删掉后面那段SELECT ... INTO STRICT的代码，直接用循环里拿到的internal_recycle_volume计算：

-- 替换原来的二次查询逻辑
brand_recycle_volume := brand_recycle_volume + (internal_recycle_volume * ratio_fac);

步骤3：（可选）处理触发器执行时机的疑虑 #

如果你确实担心触发器执行时机，可以显式指定触发器为AFTER INSERT OR UPDATE，确保数据已写入后再执行计算：

CREATE TRIGGER trigger_calculate_wss_brands
AFTER INSERT OR UPDATE ON your_target_table  -- 替换成你的目标表名
FOR EACH ROW
EXECUTE FUNCTION trigger_calculate_wss_brands();

3. 额外优化建议 #

• 避免在循环里执行查询，这会严重影响性能，尽量用批量查询一次性获取所有需要的数据
• 不要重复使用变量（比如用facility_weighted存储两种不同含义的值），定义专用变量提高代码可读性
• 如果必须使用INTO语句，且无法保证查询一定返回结果，可以去掉STRICT关键字，或者用EXCEPTION捕获异常：

BEGIN
  SELECT ... INTO facility_weighted FROM ...;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    facility_weighted := 0;  -- 无数据时设置默认值
END;

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