Postgres Trigger to sum colum from another table where ids match-CodePudding

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

CodePudding user response：

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