Returning insert counts from multiple tables using postgres function

user_11223553 0
Viewed 144

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 ?

postgresql sql
2 Answers

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.

user_10018840

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$;
user_112235530
Related