How to set a sum of column to variable in trigger function?

Si6aClo 0
Viewed 42

I am writing a trigger for a table in PostgreSQL 14 with which I could get the amounts I need, add them up and write them from a row. But I can't write data to variables after SELECT in any way. Here is my code:

CREATE TABLE folder(
    Id uuid unique,
    Url varchar(255),
    Date timestamp,
    ParentId uuid,
    Size int
);

CREATE TABLE file(
    Id uuid unique,
    Url varchar(255),
    Date timestamp,
    ParentId uuid,
    Size int,
    FOREIGN KEY (ParentId) REFERENCES Folder (Id) ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION update_dates_and_price_update_folder() RETURNS trigger AS $update_dates_and_price_update_folder$
DECLARE
    folders_sum integer;
    files_sum integer;
BEGIN
    SELECT (sum(size)) FROM file WHERE parentId = NEW.id INTO folders_sum;
    SELECT (sum(size)) FROM folder WHERE parentId = NEW.id INTO files_sum;
    NEW.size = folders_sum + files_sum;
    UPDATE folder set date = NEW.date WHERE id = NEW.parentId OR id = OLD.parentId;
    RETURN NEW;
END;

$update_dates_and_price_update_folder$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_data_folder AFTER UPDATE
    ON folder
    FOR ROW
    EXECUTE FUNCTION update_dates_and_price_update_folder();

I checked the values of variables via RAISE NOTICE, both variables are always null. If you make such a request:

SELECT (sum(size)) FROM file WHERE parentId = some_id;

It's not null. select response

triggers postgresql
1 Answers

Solution

CREATE OR REPLACE FUNCTION update_price_folder() RETURNS trigger AS $update_price_update_folder$
DECLARE
    folders_sum integer;
    files_sum integer;
    file_item file;
    folder_item folder;
BEGIN
    files_sum := 0;
    FOR file_item IN SELECT * FROM file WHERE parentId = NEW.id
        LOOP
            IF file_item.size IS NOT NULL THEN
                files_sum := files_sum + file_item.size;
            END IF;
        END LOOP;

    folders_sum := 0;
    FOR folder_item IN SELECT * FROM folder WHERE parentId = NEW.id
        LOOP
            IF folder_item.size IS NOT NULL THEN
                folders_sum := folders_sum + folder_item.size;
            END IF;
        END LOOP;
    NEW.size = folders_sum + files_sum;
    RETURN NEW;
END;

$update_price_update_folder$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_price_folder_trigger BEFORE UPDATE
    ON folder
    FOR ROW
    EXECUTE FUNCTION update_price_folder();



CREATE OR REPLACE FUNCTION update_dates_folder() RETURNS trigger AS $update_dates_folder$
    BEGIN
        UPDATE folder set date = NEW.date WHERE id = NEW.parentId OR id = OLD.parentId;
        RETURN NEW;
    END;

$update_dates_folder$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_date_folder AFTER UPDATE
    ON folder
    FOR ROW
    EXECUTE FUNCTION update_dates_folder();
Si6aClo0
Related