/aggs-utxo can take a lot of time

[majecty]

/aggs-utxo API should read all rows in the UTXOs table filtered by assetType or assetType and address.

Suggestion

Create a new table that has the aggregated result.

[majecty]

I tried this trigger and it works.

create or replace function utxo_trigger() returns trigger
    language plpgsql
as
$$
DECLARE
        prev_amount int;
    BEGIN
        IF (TG_OP = 'INSERT') THEN
            SELECT aggs.amount INTO prev_amount FROM aggs WHERE aggs.address=NEW.address AND aggs.assettype=NEW.assettype;
            IF NOT FOUND THEN
                prev_amount := 0;
            END IF;
            INSERT INTO aggs(assettype, address, amount) VALUES(NEW.assettype, NEW.address, NEW.amount+prev_amount)
            ON CONFLICT ON CONSTRAINT aggs_assettype_address_unique DO UPDATE SET amount=NEW.amount+prev_amount;
            return NEW;
        ELSEIF (TG_OP = 'UPDATE') THEN
            SELECT aggs.amount INTO prev_amount FROM aggs WHERE aggs.address=NEW.address AND aggs.assettype=NEW.assettype;
            INSERT INTO aggs(assettype, address, amount) VALUES(NEW.assettype, NEW.address, NEW.amount+prev_amount)
            ON CONFLICT ON CONSTRAINT aggs_assettype_address_unique DO UPDATE SET amount=NEW.amount+prev_amount;

            IF EXISTS (SELECT FROM aggs WHERE aggs.address=OLD.address AND aggs.assettype=OLD.assettype) THEN
                UPDATE aggs SET amount=amount-OLD.amount WHERE aggs.address=OLD.address AND aggs.assettype=OLD.assettype;
            END IF;
            RETURN NEW;
        ELSEIF (TG_OP = 'DELETE') THEN
            SELECT aggs.amount INTO prev_amount FROM aggs WHERE aggs.address=OLD.address AND aggs.assettype=OLD.assettype;
            IF NOT FOUND THEN
                prev_amount := 0;
            END IF;
            INSERT INTO aggs(assettype, address, amount) VALUES(OLD.assettype, OLD.address, prev_amount-OLD.amount)
            ON CONFLICT ON CONSTRAINT aggs_assettype_address_unique DO UPDATE SET amount=prev_amount-OLD.amount;
            return OLD;
        END IF;
    END;
$$;

alter function utxo_trigger() owner to user;

CREATE TRIGGER utxo_trigger
AFTER INSERT OR UPDATE OR DELETE
   ON utxos
   FOR EACH ROW
       EXECUTE PROCEDURE utxo_trigger()