Operation of two-layer for loops in Postgresql stored procedure (plpgsql)

When encountering tests in the project, 4500 data needs to be created, and it needs to be divided into departments and dates. 30 pieces of data per day for each department. The remaining preparation data can be about 100,000 data per department per day. Here, each time you change the department and date, you need to operate at least 300 times. I thought of writing a function using a stored procedure.

First, understand the syntax of stored procedures:

CREATE [ OR REPLACE ] FUNCTION
  name( [ [argmode] [argname]argtype[ { DEFAULT | = }default_expr] [, ...] ] )
    [ RETURNSrettype
     | RETURNS TABLE (column_namecolumn_type[, ...] ) ]
  { LANGUAGElang_name
    | WINDOW
| IMMUTABLE | STABLE | VOLATILE
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | COSTexecution_cost
    | ROWSresult_rows
    | SETconfiguration_parameter{ TOvalue| =value| FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...
    [ WITH (attribute[, ...] ) ]
————————————————

CREATE [ OR REPLACE ] FUNCTION--Create a function. If there is this function, it will replace the recreation of name ---------Function name

RETURNS---Function Return Type

For specific function declarations, please refer to [Postgresql stored procedure]

Here is the function I wrote:

CREATE OR REPLACE FUNCTION "xue"."insert_into_table"()
 RETURNS "pg_catalog"."void" AS $BODY$
DECLARE tmp VARCHAR(1024);
DECLARE n integer;
DECLARE i integer;
DECLARE inst_seq_no CURSOR FOR
  SELECT inst_seq_no FROM t where no in (
  '111','22','223','33','4358',
   '233','449','315','35335');
BEGIN
  RAISE NOTICE '------------start----------';
  i := 30;
  FOR stmt IN no LOOP
    n := 30;
    FOR n IN n..i LOOP    
      insert into test2 (NO,
      test_NO,TIME,USER_NO,SEQ_NO,
      NAME,USER_NO1,USER_NAME,CODE,USER_NO2,OPROR_NAME,
      REVIEW_TIME,DESC,
      VAL1,VAL2,DATE,UPD_TIME,DEL_FLAG) values
      (nextval('seq_test2'),n,'20190910',n,stmt.seq_no,n,n,n,n,n,n,'20190910','01','',n,n,'20190910',
      '20190909','0');
      END LOOP;
       n = n+30;
      i = i+30;
  END LOOP;
  RAISE NOTICE '-----------finished---------';
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100`

Very simple logic, but it was only implemented after modifying it three or four times. This second time I write a stored procedure, many syntaxes are not very familiar with, so I should pay attention to:

1. `Variable declaration should be used with DECLARE

2.``Usage of Cursor CURSOR

The loop is to be executed in the begin.

4. Use ":=" in the loop

Supplement: Issues paying attention to when calling stored procedures (functions) in Postgresql

In postgresql, we often use select stored procedures when executing stored procedures, but if the stored procedures are called in the stored procedures, we cannot use this way. We should use perform stored procedures. You can refer to the instructions of the official documentation.

Execute a resultless expression or command

Sometimes we want to calculate an expression or a command, but discard the result (usually because we often call functions that have useful side effects but do not have useful result values). To do this in PL/pgSQL, you can use the PERFORM statement:

PERFORM query;

This statement executes a query and discards the result. The way to write query is the same as when you usually write SQL SELECT commands, just replace the keyword SELECT at the beginning with PERFORM. PL/pgSQL variables are replaced with commands as usual. Likewise, if the command generates at least one line, the special variable FOUND is set to true, and if no line is generated, it is false.

Note: We may hope that SELECT without an INTO clause can meet this need, but the only way to accept it is PERFORM at the moment.

An example:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.