Postgres Trigger to sum colum from another table where ids match

Question

I have been stuck at this for hours, ive seen examples but just cant get this to work.

I want to sum the "Toatal" colum from "BuiltResourceLines" table and put into Total of "BuiltResources" table where "BuiltResourceLines"."BuiltResourceId" = "BuiltResources"."Id"

For the life of me cant get this to work:

    UPDATE dbo."BuiltResources" BR
    SET "Total" = SUM(BRL."Total")
    FROM dbo."BuiltResourceLines" BRL
    WHERE BR."Id" = BRL."BuiltResourceId";

This one returns ' cant use aggregate... '

    UPDATE dbo."BuiltResources" BR
    SET "Total" = 
        (
            SELECT SUM(BRL."Total")
            FROM dbo."BuiltResourceLines" BRL 
            WHERE BRL."BuiltResourceId" = BR."Id"
        ) 
    WHERE BR."Id" = "HOW TO GET REFERENCE TO BRL HERE????;

This one i cant get reference to BRL and i get a warning: Unsafe query: 'Update' statement without 'where' updates all table rows at once

Ive tried about 50 other scenarios but these seem closest.

Please help.

For info this is the entire trigger:

CREATE OR REPLACE FUNCTION update_builtresource_total()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    IF TG_OP = 'UPDATE' THEN
        
        --QUERY
      
    END IF;
    RETURN NULL;

END
$$;

DROP TRIGGER IF EXISTS update_builtresource_total_trigger on dbo."BuiltResourceLines";

CREATE TRIGGER update_builtresource_total_trigger
    AFTER UPDATE
    ON dbo."BuiltResourceLines"
    FOR EACH ROW
EXECUTE PROCEDURE update_builtresource_total();

Answer 1

Your second query doesn't need the additional WHERE. It'll already return the sum that corresponds to the current row BR."Id" in the inner expression.

What might be useful is a coalesce that will prevent null from popping up whenever there are no rows for a given "Id". Also, your first query joined on "Id" in both tables, the second one joins "Id" with "BuiltResourceId" which makes more sense. Updated demo:

create schema dbo;
create table dbo."BuiltResources" (
  "Id" serial primary key, 
  "Total" numeric);
create table dbo."BuiltResourceLines" (
  "BuiltResourceId" bigint references dbo."BuiltResources"("Id"), 
  "Total" numeric);
insert into dbo."BuiltResources"("Total") 
  values (0),(0),(0),(0),(0);
insert into dbo."BuiltResourceLines"("BuiltResourceId","Total") 
  values (1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(3,-7);

UPDATE dbo."BuiltResources" BR
SET "Total" = 
        (
            SELECT coalesce(SUM(BRL."Total"),0)
            FROM dbo."BuiltResourceLines" BRL 
            WHERE BRL."BuiltResourceId" = BR."Id"
        )
returning *;

-- Id | Total
------+-------
--  1 |     6
--  2 |    15
--  3 |    -7
--  4 |     0
--  5 |     0

To avoid updating the whole table each time, you can target only the rows matching NEW. Added to the demo:

CREATE OR REPLACE FUNCTION update_builtresource_total()
RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
        UPDATE dbo."BuiltResources" BR
        SET "Total" = (
        (
            SELECT coalesce(SUM(BRL."Total"),0)
            FROM dbo."BuiltResourceLines" BRL 
            WHERE BRL."BuiltResourceId" = BR."Id"
        )
        WHERE BR."Id"=NEW."BuiltResourceId";
    END IF;
    RETURN NULL;
END $$;