注意
- 获取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;