Returning insert counts from multiple tables using postgres function

Question

I have a function which inserts 1 month worth data From main tables into History tables. As postgres does not have stored procedure I have declared the insert and delete logic as a function. I want to know if there is a way to return the counts from the inserts and deletes as an output of the function. Following is the code.

CREATE FUNCTION public.insertDelete(integer)
    RETURNS void
    LANGUAGE plpgsql

AS $BODY$

DECLARE
    saveTime timestamp;
BEGIN
   saveTime := now();


INSERT INTO public.hist_One
    (select * from public.main_One
    WHERE udate < (saveTime - ($1::text || ' months')::interval));

INSERT INTO public.hist_Two
    (select * from public.main_Two
    WHERE udate < (saveTime - ($1::text || ' months')::interval));

INSERT INTO public.hist_Three
    (select * from public.main_Three
    WHERE udate < (saveTime - ($1::text || ' months')::interval));


delete from public.main_One
where udate < (saveTime - ($1::text || ' months')::interval);

delete from public.main_Two
where udate < (saveTime - ($1::text || ' months')::interval);

delete from public.main_Three
where udate < (saveTime - ($1::text || ' months')::interval);


END;
$BODY$;

If I try to return Query then the code looks something like this

CREATE FUNCTION public.insertDelete(integer)
        RETURNS Table(Hist_One int, Main_one int)
        LANGUAGE plpgsql

    AS $BODY$

    DECLARE
        saveTime timestamp;
    BEGIN
       saveTime := now();


    INSERT INTO public.hist_One
        (select * from public.main_One
        WHERE udate < (saveTime - ($1::text || ' months')::interval));

   RETURN QUERY select count(*) from public.main_One
        WHERE udate < (saveTime - ($1::text || ' months')::interval)

The problem though is I want to return for all the tables and with RETURN QUERY I can do it only for one table. so How can I return a table that will list the inserts into the Hist tables and the deletes from Main tables ?

possible duplicate of [Get count of records affected by INSERT or UPDATE in PostgreSQL](https://stackoverflow.com/q/4038616/8339821) — user14063792468, Feb 26 '20 at 23:46
also this is possibly related: [Postgres 9.5+: UPSERT to return the count of updated and inserted rows](https://stackoverflow.com/q/38851217/8339821) — user14063792468, Feb 26 '20 at 23:58
BTW, AFAIK, postgres do have a `stored procedure`'s and transaction control. — user14063792468, Feb 27 '20 at 00:00
Unrelated to your question, but `(saveTime - ($1::text || ' months')::interval))` can be simplified to `savetime - make_interval(months => $1)` and you don't really need to store `now()` into a variable as the value doesn't change during a transaction. — , Feb 27 '20 at 06:47

Hambone · Answer 1 · 2020-02-27T02:51:13.320

You can use the diagnostics ROW_COUNT to get the number of rows impacted with each insert, and then just change the return type of the function to an integer and "return" the total rows:

CREATE FUNCTION public.insertDelete(integer)
    RETURNS integer
    LANGUAGE plpgsql
AS $BODY$
DECLARE
    saveTime timestamp;
    rowcount integer;
    totalrow integer;
BEGIN
   saveTime := now();
   total_rows := 0;

  INSERT INTO public.hist_One
    (select * from public.main_One
    WHERE udate < (saveTime - ($1::text || ' months')::interval));

  GET DIAGNOSTICS rowcount = ROW_COUNT;
  totalrow = totalrow + rowcount;

  INSERT INTO public.hist_Two
    (select * from public.main_Two
    WHERE udate < (saveTime - ($1::text || ' months')::interval));

  GET DIAGNOSTICS rowcount = ROW_COUNT;
  totalrow = totalrow + rowcount;

  INSERT INTO public.hist_Three
    (select * from public.main_Three
    WHERE udate < (saveTime - ($1::text || ' months')::interval));

  GET DIAGNOSTICS rowcount = ROW_COUNT;
  totalrow = totalrow + rowcount;

  -- the rest of your function here    

  return totalrow;
END;
$BODY$;

Totally unrelated, and it probably doesn't matter, but I would have done the number of months slightly differently:

INSERT INTO public.hist_One
select * from public.main_One
WHERE udate < saveTime - interval '1 month' * $1;

Or perhaps even better declare the value once:

DECLARE
  from_date date;

from_date := current_date - interval '1 month' * $1;

And then use it for each query:

INSERT INTO public.hist_One
select * from public.main_One
WHERE udate < from_date;

The advantage of this is I can see cases where the dynamic calculation would suppress the use of an index, whereas this would be sent as a static value and would definitely take advantage of any indexes on the "udate" column.

Also, the commenter whose name I cannot type is right -- PostgreSQL does support stored procedures as of version 11... although in this case, since you want the number of rows returned, a function seems better.

"*but I would have done the number of months slightly differently"* - I would use `make_interval(months => $1)` — , Feb 27 '20 at 06:48
@a_horse_with_no_name - I didn't even know that function existed! Thanks for the education! — Hambone, Feb 27 '20 at 11:47

Sidhu177 · Answer 2 · 2020-02-27T19:14:17.530

After some thinking on the best way forward for returning the insert counts and delete counts I decided on creating a separate table called loadinfo and as a part of the function insert the counts into the loadinfo table after the Loading into History tables is completed. This way I can have the inserts and deletes into the Hist table for reference

CREATE FUNCTION public.insertfour_stats(integer)
    RETURNS void
    LANGUAGE plpgsql

AS $BODY$

DECLARE
    saveTime timestamp;
    from_date date;
    FuncInvoke varchar;
BEGIN
   saveTime := now();
   from_date := current_date - interval '1 month'*$1;
   FuncInvoke := 'Insert to Hist';

INSERT INTO public.Hist_One
    (select * from public.Main_One
    WHERE udate < from_date);

INSERT INTO public.Hist_Two
    (select * from public.Main_Two
    WHERE udate < from_date);

INSERT INTO public.loadinfo(bdate, FuncInvoke, summary_hist_insert, account_hist_insert)
(SELECT saveTime, FuncInvoke
 , (select count(*) from public.Main_One WHERE udate < from_date)
 , (select count(*) from public.Main_Two WHERE udate < from_date));

delete from public.Main_One
where udate < from_date;

delete from public.Main_Two
where udate < from_date;

END;
$BODY$;

Returning insert counts from multiple tables using postgres function

2 Answers2