I am writing a function to carry out some calculations on supabase, which would be triggered when there is an insert or update in my table. I am new to using triggers in SQL and need help understanding an error that I am running into. The following error is showing up when i am testing the function with test data, and am quite sure it has something to do with the function looking for data in the table before the query has input the test data into the table, although I am not sure.

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

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

Portion of the function which is causing the error:

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
          )
        );