How to summarize the inserted amount of two different queries in a single transaction?-CodePudding

When performing two consecutive insert query statements in a single transaction like:

INSERT INTO MyTable1 
SELECT * FROM SomeTable1 (100 rows)

INSERT INTO MyTable2
SELECT * FROM SomeTable2 (200 rows)

The total amount of affected rows is 200 rows (of the last INSERT statement: "MyTable2"), I was wondering how to make it to return 300 rows as the sum of both INSERT statements?

CodePudding user response：

DO
$body$
declare 
    v_count integer;
    sum_count integer;
begin
    
    sum_count = 0;

    INSERT INTO MyTable1 
    SELECT * FROM SomeTable1;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    sum_count = sum_count   v_count; 
    
    INSERT INTO MyTable2
    SELECT * FROM SomeTable2;

    GET DIAGNOSTICS v_count = ROW_COUNT;
    sum_count = sum_count   v_count; 

    raise notice 'All inserted: % ', sum_count;
    -- return sum_count; 

END;
$body$
LANGUAGE 'plpgsql';