execute transactions inside loop with function calls

by lapots   Last Updated November 16, 2018 10:06 AM

I have a function that looks like this

create or replace function do_aggregate(label varchar, type integer)
returns void
language plpgsql
as $function$
    declare
        id integer;
        tempSum integer;
    begin
        ... multiple insert / update operations
    end;
$function$

I need to execute this function multiple times and I need to wrap all this function calls into transactions every time it is invoked.

For example

begin transaction;
    select do_aggregate('max', 1);
end transaction;

begin transaction;
    select do_aggregate('min', 1);
end transaction;

In addition the creation of transactions should be done inside the loop. If Postgresql supported transaction creation inside function I would probably have something like this

create function do_loops()
returns void
language plpgsql
as $function$
declare
    counter integer := -1;
begin
    loop
        exit when counter = 0;

        begin transaction;
            select do_aggregate('min', 1');
        end transaction;

        select count(*) into counter
        ...
    end loop;
end
$function$

And then would invoke function somewhere

begin transaction;
    do_loops();
end transaction;

But postgresql doesn't support transactions inside the function. How to achieve what I need?



Related Questions


Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM