I want to sum basket item price values and insert it into a payment as total price. I am trying to use a trigger for this but I am getting error near SET total_price =. Error msg: Expected expression, got 'select'. How do I deal with this?

CREATE FUNCTION make_sum() RETURNS TRIGGER
AS $$
    BEGIN
        UPDATE payment
            SET total_price = select sum(price) from basket where basket_id = new.basket_id;
        RETURN NULL;
END;$$ LANGUAGE plpgsql;
CREATE TRIGGER make_sum AFTER INSERT ON basket FOR EACH ROW EXECUTE make_sum()

CodePudding user response：

Here is the updated version. I have made the following changes to your script:

1. Put the select in your update statement within parentheses
2. Added the keyword 'PROCEDURE' in your trigger definition.

CREATE FUNCTION make_sum() RETURNS TRIGGER
    AS $$
        BEGIN
            UPDATE payment
                SET total_price = (select sum(price) from basket where basket_id = new.basket_id);
            RETURN NULL;
    END;$$ LANGUAGE plpgsql;
    
CREATE TRIGGER make_sum
AFTER INSERT ON basket FOR EACH ROW EXECUTE PROCEDURE make_sum();