PostgreSQL stored procedures

Select and assign next value from your sequence and insert to table

create or replace procedure TestJobProcedure

language plpgsql    

as $$

declare

     txid number;

begin

   select nextval('app_reports."app_transaction_seq"') into txid;

   insert INTO app_reports.app_transaction (tx_id,tx_time) VALUES (txid, now());

   commit;

end;$$

Select and assign next value from your sequence and insert to table for entire month

 

create or replace procedure insertTransactions
language plpgsql    
as $$
declare
     txid number;
     ctxid number;
     currDate varchar(20) := '2010-03-';
     currDateIndex integer :=0;
     tmpDate varchar(20);
begin
loop
   currDateIndex=currDateIndex+1;

   select concat(currDate,currDateIndex) into tmpDate;   

   select nextval('app_reports."app_transaction_seq"') into txid; 

   insert into app_reports.app_transaction (tx_id,tx_time) values (txid,tmpDate::timestamp);

   commit;

   if (currDateIndex > 30 ) then
      exit;
   end if;
      raise notice 'currDateIndex %', tmpDate;

end loop;
end;
$$