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 ?

Answer 1

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.