PLPGSQL example - accumulator

benchmark_plpgsql.sql

--
-- Benchmark PLPGSQL
--

DROP TABLE accumulator;
CREATE TABLE accumulator (id BIGSERIAL PRIMARY KEY, new_value INT, sum_previous INT);


CREATE OR REPLACE FUNCTION "fillaccumulator"() RETURNS boolean AS
$BODY$
DECLARE
    acc_count integer;
    acc_sum integer;
    randint integer;
BEGIN
    SELECT count(*), SUM(new_value) INTO acc_count, acc_sum FROM "accumulator";

    IF FOUND THEN
        randint = trunc(random() * 99 + 1);
        INSERT INTO "accumulator" ("new_value", "sum_previous") VALUES (randint, acc_sum);
        RETURN TRUE;
    END IF;
    RETURN FALSE;
END;
$BODY$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION plbench(query text, n int) returns float as $$
DECLARE
    t0 timestamp with time zone;
    e float;
BEGIN
    t0 := clock_timestamp();
    for i in 1 .. n loop
        execute query;
    end loop;
    e = extract(microseconds from clock_timestamp()) - extract(microseconds from t0);
    return e / 1000000;
END;
$$ language plpgsql;

SELECT plbench('SELECT fillaccumulator()', 10000);

-- SELECT count(*), SUM(new_value) FROM accumulator;
SELECT * FROM accumulator ORDER BY id DESC LIMIT 1;