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 — jorge is not ai
– jorge is not ai, Commented Feb 26, 2020 at 23:46
also this is possibly related: Postgres 9.5+: UPSERT to return the count of updated and inserted rows — jorge is not ai
– jorge is not ai, Commented Feb 26, 2020 at 23:58
BTW, AFAIK, postgres do have a stored procedure's and transaction control. — jorge is not ai
– jorge is not ai, Commented Feb 27, 2020 at 0: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. — user330315
– user330315, Commented Feb 27, 2020 at 6:47

Hambone · Accepted Answer · 2020-02-27 02:51:13Z

3

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.

edited Feb 27, 2020 at 2:51

answered Feb 27, 2020 at 2:43

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

"but I would have done the number of months slightly differently" - I would use make_interval(months => $1)

Hambone Over a year ago

@a_horse_with_no_name - I didn't even know that function existed! Thanks for the education!

Sidhu177 · Accepted Answer · 2020-02-27 19:14:17Z

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

Collectives™ on Stack Overflow

Returning insert counts from multiple tables using postgres function

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related