plpgsql 循环插脚本

注意

• 获取execute的结果保存到变量，使用into，execute ... into variable、select ... into variable
• 拼接字符串，使用||，或者使用函数concat('A', 'B', ...)
• PERFORM vs EXECUTE :
• PERFORM 调用无返回值的函数
• EXECUTE 执行动态生成的语名
• pg_sleep(secends)， PERFORM pg_sleep(0.1) 这样调用，或者嵌在select中

CREATE OR REPLACE FUNCTION gen_min_day(_day varchar) RETURNS void AS  
$BODY$
DECLARE 
i INTEGER;
num INTEGER;
T TIMESTAMP;
sqlstring VARCHAR;
t1 TIMESTAMP;
BEGIN
  RAISE NOTICE 'Value: %', _day;
  EXECUTE 'create TABLE IF not EXISTS ' || concat('transducerstatusminute', _day) || '(
  "number" int4 NOT NULL,
  "value" float8,
  "time" timestamp(6) NOT NULL
  ) ';
  num := 3218101;
  i := 1;
  T := cast (concat(_day ,' 0:0:0.000') as  timestamp without time zone);
  t1 := T;
  << goto_label >>
  WHILE
  num <= 3218201
  loop
  sqlstring :=  'INSERT INTO ' || concat('transducerstatusminute', _day) || ' ( "number", "value", "time" ) VALUES ( '|| num || E',1,\'' || T || E'\')';
  --RAISE NOTICE 'running: %', sqlstring;
  EXECUTE sqlstring;
  i := i + 1;
  T := T + '5min';
  IF i = 289 THEN
    num := num + 1;
    IF num <= 3218201 THEN
      i := 1;
      T := t1;
      --sleep
      PERFORM pg_sleep(0.1);
      CONTINUE goto_label;    
    END IF; 
  END IF;
  END loop;
END;
$BODY$ LANGUAGE plpgsql;

do $$
DECLARE
daystring VARCHAR;
sqlstring TIMESTAMP;
rel VARCHAR;
k INTEGER;
BEGIN
k := 1;
WHILE k<365 LOOP
  daystring := 'SELECT CURRENT_DATE-interval  '|| E'\'' || k  || E' day\'';
  --RAISE NOTICE 'running: %', daystring;
  --获取execute的结果保存到变量
  execute daystring into sqlstring;
  --RAISE NOTICE 'running: %', sqlstring;
  select to_char(sqlstring, 'YYYYMMDD') into rel;
  PERFORM gen_min_day(rel);
  k := k+1;
END LOOP;

END;
$$ LANGUAGE plpgsql;

---

Ref
How pg_sleep() Works in PostgreSQL
Execut vs PERFORM